How to use a database with PHP

Using a database is a very common task for PHP scripts but it can be a very involved process. Below we’ll step through connecting to, inserting records and querying a database and displaying back the results.

PHP has many predefined functions for dealing with MySQL databases, you can find a full description of these at: http://php.net/manual/en/book.mysqli.php.

Your database details

If you haven’t already created a MySQL database you can follow the How to create a MYSQL database guide. For the purpose of this example we’ll use the database name DB_name, remember to replace this with your actual database name.

Connecting to the database

<?php
$connection = mysqli_connect("database4.lcn.com", "DB_username", "DB_password", "DB_name");

if (!$connection) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;
exit;
}

Lines 1-2 – Creates a connection to our database using mysqli_connect including parameters for: the database hostname, username, password and database name. A reference to our database connection is now stored in the $connection variable – which we’ll use next to perform some actions on our database. The fields for “database4.lcn.com” “DB_username”, “DB_password” and “DB_name” should be updated to include your database hostname, username, password and database name.

Lines 4-6 – If we couldn’t connect for any reason an error message is returned.

Creating a database table

Databases organise their records into tables. Each table should only contain one type of information.

In this example we’ll create a simple table to store contact information that hold First Name, Last Name and Email Address.

<?php
$sql = "CREATE TABLE IF NOT EXISTS Contacts (
FirstName varchar(64),
LastName varchar(64),
EmailAddress varchar(255))";

mysqli_query($connection, $sql);

?>

Lines 2-5 – Store a SQL query that will create our contacts table. We’ve declared FirstName and LastName as VARCHARs (variable characters, which is what we usually use for short text) with a length of 64 characters which should work for some pretty long names, and EmailAddress as a VARCHAR with a length of 255 characters, as Email Addresses can be quite long.

Line 8 – Will run our SQL query on the database, and create our table.

Inserting Records into our new table

Our next step is to start filling our table with information.

mysqli_query($connection, "INSERT INTO Contacts (FirstName, LastName, EmailAddress)
VALUES ('Peter', 'Griffin', 'peter@thehappygoluckytoyfactory.com')");

mysqli_query($connection, "INSERT INTO Contacts (FirstName, LastName, EmailAddress)
VALUES ('Homer', 'Simpson', 'homer@springfieldpowerplant.com')");

mysqli_query($connection, "INSERT INTO Contacts (FirstName, LastName, EmailAddress)VALUES ('Philip', 'Fry', 'pjfry@planetexpress.com')");

This code will insert 3 records into our Contacts table. Now that we have some data, we’ll take a look at how we look up and fetch data from our table.

Querying our database

We’re now going to find all the records from our Contacts table that have the FirstName “Peter”.

$results = mysqli_query($connection, "SELECT FirstName, LastName, EmailAddress FROM Contacts WHERE FirstName='Peter'");

This code will put an Array containing the FirstName, LastName and EmailAddress fields from records with a FirstName of ‘Peter’ into the variable $results.

Displaying our results

We’re now going to print our results into an HTML table.

echo "<table border='1'>
<tr>
<th>FirstName</th>
<th>LastName</th>
<th>EmailAddress</th>
</tr>";

while($row = mysqli_fetch_array($results)) {
echo "<tr>";
echo "<td>" . $row['FirstName'] . "</td>"; 
echo "<td>" . $row['LastName'] . "</td>"; 
echo "<td>" . $row['EmailAddress'] . "</td>"; 
echo "</tr>"; 
}
echo "</table>";

Lines 1-6 – Begins our table and creates some headings for our table.

Lines 8-15 – Loop over each of our results, and prints out a new row in our table.

Line 16 – Closes our table.

Disconnecting from the database

mysqli_close($connection);

It’s important that we make sure to close our connection to the database at the end of our script, so that we don’t end up leaving connections open. Having a lot of unclosed connections can cause issues.

The final script

<?php
// Connect to the database

$connection = mysqli_connect("database4.lcn.com", "LCN345596_db", "terminal1", "ranoutofideas_com_db");

if (!$connection) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;    
exit;
}

// Create our table

$sql = "CREATE TABLE IF NOT EXISTS Contacts (
FirstName varchar(64),
LastName varchar(64),
EmailAddress varchar(255))";

mysqli_query($connection, $sql);

// Insert some records

mysqli_query($connection, "INSERT INTO Contacts (FirstName, LastName, EmailAddress)VALUES ('Peter', 'Griffin', 'peter@thehappygoluckytoyfactory.com')");

mysqli_query($connection, "INSERT INTO Contacts (FirstName, LastName, EmailAddress)VALUES ('Homer', 'Simpson', 'homer@springfieldpowerplant.com')");

mysqli_query($connection, "INSERT INTO Contacts (FirstName, LastName, EmailAddress)VALUES ('Philip', 'Fry', 'pjfry@planetexpress.com')");

// Query our Database

$results = mysqli_query($connection, "SELECT FirstName, LastName, EmailAddress FROM Contacts WHERE FirstName='Peter'");

// Print out our results

echo "<table border='1'>
<tr>
<th>FirstName</th>
<th>LastName</th>
<th>EmailAddress</th>
</tr>";

while($row = mysqli_fetch_array($results))
{
echo "<tr>"; 
echo "<td>" . $row['FirstName'] . "</td>"; 
echo "<td>" . $row['LastName'] . "</td>"; 
echo "<td>" . $row['EmailAddress'] . "</td>"; 
echo "</tr>"; 
}
echo "</table>";

// Close our connection to the database

mysqli_close($connection);

?>

That’s it! You now have all you need to use a database with PHP.