How to use a database with PHP

Applies to: Dynamic Hosting, Premium Hosting, Unlimited Hosting

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.mysql.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 my_database, remember to replace this with your actual database name.

Connecting to the database

<?php
$database_username = 'username';
$database_password = 'password';
$database_host = 'database.lcn.com';
$database_name = 'my_database';

$connection = mysql_connect($database_host, $database_username, $database_password);
if(!$connection) { // if our attempt to connect failed
   die('Could not connect: ' . mysql_error());
}
mysql_select_db($database_name, $connection); // makes other mysql_ functions act on this database

?>

Lines 2-5 - declare variables to store our access details.

Line 7 - uses the variables to connect to our database. 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.

Lines 8-10 - If we couldn't connect for any reason we raise an error message.

Line 11 - 'selects' our database, so that any other mysql_ functions after this will work on our database.

Connecting to the database

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))";

// Execute query
mysql_query($sql,$connection);

?>

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.

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

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

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

mysql_query("INSERT INTO Contacts (FirstName, LastName, EmailAddress)
VALUES ('Peter', 'Bishop', 'peter@thebishops.com')");

This code will insert 4 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 = mysql_query("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 = mysql_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

mysql_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 – as having a lot of unclosed connections can cause issues.

The final script

<?php
// Connect to the database

$database_username = 'username';
$database_password = 'password';
$database_host = 'database.lcn.com';
$database_name = 'my_database';

$connection = mysql_connect($database_host, $database_username, $database_password);
if(!$connection) { // if our attempt to connect failed
   die('Could not connect: ' . mysql_error());
}
mysql_select_db($database_name, $connection); // makes other mysql_ functions act on this database

// Create our table

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

mysql_query($sql,$connection);

// Insert some records

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

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

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

mysql_query("INSERT INTO Contacts (FirstName, LastName, EmailAddress)
VALUES ('Peter', 'Bishop', 'peter@thebishops.com')");


// Query our Database

$results = mysql_query("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 = mysql_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

mysql_close($connection);

?>

If you have any problems running this script try reading our PHP troubleshooting guide.

Similar articles:

• How to use a database with Perl
• How to create an email form with Perl
• How to create a Perl script