Decision: PDO vs. MySQLI for "drop-in" DataAccess class replacement...

[dalecosp]

I'm trying to rewrite a "DataAccess" class for an app written by Someone Else(tm).

They didn't use PDO, nor MySQLI. I'm trying to decide which I want to use. Their class made use of a good number of prepared statements, and when an array is fed in they don't bother to enumerate or even label the params array (in other words, the array could be flat, or associative, and the number of members varies from call to call).

So far I've run into have a little trouble with MySQLI and a varying number of params. However, the outside app uses Execute() as a native DB call and think I'm having issues because that name conflicts with the built-in PDO execute() function (xDebug: Maximum function nesting level of '200' reached, aborting!).

Any thoughts? Which do you prefer (PDO, or MySQLI). Do you use $mysqli->prepare() and, if so, can you feed it any number of bind params at will? Alternatively, if I use PDO what shall I do with all those DataAccess::Execute() calls that appear as if they'll be broken?

[NogDog]

I prefer PDO for a few reasons.

1. It works with many different RDBMS's.
2. I find its syntax slightly "nicer", and in particular like using named parameters instead of question marks.
3. It's what I've used for the last 4+ years at work (with PostgreSQL), so I'm used to it. 🙂

[laserlight]

dalecosp wrote:

However, the outside app uses Execute() as a native DB call and think I'm having issues because that name conflicts with the built-in PDO execute() function (xDebug: Maximum function nesting level of '200' reached, aborting!).

The error message is symptomatic of excessive recursion, but I don't see how a native DB call would conflict with a PHP class' member function.

[Bonesnap]

I don't have much experience with PDO but I am sure it is capable of doing whatever you need. With that said I have used MySQLi for my database needs, though recently been slowly putting together a very basic DAL.

You can indeed use a variable number of prepared items in MySQLi. I am using the following in my DAL:

/**
 * Binds a list of parameters to be used as a prepared statement.
 *
 * This function expects a two-dimensional array of parameters. Each "child" array should contain a type and a value.
 *
 * @since 0.1.0
 *
 * @throws Invalid_Argument_Exception
 * @throws Data_Exception
 *
 * @param array $parameters The array of parameters.
 *
 * @return Mysqli_Database Returns the database object to allow method chaining.
 */
public function bind_parameters(array $parameters)
{
	$query_parameters = array('');

foreach($parameters as $key => &$parameter)
{
	if(!is_array($parameter))
	{
		throw new Invalid_Argument_Exception('Individual parameters must be arrays!');
	}

	$query_parameters[0] .= $parameter[0];
	$query_parameters[] = &$parameter[1];
}

if(!call_user_func_array(array($this->statement, 'bind_param'), $query_parameters))
{
	throw new Data_Exception('Unable to bind parameters!');
}

return $this;
}

[maxxd]

PDO all the way. In my experience, it's much easier to deal with on the whole, especially with regards to binding a variable number of parameters to a prepared query.

[sneakyimp]

laserlight;11053703 wrote:

The error message is symptomatic of excessive recursion, but I don't see how a native DB call would conflict with a PHP class' member function.

I (think I) was wondering the same thing. Sounds like he might be trying to just extend the native mysqli class or something? Seems pretty clear to me that the actual $db connection (whether PDO or mysqli) would be a member variable rather than some kind of base class and there would therefore be no problem with the $db object and the DataAccess class having some method names in common.
😕

[dalecosp]

sneakyimp;11053751 wrote:

I (think I) was wondering the same thing. Sounds like he might be trying to just extend the native mysqli class or something? Seems pretty clear to me that the actual $db connection (whether PDO or mysqli) would be a member variable rather than some kind of base class and there would therefore be no problem with the $db object and the DataAccess class having some method names in common.
😕

That was a couple iterations ago, but yes, IIRC I had:

class DataAccess extends MySQLi ...

[sneakyimp]

dalecosp;11053839 wrote:

That was a couple iterations ago, but yes, IIRC I had:

class DataAccess extends MySQLi ...

Can't you just remove the "extends MySQLi" and then change your class to construct a MySQLi object and assign it to some internal/private member variable? I'm guessing this would require you to construct member functions that match those of the mysqli object in order to have it keep working where you've actually been using it, but by defining these functions in your PHP, you separate them from those methods of the MySQLi object and have a chance to define them as you like and add any add'l logic you might need. More specifically you could define the execute method to match the original programmer's intentions and only when it has properly examined the params would it feed those to the mysqli object's (or pdo object's) execute method or whatever.

[dalecosp]

sneakyimp;11053841 wrote:

Can't you just remove the "extends MySQLi" and then change your class to construct a MySQLi object and assign it to some internal/private member variable? I'm guessing this would require you to construct member functions that match those of the mysqli object in order to have it keep working where you've actually been using it, but by defining these functions in your PHP, you separate them from those methods of the MySQLi object and have a chance to define them as you like and add any add'l logic you might need. More specifically you could define the execute method to match the original programmer's intentions and only when it has properly examined the params would it feed those to the mysqli object's (or pdo object's) execute method or whatever.

Yes! ... and I've already decided against having it extend MySQLI ... by default, because I've decided to try it with PDO. Heck, I've even been reading Some Other Docs, because the original implementation used that and it might be easier to give the app what it expected in the first place (there's apparently an awful lot of this DB class that was just wrappers? 😕 )

[sneakyimp]

dalecosp;11053849 wrote:

Yes! ... and I've already decided against having it extend MySQLI ... by default, because I've decided to try it with PDO. Heck, I've even been reading Some Other Docs, because the original implementation used that and it might be easier to give the app what it expected in the first place (there's apparently an awful lot of this DB class that was just wrappers? 😕 )

In my opinion, you should ALWAYS use some kind of db wrapper class. It comes in handy in precisely the situation where you need to switch from one DBMS to some other DBMS. DB wrapper classes also let you intercept all of your db actions if you need to log something or do performance profiling or perhaps even do an emergency filter to halt some SQL injection type stuff (e.g., throw an Exception if a query matches some regex). I know that last one may sound fairly abominable, but it can come in handy when you need to stop some mysql injection immediately but can't find the hole.