This is an old post from 2012. As such, it might not be relevant anymore.
MySQL is/was deeply integrated within PHP. No doubt you have seen and used the mysql_*() functions at some stage, but there is an alternative that makes your database interactions much more reliable. It’s called PDO (PHP Data Object) and here’s a quick guide that should get you up to speed in no time at all.
The number one reason is security. With plain MySQL functions you need to sanitise the input manually using type casting or mysql_real_escape_string(). With PDO this is all taken care for us and means we no longer have to worry about SQL injection. Great.
The ability to easily migrate to a different database engine (SQLite, Oracle, MSSQL, ODBC, etc.) is the cherry on top of the cake.
Connecting is just as easy as in standard MySQL functions, it just looks slightly different. We wrap the connection attempt in a try-catch statement so we can gracefully handle errors. E.g.
try {
$dbh = new PDO("mysql:host=localhost;dbname=my_db_table", 'username', 'password');
} catch(PDOException $e) {
echo $e->getMessage();
}
One thing I always do is instantly tell PDO I want associative arrays returned. I prefer them to objects, but your milage may vary. I also say that we want to deal exclusively in UTF8.
// Connection string, UTF-8 encoding, and fetch associative arrays
$dbh = new PDO("mysql:host=localhost;dbname=my_db_table;charset=utf8", 'username', 'password');
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
Finally, some database interaction which lets us use the benefit of PDO – prepared statements. Essentially we insert placeholders into our SQL and define a value for those outside the statement. We do not, I repeat, we do not ever place user input directly into the SQL. This is susceptible to SQL injection.
Secondly, there are two ways to define these placeholders in your SQL statement: Positional and Named. You only ever want to use Named placeholders. Positional are, quite frankly, a silly idea. Positional placeholders ultimately will end up in inserting data in the wrong columns, or selecting data using an incorrect parameter. I’m only going to show you the correct way (in my opinion) so as to not give you bad habits.
// Define the SQL with named placeholders
$sth = $dbh->prepare("INSERT INTO `table` (`id`, `title`) VALUES (:id, :title)");
// Execute the query, replacing the placeholders with their true value
$sth->execute(array(
':id' => 123,
':title' => 'I am the title'
));
You can see that we create a variable $sth (statement handler) which calls the $dbh (database handler) prepare method. The SQL should look pretty standard until we reach the :id and :title. These are our named placeholders. We can run this query by calling the execute() function which takes an array of the placeholder data. Quite simple.
The benefits of this are that our data is automatically escaped – we do not have to worry about calling mysql_real_escape_string(), after all we might not be using MySQL. Also, the benefits are that we can call the execute() functions many times without re-declaring the SQL statement, which will speed up our code execution.
// Insert three rows into the table
$sth->execute(array(':id' => 123, ':title' => 'First title'));
$sth->execute(array(':id' => 456, ':title' => 'Second title'));
$sth->execute(array(':id' => 789, ':title' => 'Third title'));
Now that you have just mastered inserting data, selecting is a piece of cake, it’s very similar, and also gives us a chance to go over a few new functions.
// Select some data
$sth = $dbh->prepare("SELECT `id`, `title` FROM `table` WHERE `id` > :country_id");
// Execute the query, replacing the placeholders with their true value
$sth->execute(array(
':country_id' => 200
));
// How many records did we find?
echo 'We found ' . $sth->rowCount() . ' records.';
// Loop over the data
while ($row = $sth->fetch()) {
echo 'My ID is ' . (int)$row['id'] . ' and my title is ' . htmlentities($row['title']);
}
To get the number of rows returned you can use the rowCount() function (akin to the mysql_num_rows()), and to loop over the data you can use the fetch() function (akin to the mysql_fetch_assoc()). If you wanted to return all of the rows into a variable without performing a loop you can do fetchAll(). Otherwise there are no gotcha’s here.
You’re going to be bored of me saying this, but there is nothing different here than the previous examples:
// Update a row
$sth = $dbh->prepare("UPDATE `table` SET `title` = :title WHERE `id` = :id LIMIT 1");
// Execute the query, replacing the placeholders with their true value
$sth->execute(array(
':title' => 'I am the newly updated title',
':id' => 123
));
// Did we manage to update the row?
if ($sth->rowCount() >= 1) {
echo 'Yup, the row was updated.';
} else {
echo 'Nope, row was not updated.';
}
Again, nothing too complex:
// Delete a row
$sth = $dbh->prepare("DELETE FROM `table` WHERE `id` = :id LIMIT 1");
// Execute the query, replacing the placeholders with their true value
$sth->execute(array(
':id' => 123
));
// Did we manage to delete the row?
if ($sth->rowCount() >= 1) {
echo 'Yup, the row was deleted.';
} else {
echo 'Nope, row was not deleted.';
}
This comes down to preference. I prefer the execute() because we are avoiding function calls, speeding up our code, and easier to update. But I’ll go over all three.
bindParam() is asking for a reference. It is not asking for an immediate value, it only receives the value once the execute() function is called. This is probably the most confusing aspect of PDO, so let me give you an example.
// Select some data
$sth = $dbh->prepare("SELECT `id`, `title` FROM `table` WHERE `id` > :country_id");
// Set binds
$sth->bindParam(':id', $tableId);
// Define variables
$tableId = 123;
// Execute
$sth->execute();
This works because, although $tableId does not exist when referenced, it is defined before our execute() call. The reason I do not like this is because it is leaving an uncertainty to the statement. You could quite easily bind the parameter, then change the variable for some reason and cause an error. You also have the problem that literals can not be used since they cannot be referenced. So, bindParam(':id', 123) would result in an error.
bindValue() to the rescue! This works in the same way as bindParam(), but it uses the value straight away, be it a literal or a variable.
execute() is what we have been using in the previous code samples, so does not need an introduction.
exec(), execute(), and query()
These all do the same thing, run a query. But they do so in different ways. In my opinion you should only ever use execute() as it is the only one which works with prepared statements which, in time, the other two can become susceptible to SQL injection. They also have their nuances, exec() can only be used for queries that do not return data objects (e.g., SELECT statements), and generally speaking query() is only used on SELECT statements. But since you cannot use prepared statements (e.g., placeholders), I wouldn’t bother with them.
You now know the basics of PDO. You can see it isn’t a scary monster and it really is easy to switch. You now know how to insert, select, update, and delete rows. You know how to safely pass in parameters using placeholders (remember, named not positional!) and so you now never have to worry about SQL injection.