MySQL Prepared Statements from PHP: MySQLi vs PDO
July 21st, 2009I’ve been using my simple MySQLi wrapper class for some time now, and feel that it’s been reasonably well fine-tuned. It’s been used on several live projects, and is working extremely well. Most importantly, it has helped to simplify my code enormously. It allows me to execute parameterised SQL statements with a single line of code, and return the results as an object or array.
A little while ago I also started investigating PHP Data Objects (PDO). PDO provides much the same functionality as MySQLi (with the advantage of being relatively database independent), and also provides a really nice set of additional functions such as fetching a result set into an existing object. I’ve created a wrapper with an identical interface as the earlier MySQLi version, which encapsulates PDO functionality to a MySQL database. This is a drop-in replacement to the MySQLi version, and has allowed me to benchmark the differences in performance. In the process of checking out PDO, I’ve learned a couple of interesting things:
1) Unrelated to databases, but PHP does allows properties to be added to objects dynamically at runtime. You may have already known this, but it was news to me. Previously I’ve been using the magic __get and __set functions. Instead, I could have just created a new instance of StdClass, and added my properties to it as so:
$obj = new StdClass();
$obj->myProperty = 17;
$obj->anotherProperty = array('Blue', 1.712);
Kris Jordan thinks that using __get and __set is around 30 times slower than using dynamic properties on StdClass.
2) The PDO wrapper class seems to be quicker by a significant margin. I’ve timed the execution time for various pages (by differencing the results of a call to “microtime()” at the start and end of each script) and the PDO version is consistently quicker. For some sites, PDO reduces script execution time by nearly 50%.
To help simplify usage, I’ve bundled everything together, defined an interface that exposes the relevant access methods, and added a few factory methods to an abstract base class:
interface DFData
{
function Close();
function StartTransaction();
function CommitTransaction();
function RollbackTransaction();
function FetchRow($sql);
function FetchRowAssoc($sql);
function FetchSingleObj($sql);
function FetchObj($sql);
function ExecuteSQL($sql);
}
abstract class DF_DataObject implements DFData
{
public static function PDOInstance($username, $password, $database, $servername = 'localhost') {...}
public static function MySQLiInstance($username, $password, $database, $servername = 'localhost') {...}
}
I haven’t been playing with this for long, but so far it’s proven to be robust, reliable, and in the case of the PDO version, quick.
Once again, for those that have missed the earlier posts, the wrapper class allows to you eliminate SQL injection attacks by providing a really easy way to execute parameterised queries:
$db = DF_DataObject::PDOInstance('username', 'password', 'databasename');
// Insert statements
$db->ExecuteSQL('INSERT INTO Fruit (Name, Colour) VALUES (?, ?)', 'ss', 'Banana', 'Yellow');
$db->ExecuteSQL('INSERT INTO Fruit (Name, Colour) VALUES (?, ?)', 'ss', 'Apple', 'Green');
$db->ExecuteSQL('INSERT INTO Fruit (Name, Colour) VALUES (?, ?)', 'ss', 'Lemon', 'Yellow');
$insertID = $db->ExecuteSQL('INSERT INTO Fruit (Name, Colour) VALUES (?, ?)', 'ss', 'Orange', 'Yellow');
// Update statements
$db->ExecuteSQL('UPDATE Fruit SET Colour = ? WHERE ID = ?', 'si', 'Orange', $insertID);
// Select statements
$obj = $db->FetchSingleObj('SELECT Name, Colour FROM Fruit WHERE Colour = ?', 's', 'Green');
/*
$obj->Name = 'Apple'
$obj->Colour = 'Green'
*/
while ($fruit = $db->FetchObj('SELECT Name, Colour FROM Fruit WHERE Colour = ?', 's', 'Yellow'))
echo $fruit->Name;
/*
Banana
Lemon
*/
$arr = $db->FetchRowAssoc('SELECT Name, Colour FROM Fruit WHERE Colour = ?', 's', 'Orange');
/*
$arr = array('Name' => 'Orange', 'Colour' => 'Orange');
*/
Source code is here
July 24th, 2009 at 12:26 am
Very nice, thanks man. I’ll have a look and see if it works for me.
Found one bug though. The second line of the FetchRowAssoc function in the PDO class should be:
$this->Query = $this->PDO->prepare($sql);
You forgot the PDO.
Regards,
Bas
July 24th, 2009 at 9:20 am
Thanks Bas – I really appreciate all your debugging! Source updated.
August 12th, 2009 at 7:46 pm
Cheers for this, it’s really helped me a lot… just a couple of questions. Is there any sort of attribution I should place in the code or somewhere on the commercial site it will be used on?
Also I have implemented a function which fills a combobox form element with the results of a query. This works fine until I have 2 comboboxes on the same page which use the same function but with different parameters (i.e. grouptype=2 then grouptype=3) and one of the queries returns no results. The first combobox appears correctly, then I get the following php error instead of the second:
Fatal error: Call to a member function fetch() on a non-object in dbconnect.php on line 98.
I managed to fix this error and get the right results by adding $this->LastSQL=”"; under line 100 (and adding the brackets to the now 2-line if statement).
Is this the correct approach to solve the problem or will I have introduced other problems by doing this?
Many thanks,
Ash
August 12th, 2009 at 8:29 pm
Hi Ash,
Glad you’re finding the code helpful. Feel free to link to this page in your source code, but it’s not a requirement.
Your solution will work just fine. I’ve just added an updated version of the code that solves exactly this issue (although in a slightly different way), plus a few other changes:
1) Fixed bug: PDO normally doesn’t require parameters types, the only exception being for parameters in a LIMIT clause. I’ve had to include parameter types for all parameters to account for this.
2) Fixed bug where EDuplicateKey wasn’t being raised correctly by the PDO class.
3) Improved performance for repeated calls to “ExecuteSQL”
4) Added a Reset() function to the interface.
Cheers,
Rob
August 14th, 2009 at 11:47 pm
Thanks for the updates. Was just going to report the Duplicate Key issue, but I see you’ve already solved it. I also added a few functions to use custom classes. I’ll post them below, they might be of use to someone. These functions return a class of the type you specify in $className. This is usefull if you want to use your own classes and extend them.
public function FetchSingleClass($sql, $className)
{
$this->Query = null;
$this->Query = $this->PDO->prepare($sql);
$args = func_get_args();
array_shift($args);
array_shift($args);
$this->BindParams($args);
$this->Query->execute();
$this->Query->setFetchMode(PDO::FETCH_CLASS, $className);
return $this->Query->fetch(PDO::FETCH_CLASS);
}
public function FetchClass($sql, $className)
{
if ($this->LastSQL != $sql) {
$this->Query = null;
$this->LastSQL = $sql;
$this->Query = $this->PDO->prepare($sql);
$args = func_get_args();
array_shift($args);
array_shift($args);
$this->BindParams($args);
$this->Query->execute();
}
$this->Query->setFetchMode(PDO::FETCH_CLASS, $className);
$obj = $this->Query->fetch(PDO::FETCH_CLASS);
if ($obj === false)
$this->Query = null;
return $obj;
}
August 15th, 2009 at 2:59 am
HI Rob, i’ve been following the progression of your pdo interface since your first blog post. Great job.
Quick Question, what’s the best way to get a row count in relation to performing a select statement.
I’ve tried several ways but i can’t seem to get it working
August 18th, 2009 at 8:38 pm
Hi James,
Good question. The database classes are really built from a slightly different perspective – ie being able to write a parameterised query in a single line of code. In order to allow this simplicity, I’ve had to hide away concepts such as result sets, PDOStatement objects and mysqli_stmt objects. This means that I’m not really sure where you’d be able to use the number of rows returned from a select statement.
When I’ve needed to know the number of rows, I’ve almost always used something like this:
$colour = ‘yellow’;
$numRows = $db->FetchRow(“SELECT COUNT(1) AS NumRows FROM tblFruit WHERE Colour = ?’, ‘s’, $colour);
if ($numRows > 0)
{
while ($fruit = $db->FetchObj(“SELECT ID, Name FROM tblFruit WHERE Colour = ?’, ‘s’, $colour))
echo “$fruit is $colour\n”;
}
else
echo “No $colour fruit were found\n”;
or alternatively:
$numResults = 0;
$colour = ‘yellow’;
while ($fruit = $db->FetchObj(“SELECT ID, Name FROM tblFruit WHERE Colour = ?’, ‘s’, $colour))
{
$numResults++;
echo “$fruit is $colour\n”;
}
if ($numResults < 1)
echo “No $colour fruit were found\n”;
Granted, it isn’t really the most efficient way to go about things, although for simple queries the overhead is negligible. Rather than restrict what is possible, I’ve added a new method to the class called “GetConnectionObject”. This returns the underlying connection object (either an instance of PDO or MySQLi), and you can then use “long-hand” techniques to query the database and get the number of rows in a result set.
Hope this answers your question.
RRP
August 18th, 2009 at 8:40 pm
Thanks Bas.
I’ve uploaded a new version which includes your additions, and also implemented versions for the MySQLi class.
September 9th, 2009 at 1:58 am
Hi Rob,
Just downloaded your code – question regarding the current file up for download.
Something tells me that the code isn’t supposed to repeat itself.
With the exception of the method GetConnectionObject( ) in the first instance of the code being named different to the method GetConnection( ) in the second instance of the code – everything seems to be the same – and not to mention in the file twice.
Maybe copy-paste got the best of you while updating your code?
Anyway – check out line 659 – I think you will get what I mean.
It’s 1am – so if I’m wrong, sorry for the trouble.
Cheers.
September 9th, 2009 at 7:25 am
Hi James,
Thanks for spotting that. I’ve removed the duplicated code and reposted. Like you, I seem to post this stuff in the early hours, so I apologise for any bugs in the code.
Cheers,
Rob
October 31st, 2009 at 8:31 am
Hi Rob – this looks like good stuff.
I started testing with the MySQLi version, and selects appear to be fine, but when I try to do an insert like so:
$db->StartTransaction();
$insertID = $db->ExecuteSQL('INSERT INTO contracts (CONTRACT_NAME, USER) values (?,?)', 'ss', 'A Test Contract' , 'A Test User');
I catch an exception with the message: “Bind parameters failed” and this is logged:
[30-Oct-2009 16:03:26] PHP Warning: call_user_func_array() [function.call-user-func-array]: First argument is expected to be a valid callback, 'Array' was given in /workspace/testapp/dbconnect.php on line 312
Any ideas? I'm testing in PHP 5.2.3 and MySQL 5.0.41 under MAMP
Cheers, Michael
October 31st, 2009 at 6:01 pm
Hi Michael,
Thanks for the comment. Chances are the exception is thrown due to an SQL error. I managed to reproduce this if I deliberately mis-spelled a column name in an insert statement. Whilst your sample code looks fine, is it possible that your column names are spelled incorrectly, or perhaps you have omitted a field that was declared as “not null”?
I’ve modified the MySQLi version of the code so that it now throws an exception if the query fails in the “prepare” stage – this may help you in your debugging. Let me know if this helps solve the problem.
November 2nd, 2009 at 5:42 pm
I’m used your code is very well in kooru.com. It’s perfomance in query.
November 2nd, 2009 at 5:46 pm
Hi Lulu.
Thanks for your comment. Nice site – very glad you found the code useful.
December 2nd, 2009 at 12:16 am
Hi Rob,
I’ve implemented this in my own site, and using PDO is working just fine for me (see http://www.lenocin.com .. though not much going on there at the moment)
I don’t see an rss feed link for this page, and was wondering how one can be sure to be updated if any changes to this class? (Perhaps google code would be a good home for this magnificent work)
Once again, Fantastic work and on behalf of everyone using it, thank you very much.
December 2nd, 2009 at 1:16 am
Anyone successfully do nested queries and loops? Like this:
$db = DF_DataObject::PDOInstance(DB_USER, DB_PASSWORD, DB_NAME,DB_SERVER);
while ($category = $db->FetchObj(“SELECT * FROM post_categories”)) {
echo(“Category: “.$category->category);
while ($subcategory = $db->FetchObj(“SELECT * FROM post_sub_categories WHERE category_id=?”, ‘i’, $category->id)) {
echo(“– Sub-Category: “.$subcategory->sub_category);
}
}
Either of the above queries on it’s own are working out just fine. Though nesting them inside each results into an infinite loop.
For what it matter my post_categories table have only one record (Code Samples) and the post_sub_categories table 2 subcategories (jQuery and PHP). Running the above above result in the expected results printed out forever.
Category: Code Samples– Sub-Category: jQuery– Sub-Category: PHPCategory: Code Samples– Sub-Category: jQuery– Sub-Category: PHP
December 2nd, 2009 at 6:44 am
Unfortunately nesting queries like this is not possible with a single database connection. When dealing with master/detail problems such as this, I’ve been using code along these lines:
$currentCategory = null;while ($obj = $db->FetchObj('SELECT C.category, S.subcategory FROM post_categories C INNER JOIN post_sub_categories S ON S.category_id = C.id ORDER BY C.id'))
{
if ($currentCategory != $obj->category)
{
$currentCategory = $obj->category;
echo "Category: $obj->category\n";
}
echo "-- Sub-Category: $obj->subcategory\n";
}
unset($currentCategory);
This approach has the advantage of using only a single query, instead of 1 + the number of categories in your table.
December 15th, 2009 at 3:34 am
Hi Rob,
Still using your class regularly. I’ve run into the problem that you can’t execute the same query twice. I knew this wasn’t possible, however it seems it’s also not possible to run the same query with different parameters. This seems like a bigger problem. One of the advantages of using prepared queries is the ability to run the same query with different parameters in an efficient way. I think the class should check for both changing parameters and a different query. I’ve used the following altered method for fetching an associative array. Other methods could be changed in the same way. This will prepare a query only once when only the parameters are changed between funtion calls.
public function FetchAssoc($sql)
{
if (!isset($this->Query) || ($this->LastSQL != $sql)) {
$this->LastSQL = $sql;
$this->Query = $this->PDO->prepare($sql);
}
$args = func_get_args();
if (!isset($this->LastParams) || ($this->LastParams != print_r($args, true))) {
$this->LastParams = print_r($args, true);
array_shift($args);
$this->BindParams($args);
$this->Query->execute();
}
$obj = $this->Query->fetch(PDO::FETCH_ASSOC);
if ($obj === false)
$this->Query = null;
return $obj;
}
Haven’t tested this extensively, but I think it would be an improvement. Let me know what you think.
Regards,
Bas
December 15th, 2009 at 7:50 am
Hi Bas,
Thanks for sticking with the code! I’ve been aware of the problem you’ve discovered for a while now. It’s more of a conceptual problem than an issue with the code, and it arises entirely due to the fact that I’m trying to make things too simple.
As you’ve pointed out, you can’t run the same query twice in a row (even when using distinct parameters). When I’ve come across these instances, I use
$db->Reset();immediately before the second query, but you don’t always spot these cases straight away.It’s still early in the morning here, but I’m getting an idea to resolve this very easily, without the need to check parameters each time… I’ll post an update later on.
Thanks,
Rob
January 7th, 2010 at 3:24 am
How would you go about changing the constructor to be truly abstract and work with something other than mysql (ie: sqlite), which does not require some of the params you pass to the constructor, like user and pass, and has a different prefix?
January 7th, 2010 at 4:21 pm
[...] digging around a while for samples, I found this brilliant piece of work by Rob Poyntz I decided to start out with his database wrapper class, and extend and modify it to my [...]
January 13th, 2010 at 11:09 pm
Hi Andrew,
New version coming soon that addresses this issue and others. Will keep you posted.
March 12th, 2010 at 12:30 pm
Thanks so much! I wanted prepared statements but didn’t want to manually bind individual variables for each result variable. Tried Doctrine, didn’t like it. This helps A LOT.
March 12th, 2010 at 12:54 pm
Line 382 and 383 are failing:
if ($query = $this->MySQLi->prepare($sql))
throw new EDatabaseException($this->MySQLi->error);
with this generic error:
Fatal error: Uncaught exception ‘EDatabaseException’ in Path\to\dbconnect.php:383 Stack trace: #0 Path\to\file.php(line#): DF_MySQLi->FetchRowAssoc(‘SELECT * FROM u…’) #1 {main} thrown in Path\to\dbconnect.php on line 383
March 12th, 2010 at 1:10 pm
T,
This seems to be failing on the MySQLi->prepare($sql) line – prepare sends your query to the database engine for compilation, so if an error is occurring here it’s likely that your SQL statement is invalid. What is the error code that’s being reported?
If you can send me the SQL, table definition and parameters you’re using, I might be able to spot the problem.
March 13th, 2010 at 1:01 am
The query is simple: “SELECT * FROM `users`”
I didn’t even use any parameters, and when I try something like “SELECT * FROM `users` WHERE 1=?” and pass 1 as the parameter, it still doesn’t work. Table definition is simple, something along the lines of:
Table `users`:
users_ID | Username | Created | LastModified
There are a few lines in the source code of your class that might be causing the problem. Namely, these lines of FetchRowAssoc:
if ($query = $this->MySQLi->prepare($sql))
throw new EDatabaseException($this->MySQLi->error);
Appear to be related to these lines in FetchRow:
if (!$query = $this->MySQLi->prepare($sql))
throw new EDatabaseException($this->MySQLi->error);
However, there is no not operator in the FetchRowAssoc implementation. Should there be? Also, in FetchRow:
if ($this->MySQLi->errno != 0) throw new EDatabaseException($this->MySQLi->error);
That line appears related to this line in FetchRowAssoc:
if ($this->errno != 0) throw new EDatabaseException($this->error);
But in the FetchRowAssoc version, $this->[property] is used instead of $this->MySQLi->[property].
I changed those two things in FetchRowAssoc and everything worked up to these lines where it failed:
if (!call_user_func_array(array($this->Query, ‘bind_result’), $bindparams))
throw new EDatabaseException(‘Bind parameters failed’);
It was either those lines, or the other two lines that binded parameters.
March 14th, 2010 at 10:58 pm
Hi T,
It looks like there could well be a few problems in that code. If you can please bear with me for another day or so, I’m about to release a new version (and a new site to go with it). The current version is really just a private project, and that parts of the code I don’t use myself aren’t well tested. The new version will be fully tested, supported, and released under a sensible license.
March 15th, 2010 at 7:21 pm
T,
Please check out http://easypdo.robpoyntz.com – latest version which should have ironed out most of those issues.
Cheers,
RRP
March 17th, 2010 at 4:56 pm
What different EasyPDO and old code ? I’m will change my code. But i’m think old code is well work for me. It’s great.
March 17th, 2010 at 5:06 pm
Hi Lulu,
There are a few major differences in the new code:
1) The API has changed – for example instead of (while $obj = $db->Fetch()) from the old code, EasyPDO uses foreach ($db->Fetch() as $idx => $obj).
2) The new code does not support PHP’s MySQLi drivers – mainly because PDO/MySQL is quicker
3) EasyPDO supports more database engines, currently MySQL, PostGres and SQLite
If the old code is working for you, then there’s no need to change!
March 17th, 2010 at 6:11 pm
Thank you for quick answer and best code.