MySQL and PHP

PHP is a great language for developing websites as it gives you all the tools you'll need in order to make any site you can dream up. If its online then the changes are it can be made using PHP. However, on its own PHP has a limitation. When it coes to storing large amounts of data you really need to look at another application, a database. While it would be possible to create a data storage solution in PHP (using flat text files for example), it would be quite a silly idea. Databases are much faster, easier to use, and they've been created already.

One particular database application the is very well suited to running alongside PHP is MySQL. MySQL is free (with certain licensing restrictions), fast, and quite robust. Whats more, PHP has a suite of functions builtin to take advantage of MySQL, so you're able to jump right in and start working with it right away.

Connecting to a MySQL database

The first thing you need to do in order to use data stored in a MySQL database with your PHP script is connect to the database server. This is usually the same server as your website is stored on, but it doesn't have to be.


The three variables passed to the mysql_connect() statement tell PHP where the database server is, and which database user to connect as. "locahost" in this example is the computer that the script is running on. In this example the web server and database server are the same machine. "user" is a database user that has permission to access the database that we'll be using. Database servers can have many users all with different permissions to access various databases stored on the server. "password" is "user"s database password.

Next we need to tell PHP which database we want to use. In our example we'll be using "shopdb", a database for an online ecommerce store.


In the mysql_select_db() statement we have told PHP to use the "shopdb" database for $connection, the database connection that we set up in step 1.

Now that we have a database selected we're ready to do something with it.

Querying a database

There are lots of different kinds of query that you can run on a database, but we'll concentrate on the three most used: SELECT, INSERT, and UPDATE. A SELECT query is used to pull data out so that you can use it.


This will generate a resultset for all the records in the items table of the database. There are lots of functions in SQL that we could use to alter which records are returned, but thats an exercise in SQL rather than PHP, so we'll save the details of that for a later article.

Once you have a set of results you need to get at the data in them. To do this there are several functions available. Which one you should use depends on your preferred style of coding. There are:

mysql_fetch_array() - This function fetchs each row as an array. What sort of array depends on which type you request in the function call.
mysql_fetch_row() - This function fetchs each row as an enumerated array.
mysql_fetch_assoc() - This function fetchs each row as an associative array.
mysql_fetch_object() - This function fetchs each row as an object.

Whichever you choose you will need to iterate through each row of the results if there is more than one returned.

Move to page: 1 2 3

Comments are not currently being accepted for this article.