PHP111

Main Page >> Web Application Development >> Workbook >> PHP and Databases

PHP and Databases – Making the Connection

How to connect

There are 3 ways to connect to a database in PHP:

1. (DEPRECATED) The mysql functions are procedural and use manual escaping.
2. MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements. We will use this option on this module
3. PDO (PHP Data Objects) is a general database abstraction layer with support for MySQL among many other databases. It provides prepared statements, and significant flexibility in how data is returned.

(source)

The code

<?php

// Connect to server/database
$mysqli = mysqli_connect("localhost", "YOUR_LOGIN", "YOUR_PASSWORD", "YOUR_DATABASE");
if (mysqli_connect_errno($mysqli)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Run SQL query
$res = mysqli_query($mysqli, "SELECT name, species, age FROM pet");

// How many rows were returned?
echo("<p>" . mysqli_num_rows($res) . " record(s) were returned...</p>");

// Loop through resultset and display each field's value
while($row = mysqli_fetch_assoc($res)) {
  echo $row['name']. " - ". $row['species'] ."<br>";
}

?>

See the result here.

The code explained

// Connect to server/database
$mysqli = mysqli_connect("localhost", "YOUR_LOGIN", "YOUR_PASSWORD", "YOUR_DATABASE");
if (mysqli_connect_errno($mysqli)) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Run SQL query
$res = mysqli_query($mysqli, "SELECT name, species, age FROM pet");

// How many rows were returned?
echo("<p>" . mysqli_num_rows($res) . " record(s) were returned...</p>");

// Loop through resultset and display each field's value
while($row = mysqli_fetch_assoc($res)) {
  echo $row['name']. " - ". $row['species'] ."<br>";
}

Ready to try some more PHP/MySQL?

A simple example of using a HTML form to capture search requirements and execute a custom query can be found at PHP112 - Simple HTML/PHP/MySQL example