Using MySQL with PHP – Part 1

by Curtiss   |   last updated March 5, 2009   |   PHP Tutorials ·

This is a very simple tutorial intended to explain how to connect to a MySQL database with PHP and how to retrieve and manipulate information from that database.

Prior Steps

Before you actually connect to a database, you need to have a database to which to connect. Further, you need to have a table inside of that database that contains information. I will write up a simple tutorial explaining how to create new databases and add information to them in another tutorial, soon.

You will also need to know the username and password for a MySQL user that has, at least, SELECT permissions on the database and you will need to know the name of the “local” MySQL host (generally “localhost,” but it will sometimes deviate to things like “MYSQLHOST” or something like that). If worse comes to worse, you can try using the IP address of your Web server, if you know it.

Additionally, you need to have PHP installed and set up with the MySQL module set up (which, if you are not setting up your own Web server, is not generally something you ever have to worry about).

Connecting to the MySQL Database

To connect to the database, you will need to begin by setting up a “MySQL Link Resource Identifier” within PHP. You do that by using the mysql_connect() function. Your code would look something like the following. You will need to assign that “link identifier” to a variable so you can use it, later.

$link = mysql_connect('localhost','mysqluser','mysqlpassword');

In the function above, “localhost” should be replaced with the name of the MySQL host your Web host uses (although, as mentioned above, most Web hosts will leave that set as “localhost”). The term “mysqluser” should be replaced with the username that has the appropriate permissions for the database and “mysqlpassword” should be replaced with the password for that MySQL user.

Once you have opened the connection, you should specify which database you plan to use. You don’t absolutely have to do this (as long as your SQL queries include the database names), but it can save you a lot of time and hassle later. You do so by using the mysql_select_db() function. This function simply returns boolean true or boolean false, so you do not need to assign the result to a variable.

mysql_select_db('databasename',$link);

In the function above, you should replace “databasename” with the actual name of the database to which you are connecting. Once you have run that function, you will have a connection established to the MySQL server and you will have opened the specific database you wish to manipulate.

Retrieving Information from the MySQL Database

Now that you’ve connected to your database, you probably want to do something with it. Here, I will show you how to retrieve information from your MySQL database and I will go over some simple things you can do with that information.

In order to begin, you need to use the mysql_query() function. Inside the mysql_query() function, you need to write an SQL query. Let’s begin with something simple that selects all of the information from our table, which we’ll call “mytable” for this example. When used with SQL queries that are meant to return results, the mysql_query() function returns a resource identifier, so you need to assign the result to a variable.

$sql_query = mysql_query("SELECT * FROM mytable");

That, on its own, doesn’t really do anything, though. We’ve run the query, but we haven’t actually retrieved anything, yet. To do that, we need to use one of the mysql_fetch functions. There are three different mysql_fetch functions that may be of use to you. They each have their own specific uses, so be certain to use the correct one for your application. Following is an example using the mysql_fetch_array() function, but all three functions are set up the same.

if($sql_query = mysql_query("SELECT * FROM mytable")) {
    while($rs = mysql_fetch_array($sql_query)) {
        // Perform actions related to the result set
    }
}

The mysql_fetch_array() function will retrieve the information from the database, row-by-row, in two formats. The first format is a non-associative array, using numbers (starting at 0) as the keys to the array. The second format is an associative array using the column names as the keys to the array. Therefore, when you use the mysql_fetch_array() function, you can access the information retrieved from the database by using either numerical keys or named keys.

For instance, if there are three columns in your database, named “colA,” “colB” and “colC,” respectively, then the array retrieved by the mysql_fetch_array() function would look like:

resultset = array(
    0=>'Information from colA',
    1=>'Information from colB',
    2=>'Information from colC',
    'colA'=>'Information from colA',
    'colB'=>'Information from colB',
    'colC'=>'Information from colC'
);

The mysql_fetch_row() function will simply retrieve an array with numerical keys. For instance, using the same table example mentioned above, the mysql_fetch_row() function would return the following array.

resultset = array(
    0=>'Information from colA',
    1=>'Information from colB',
    2=>'Information from colC',
);

The mysql_fetch_assoc() function will retrieve an associative array using the column names as the keys to the array. For instance, using the same table example mentioned above, mysql_fetch_assoc() would return the following array.

resultset = array(
    'colA'=>'Information from colA',
    'colB'=>'Information from colB',
    'colC'=>'Information from colC'
);

Obviously, the easiest function to use is the mysql_fetch_array() function. However, unless you have a specific need to access your columns both numerically and by column name, I wouldn’t recommend it, as it potentially could add a lot of overhead to your script. Instead, I would recommend choosing which of the other two functions makes the most sense for your application.

Manipulating the Data Retrieved

So, now you’ve retrieved the data, but you don’t know what to do with it, yet. I will begin by showing a simple example that creates an HTML table out of your results.

<?php

echo '
<table>
    <thead>
        <tr>
            <th scope="col">Column A</th>
            <th scope="col">Column B</th>
            <th scope="col">Column C</th>
        </tr>
    </thead>
    <tbody>';

if($sql_query = mysql_query("SELECT * FROM mytable",$link)) {
    while($rs = mysql_fetch_assoc($sql_query)) {
        echo '
        <tr>
            <td>'.$rs['colA'].'</td>
            <td>'.$rs['colB'].'</td>
            <td>'.$rs['colC'].'</td>
        </tr>';
    }
}

echo '
    </tbody>
</table>';

?>

That’s really all there is to it. Pretty simple, eh? Here are a few things to keep in mind:

1. It’s not a good idea to keep your database connection string in the same file with the rest of your code. In fact, if at all possible, keep it in a file that’s not generally accessible to normal Web users. It’s also not a bad idea to store the username and password separately from the connection string and even to encrypt the username and password in some way.
2. If the MySQL table is empty (or if the particular query you run returns no results), the while loop associated with the mysql_fetch function will never run. Therefore, you should have some sort of contingency plan, just in case your query returns an empty result set.
3. The “resource link identifier” is an optional part of the mysql_select_db() and mysql_query() functions. If you don’t include the resource link identifier, PHP will automatically attempt to use the most recently opened connection to the MySQL server.
4. PHP automatically closes all connections to the MySQL server and frees up all results when the PHP script stops running. However, it is usually a good idea to close the connection as soon as you’re finished using it (mysql_close()), and to free up your result sets as soon as you are done using them (mysql_free_result()).