I find a lot of questions on here relating to MySQL and designing queries, and the first thing that we always tell them is "Don't use the mysql_* functions - they're being deprecated." So I thought I would add a quick guide to setting up a PDO connection in your PHP program so that we can refer new PHP developers here, and help make their programs more secure.
Those experience in PHP and PDO, please feel free to edit and add where necessary to make this more useful for beginners in PDO.
Step 1: Setting up a new PDO connection
This is not nearly as hard as it is sometimes made out to be. To begin with, you can hunt down those mysql_connect/mysql_select_db and replace them with this code:
//Obviously, replace these with your own values
$host = 'host_name';
$dbname = 'database_name';
$user = 'user_name';
$pass = 'user_pass';
try
{
$DB = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
}
catch(PDOException $e)
{
echo $e->getMessage();
}
This will create your PDO object which has all the same functionality of the mysql_* calls and then some.
Step 2: Submitting a query
once you have your PDO object, you can begin using it to query your database. We'll look at a basic select query first, since the techniques we'll use are similar in most query types. Now, you can query directly, but that takes away some of the power of PDO. Instead, we can use prepared statements. By doing so, PDO will work for us to prevent injection or even accidental query breakage. Here's an example:
$query = "SELECT * FROM table_name WHERE col1=':value1' AND col2=':value2'";
$statement = $DB->prepare($query);
$statement->execute(array(':value1' => 1, ':value2' => 2));
At this point, we've queried the database, and have a statement object with the results in it. The bonus here is that, in place of 1 or 2 in the execute statement, we could use a user-generated value, without even checking for SQL injection attempts, because PDO catches them and fixes them automatically. (Though, granted, you should still check that they exist before using user-generated values.)
Step 3: Retrieving the results
Now, we need to get the data we were searching for, so we can use it. With PDO it's quite simple, all you need is to call the fetch command, just like you would have used that mysql_fetch_array() command before. You'll also want to include it in a while loop to retrieve all the results, since it acts almost identically to mysql_fetch_array().
//You can use several options in fetch, to determine what kind of results you get.
//PDO::FETCH_ASSOC -> gives you column names as the array indices
//PDO::FETCH_NUM -> Gives you the column number as the indices
// By default, it uses PDO::FETCH_BOTH which does both.
while($row = $statement->fetch(PDO::FETCH_ASSOC))
{
echo "Col1: " . $row['col1'] . "<br />";
echo "Col2: " . $row['col2'] . "<br />";
echo "Col3: " . $row['col3'] . "<br />";
echo "Col4: " . $row['col4'];
}
Obviously, this is a pretty simple layout, but you can see how it works, and can modify it for your needs. This does exactly the same thing as your current mysql_* code does, except it does it in a simpler more secure manner.
Step 4: The possibilities
From here, you can see how to replace your basic mysql_* functions. You can replace all other mysql functions with PDO calls as well, a few examples are:
mysql_num_rows() == $statement->rowCount() (used after you've executed your query)
mysql_real_escape_string() == You don't even need this anymore!
mysql_insert_id() == $statement->lastinsertid()
The definitive guide to PHP's PDO usage can be found here:
http://us3.php.net/manual/en/book.pdo.php
And here is a question that looks at the strengths and weaknesses of mysqli vs PDO:
mysqli or PDO - what are the pros and cons?
