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();
}

The first line of code is very important as it establishes and connection to our database server, using your credentials, and specifies which database we want to use on the server.

The mysqli_connect() function takes 4 parameters:

• server: "localhost" works fine here, since both PHP and MySQL are installed on the same server (mi-linux)
• login: your MySQL login
• password: your MySQL passord
• databse: the name of YOUR database (usually "db" + your student number, e.g. db0123456)

The mysqli_connect() function returns a connection object ($mysqli in this case) that we will use again later (see below).

The if statement deals with errors, i.e. if the connection fails, it provides basic information and error messages (e.g. "Access denied" if your login/password is wrong).

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

Once connected, the mysqli_query() function is used to RUN queries, and takes the following 2 parameters:

• A connection object, as returned by mysqli_connect() (see above)
• An SQL string

It returns a result-set as an object ($res in this case), i.e. a collection of 1 or more records.

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

Once you have obtained a result-set from the database, the first thing you can do is count how many records it contains, using the mysqli_num_rows() function. It takes a valid result-set as a parameter, and returns an int value (i.e. a number).

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

Displaying the number of records can be useful, but what we are really after here is the data itself, i.e. the records. You can fetch ONE record from a result-set using the mysqli_fetch_assoc() function, passing the result-set as a parameter. It returns ONE record from the set, i.e. 1 pet in this case.

If you put the mysqli_fetch_assoc() function in a while loop (as is the case here), it will keep fetching records until you reach the end of the result-set.

Inside the loop, we can access the current's record's fields like this:

$row['field_name']

So for example in our case:

$row['name']
$row['species']
$row['age']

In this case, we simply display the name and species on a single line, separated by a - character. 

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