Making It Your Web

PHP and MySQL Tutorial (Updated)

Quite a while ago now I posted a blog about how to interact with a database to save the star ratings from my Star Rating Script built with YUI. I recommend you do not follow the guidelines in that post any more because it uses an old method of database interaction with PHPs built-in mysql_* functions, which are really not secure any more and deprecated in the newest version of PHP.

What you should be using for database interactions from PHP these days is what is known as PDO, which stands for PHP Data Objects. From the PHP website:

The PHP Data Objects (database-specific PDO driver to access a database server.

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

PDO ships with PHP 5.1, and is available as a PECL extension for PHP 5.0; PDO requires the new OO features in the core of PHP 5, and so will not run with earlier versions of PHP.

This is quite complicated, but is basically an encryption layer built into PHP so that when you pass data to a database you don't have to worry about escaping anything. I would highly recommend adding your own validation layer to your data before even passing it to your PDO object, but that is for another blog post.

An example

To setup a new PDO object that your application can use all you need is the following line:

$objPdo = new PDO('mysql:host=dbhost;dbname=dbname', 'dbuser', 'dbpass');

You'll need to change a few things in the above for your own database:

  • dbhost - your database host (usually localhost, but could be an external IP)
  • dbname - the name of the database to connect to
  • dbuser - the username of the database user you're connecting with
  • dbpass - the password for that user to connect to the database

Once that's all done $objPdo is now an object that you can use to access your database to read from, or write to. So, how do we do that? Well, quite simply, the following line will query the server with the given SQL and return you an array of data (or false if the query failed).

$objSth = $objPdo->query("SELECT * FROM your_table");
$arrResponse = $objSth->fetchAll();

Pretty simple, huh? In the first line we tell the PDO object that we want to query "your_table", which creates an object that we can then do stuff with. fetchAll() is called on that object to query the database and return everything it finds (or false on error). Depending on the data in your table you'll either get an array of the data, or an empty array. If the query fails for whatever reason (e.g. your_table doesn't exist) $arrData will just be false.

There are also plenty of options you can pass with fetchAll() to get different datasets. Check out this link to see which. I like to call $objSth->setFetchMode(PDO::FETCH_ASSOC); before fetchAll() to only return the associative names of the columns in the table, otherwise you'll get both the columns names and their relative indexes as array keys. For example, without use FETCH_ASSOC you could get something like:

	"name" => "A name",
	"0" => "A name",
	"email" => "",
	"1" => ""

One last thing with fetchAll() here. You can use $sth->rowCount(); to give you the number of rows that have been returned by a successful query.

How about adding data to the query?

You can add data to the query by passing an array of parameters and using placeholders for where the parameters should appear in the query. For example, say you wanted to fetch a user record where the name matches "John Doe". Your query would look something like:

$objSth = $objPdo->prepare("SELECT * FROM your_table WHERE name = :NAME");
$arrData = array("NAME" => "John Doe");

Notice that we don't put the name "John Doe" in the query at all, we just pass a reference that we later assign the string "John Doe". This reference can be anything you want but should probably be something relating to the field you want to query for brevity. The array on the next line then gives that reference a value that we want to pass to the query. This is usually a variable (for example from a post array from a form). To add more you simple add the placeholder and then add more items to the parameter array ensuring the names match;

The query to then select data from the database is slightly different. Rather than just query we need to merge the parameters with their value and PDO does this in a way that secures and encrypts the data removing any erroneous characters or potentially harmful SQL injections. For this we use the prepare statement followed by execute, such as:

$objSth = $objPdo->prepare("SELECT * FROM your_table WHERE name = :NAME");
$arrData = array("NAME" => "John Doe");
$arrResponse = $objSth->fetchAll();

And there we have it! With two extra lines we have a secure query to the database passing data. Exactly the same method can be used for inserts, deletes and updates. The only difference is that, with an insert the ID of the inserted row will be returned and with update either true or false will be returned.

One final point

If you would like to just return one element from the database (for example where you expect there to be only one result or only want the first result to be returned) you can use fetch() instead of fetchAll(). fetchAll() will always return a multi-dimensional array (where the query is successful and there is more than one results) whereas fetch() will only return a single array of the data.

There is a heck of a lot more to PDO than this. This is merely a very brief introduction. I recommend checking out the extensive manual on and having a good read through and use it where you can! Update any legacy mysql_* code to use PDO and you'll immediately be adding a layer of security to your applications.

Comments (be the first)

Add A Comment

Please note: all comments are moderated before they are published on this page.

Cancel Reply