Blake O'Hare .com

PHP Tutorial Part 7 - Interacting with a Database

There are very few websites left that don't utilize a database of some sort anymore. Luckily, PHP is more or less married to MySQL so interacting with a MySQL database is quite simple.

In this section, I will cover how to interact with a MySQL database from PHP. As for learning SQL, you will have to find another tutorial to do that. I have one such tutorial here.

Connecting to the MySQL server

First, I will assume you have your MySQL database set up and ready to go. The first thing you need to do is make a connection to the database. There is a function called mysql_connect that takes in the host name, the MySQL username, and the password.

mysql_connect("localhost", "username", "password");

The connection created by this function persists for the rest of the running of this script. Once the page is done loading, the connection dies. 9 times out of 10 on commercial shared hosting, the host for the MySQL server is on the same machine, so the host is "localhost", but if not, this should be provided to you by your hosting service.

Selecting a Database on the Server

A MySQL server contains multiple databases. So you have to specify which one you'd like to make queries against. The function mysql_select_db lets you do this...

mysql_select_db("mysite");

This sets the database that will be queried from the point you select it onward. If you want to change the database called, you can call this again to switch it.

Finally, making queries

Now you're ready to make queries. Queries are routed to the last server connected to and last database selected.

$result = mysql_query("SELECT * FROM `users`");

This makes the query and returns the result of the query into the $result variable. The actual contents of $result is what we call a MySQL resource object. You can't access the data inside it like an array. But luckily, PHP has even more functions that let you pull information out of $result.

mysql_num_rows($result);

As the name implies, this will give you the number of rows in the MySQL result.

$row = mysql_fetch_row($result);

This will get the next row of the result from the last time you called this function. So the first time you call this on $result, you will get the first row. The 2nd time you call this on $result, you will get the 2nd row. etc. The row is returned in the form of an array. Now we have enough tools to make a loop from a query...

$people = mysql_query("SELECT * FROM `users`");

echo '<table>';

for ($i = 0; $i < mysql_num_rows($people); $i = $i + 1)
{
    echo '<tr>';
    
    $person = mysql_fetch_row($people);
    for ($j = 0; $j < count($person); $j = $j + 1)
    {
        echo '<td>'.$person[$j].'</td>';
    }
    echo '</tr>';
}
echo '</table>';

This example will take all the information from any query and display the information in the form of a table.

However, using an indexed array for each row of a MySQL result is arbitrary and in many cases, creates hard to read code. If you already know the column names, it's usually better to use mysql_fetch_array instead of mysql_fetch_row. Here's an example similar to the last example of the arrays tutorial...

$people = mysql_query("SELECT `name`,`age` FROM `users` ORDER BY `name`);

echo '<h1>People...</h1>';

for ($i = 0; $i < mysql_num_rows($people); $i = $i + 1)
{
    $person = mysql_fetch_array($people);
    
    echo '<p>'.$person['name'].' ('.$person['age'].')</p>';
}
?>

...because $person['name'] and $person['age'] are much easier to understand than $person[0] and $person[1]. Especially after looking back at your code 3 months after you wrote it.

There are many more MySQL functions that PHP provides, but these are actually all you ever really need to do basically...anything.

Next: GET and POST
Back to Tutorial Overview