PHP and MySQL Programming/Database Connectivity

You can only do so much with storing information in files. When you need to store large amounts of data, and perform intensive number crunching on that data, there is nothing better than a good SQL database. In this section, we'll discuss connecting PHP to a MySQL database and perform queries and retrieve data back from the database.

Opening a Connection to a MySQL Database
The first thing that we need to do before we can interact with any database, is to open up a connection to that database server. This is done by using the mysql_connect function, which returns a database handler, much like a file handler when dealing with files. The database handler is then used to select the active database to use.

Here is the code to setup a connection to the database server, and to select a database to use:

Or

Creating a Query
Once a connection to a database has been made, you will inevitably want to perform a database query. To create a query on the selected database, we use the mysql_query function. If you use a SELECT query, then the data returned from that query will be passed to the mysql_query function, which will in turn return it to a variable which you can specify. In the following example, two queries are made, the first does not return any data, and the second does.

Retrieving data from a SELECT Query
There are many methods for retrieving data from a SELECT query.

If we take, for example, the following code:

To now retrieve the data from the $result variable, we can use one of many methods. The recommended method, however, is to sequentially go through each row of the table, storing it into a one-dimensional array. We do this by using the mysql_fetch_row function, passing it the variable into which the result is stored. Here is a simple example:

This will simply output the result in table-like format.

Here is another example of using this method:

The above example, simply splits the results up into multiple one-dimensional arrays, for easy manipulation.

Closing a Database Connection
It is not always necessary to close a connection when you are finished, but it is advised. It is, however, necessary to close the connection to the database if you want to open up a new connection to a different database.

To close a connection to a database, we use the mysql_close function, as follows:

Error Handling
It is useful when debugging, and even when you just want to make sure that a database does not behave unexpectedly. Once a query has been created via the <tt>mysql_query</tt> function, any error messages generated will be stored in the <tt>mysql_error</tt> function. Here is a sample code snippet to display a error message. However, when there is no error messages, a blank string is returned.