Update: Old Technical Post. The findings, information and discussion might be outdated, useless or plain wrong given the current and fluid area of the topic in this post.
Okay, this is less of a MySQLi vs PDO debate and more of why I don’t like PDO. MySQLi and Mysql extensions are very simple to use and work with. It is also fairly easy to find errors, either SQL or PHP. PDO introduces a new method of dealing with databases data and it is going to take a lot of tutorials and help on getting all of the little things that will bite you in the ass.
I kind of totally rewrote the entire thing, partly because I didn’t feel like creating a new post correcting myself.
The PHP.net Examples
…And God said that PDO would be and it was good.
The php.net examples give an introduction of how it is possible to use and abuse PDO, but it doesn’t dive in depth as to how to fix issues that will come up other than the ones that will be thrown as an exception or error. My conflict and confusion is in the usage of bindParam(), bindValue(), PHP variables, and how PDO uses them.
Helpful Resources
The PDO slides, if you can find them are a great deal more helpful than the php.net examples are, in my humble opinion. There are most likely a ton of resources that can be found on Google also. PHP Wiki is also a good resource for just about anything PHP.
Introduction
I just want to say that PDO is awesome and here the hard part, I totally hate it. Not the hate hate, like that one friend you wish would just die in some horriblely long fashion, but the hate like you hate homework or doing chores. I’ll say PDO is more like a chore and I hate it.
Let me explain, MySQL and MySQLi extension are easy and predictable. It is also easy to find where you went wrong. However, I came across some issues with PDO that I could not track down.
My Example
This is code that you shouldn’t do and the correction will follow.
try {
$statement = $GLOBALS['mysql']->prepare("SELECT COUNT(*) FROM gh_users
WHERE username=:username AND pass=PASSWORD(:password)");
$statement->bindParam(':username', $username);
$statement->bindParam(':password', $password);
$username = $_POST['username'];
$password = $_POST['password'];
$statement->execute();
$row = $statement->fetch(PDO::FETCH_NUM);
$exists = (bool) $row[0];
}
catch(PDOException $e) { var_dump($e); }
if($exists == true)
{
$_SESSION['username'] = $_POST['username'];
$_SESSION['password'] = $_POST['password'];
}
No errors are returned, the connection was successfully made and from the documentation everything should be okay. Right? Wrong. $exist would never evaluate to be true. It’s not a scope problem either as scope isn’t an issue in PHP.
The funny thing is that using the MySQLi extension I could easily tear a working implementation out of my ass that would work without fail. I suppose that is why Professionals make the big bucks. They know how to track down the proper documentation on how to use such ‘complex’ extensions.
Solution
In my reading of the php.net examples there are sample code of the usage such as the one I’m using. However, $exists will never evaluate to be true, even if the sign in information is correct. There are two correct ways to fix the problem.
- Place the variables before the bindParam(). (Thanks Simon).
- Use the array value instead, in this case $_POST['username'] and $_POST['password'].
$statement->bindParam(":username", $_POST[username]);
$statement->bindParam(":password", $_POST[password]);
$statement->execute();
To be honest, I thought about placing the $_POST keys in the bindParam, but decided against it because I thought it wouldn’t have worked.
Thank You PDO Creator
While reading the slides for the PDO extension via the creator, maintainer, and PECL coder for the extension, it became clear that I was missing quite a few details that I either breezed over or missed.
I did read about a bug that stated that statements (like the one I’m using) aren’t cleaned up after the script ends. The connection wasn’t made and it wasn’t until I read something like this.
$pdo = new PDO("mysql:host=localhost;dbname=test", $user, $pass);
$statement = $pdo->prepare("SELECT COUNT(username) FROM gh_users WHERE username=:username AND pass=PASSWORD(:password)");
// do stuff here
$statement = null;
Then I was all like, “Damn I suck!” Sigh, such matters are best left to the masters I suppose. I bow to you oh PDO god and please forgive my arrogance.
Perhaps Someone Could Explain
What is something so beautifully complex and awesome, so damn complicated. I believe I need to do some more research and testing to find out where I went wrong.
Looking Back
I’ll keep track of PDO progress and continue to using Mysql and MySQLi on critical applications. Once I’m finished with my current project, I’ll be using PDO more and continue my research. I need to reread the PDO slides for some more hints on where I went wrong, but for now, I think I’ll not be using the awesome and powerful PDO.
Possibly Related Posts:
- Bullet: E-Book Library Management and Content Server
- Using ZendFramework 2 beta1 For Directory Project
- The Way of Kings and Cosmere Theory
- “In Time” Movie Premise Flawed
- Completing HTTP Library For PHP
Already stated the solution.
Your solution also seems to work. In the manual, there are multiple examples where the variables are set after the bindParam(), so I just assumed that was how it was done. Either my solution or your solution would work correctly to give the same result. Why use two variables for one thing when one is just as good?
Em,
Arn’t these two code blocks the wrong way round?
$statement->bindParam(‘:username’, $username);
$statement->bindParam(‘:password’, $password);
$username = $_POST['username'];
$password = $_POST['password'];
Quite agree, it’s one of the dumbest bits of PDO, leaving variables dangling in space – there is no reason for this ‘feature’, if you need to change the value you want to use, just calling bindParam() again is simple..
bindValue(‘:username’, $_POST['username']);
Well, actually not because let me explain. The reason to use variables is if you do multiple transactions. It would be better to bind one variable and use that for the updating than to keep calling bindParam() or bindValue.
I haven’t done any further testing of the matter, so there might be a catch to it, but I believe it is a far simple and efficient way. For my example, using two variables wouldn’t make sense, but other examples it would.
I’ll probably be writting a follow up to my jackassery later. This PDO stuff is awesome and I’m looking forward to correcting my point of views on it.
Well, you could just pass execute an array and bypass the bindParam(), which could be even better. So many tests to do, so little time. Well, my curiosity is peaked, so I’ll probably devote some time to it sooner, rather than later.
Testing will be primitive, using simple microtime and 1000 rows or so. If you know of any available tests that I won’t be able to find with a simple Google search, then let me know. Should probably have something within a week.
Tests will include:
1. BindValue() vs BindParam vs Array passed to execute Speed
2. BindValue() vs BindParam ease of use test
3. MySQLi vs PDO prepared statement speed comparsion.
4. Using variables for calling vs calling BindParam every update.
It appears on the face of things to be far more efficent,
It’s a trade off, between having some explicit code that you can see says’ I’m updating this, and some magic attachment, that is not clear from the code, that could lead to bugs.
I’d choose the extra typing every day over crazy magic like this
I doubt the performance difference will be significant, compared with the actual database call, which are normally slow….
I think you’re hating it because you’re writing too much code.
KISS:
try {
$statement = $mysql->prepare("SELECT COUNT(*) FROM gh_users
WHERE username=:username AND pass=PASSWORD(:password)");
$statement->execute(array(
'username' => $_POST['username'],
'password' => $_POST['password']
));
$row = $statement->fetch(PDO::FETCH_NUM);
if ($row[0]) {
$_SESSION['username'] = $_POST['username'];
$_SESSION['password'] = $_POST['password'];
}
} catch(PDOException $e) {
var_dump($e);
}
A common newbie mistake with PDO is wondering why it doesn’t report errors. You need to tell PDO that you want either warnings or exceptions, otherwise it will assume that you’re being a good coder and checking return values and calling errorCode() and errorInfo() in the right places.
PDO is not bad.
The situation may dictate if you use mysqli or PDO.
mysqli works only on MySQL.
PDO can work with many databases.
So, if work with a large project, PDO could be the winner.
I have been using PDO, and i’m very happy. Zend Framework has implemented it.
Here’s a good reason to like PDO and hate the MYSQLi.
Let’s say that you’re making a mapper class. And in the mapper class, you’re trying to create an “insert($objects)” method that can go get the relevant fields from the object, and insert them.
The way I tried to do it was to count up the fields, use that to make a string of “?,?,?” placeholders, and then pass the array of values into execute(). That works in PDO, the way it does in Perl DBI.
But not with mysqli. As far as I can tell, with mysqli, you prepare the statement, and then you have to bind them explicitly like so:
$stmt->bind_params(“sss”, $foo, $bar, $baz);
Whatever, seems like BS to me. But more importantly, if I’m passing in an array with an arbitrary number of insert values:
1. now I have to do some kind of meta-info about each object attribute? so i can create that string? Bullocks.
2. as far as i know, correct me if I’m wrong, I can’t just do $stmt->bind_params(“sss”, @attrs), they have to be broken out into individual variables. Again, bad bad bad. If this was Perl, it wouldn’t care if I passed in a list of vars or an array. But in PHP, it’s much easier to put things into an param array than to bust individual params out of one. PITA.
So, I’m going back to PDO. If I don’t go back to Perl first.
For chigging, you can always use call_user_func_array() if you don’t want to type each individual parameters.
That is a very good idea, Mario. I was thinking of doing that, but was unsure if it would work.
I just started using MySQLI. I have no need for additional databases, but does PDO have any other benefits over MySQLI?
Speed?
Ease of Use? (doesn’t sound like it!)
More Robust?
@John
It actually is pretty easy once you get used to the API. Speed can be fine, but more robust? Maybe not. Depends on your needs.
Hi Jacob:
I think I saw you somewhere. Anway. Is ADODB out of picture in the case?
I used to use PHP ADODB library and switch database between MySQL and MSSQL. I think PDO may fit my need to convert MySQL into Oracle later on PHP5.2.6+.
I have heard people saying that prepare() function may not use the true MysQL Cache feature on MYSQL server. People recommend to use query() for statement that need to be cached.
Ex.
select SQL_CACHE firstname, lastname from table …
I have never use prepare() with stored procedure to pass in and pass out the record sets. I heard that PDO did not do good job on passing multiple records sets from MSSQL.
Can you really recommend to use PDO for the following reasons, or should I go back to ADODB? I think MYSQLi won’t get me anywhere.
1. Support different databases
2. Performance with cache (a lot of huge tables need to be cached)
3. Support stored procedures with parameters in and out.
Thank you so much! I am glad that I found your blog.
@Terence Chang,
Not really, ADODB can use PDO in order to access the databases. PDO is more abstraction of various database APIs, so that you don’t have to change the functions everywhere. It doesn’t abstract the SQL and rewrite it for you like some other Database and SQL abstraction libraries out there.
The query() function uses prepare() in its implementation. You want to turn on Emulate_Prepares for mysql when using it. That might change when the PDO_MYSQLND driver comes out.
I use PDO, because it has less overhead and it does what I need it too. ADODB is implemented in PHP (mostly, there is a PHP extension to speed it up) and PDO is implemented in C. For most of my projects I haven’t needed the power of ADODB and I’m comfortable with PDO after using it for a number of years.
Really, most of my queries are prepared statements, even if I’m making one query at a time, I usually save the prepared object as a static property of the class I’m working in to be used later. I haven’t done any benchmarks to know if it actually is any faster.