Upgrading database queries to MYSQLi or PDO

Are there direct command replacements for the following, or is there more to it than that?

$st1 = "SELECT * from `members` WHERE (memberid ='$usernum')";
$r1 = mysql_query($st1, $sqlsession);

if (mysql_num_rows($r1) )
{
while ($r2= mysql_fetch_array($r1)) {
[whatever]
}
}

Code (markup):

I know the above well, but new to MYSQLi or PDO

Thanks!

 

While you CAN directly replace, you are missing the point in cases like that. One of the ENTIRE reasons we've been told to STOP using mysql_ functions is so that we will STOP using the insecure practice of putting variables into the query string. Done PROPERLY when you have a value to be passed you should do it separately, in what's called the prepare/execute model.

I prefer PDO for this, but your example it would go something more like this:

// assumes $db is a connected PDO object
$stmt = $db->prepare('
	SELECT *
	FROM members
	WHERE memberid = ?
');
$stmt->execute([ $usernum ]);
if ($row = $stmt->fetch()) {
	do {
		// whatever you're doing with the row
	} while ($row = $stmt->fetch());
} else {
	// report no results
}

Code (markup):

A nice side effect of prepare/execute is you no longer need to screw around with things like mysql_real_escape_string or any other such asshattery on sanitization. Consider the job done for you!

Also, notice the cute trick of trying to fetch the first row inside the IF, then doing a do/while. Cleaner/faster implementation than screwing around with pulling the row count.

There are actually several different ways of building that in PDO in terms of the query and execute, you could also word that as:

$stmt = $db->prepare('
	SELECT *
	FROM members
	WHERE memberid = ?
');
$stmt->bindParam(1, $usernum);
$stmt->execute();

Code (markup):

Or

$stmt = $db->prepare('
	SELECT *
	FROM members
	WHERE memberid = :memberId
');
$stmt->execute([ ':memberId' => $usernum ]);

Code (markup):

The latter can be handy as you can see from the [] you can pass an array to it, so a quick translation/validate function for any user input could put valid results into the array for easy passing.

One BIG advantage of prepare/execute is that you can prepare once and execute many times... a LOT of people miss this detail.

Let's say you had an array of member id's called $memberList and you wanted to update a "lastaccess" field to NOW()

$memberList = [ 1, 3, 5, 7, 11, 13, 17 ];
$stmt = $db->prepare('
	UPDATE members
	SET lastaccess = NOW()
	WHERE memberid = ?
');
$stmt->bindParam(1, $memberId);
foreach ($memberList as $memberId) $stmt->execute();

Code (markup):

One of the most powerful bits of bindParam is that it literally binds that variable to the query, so if you change the value of the variable like we do on the foreach, it's applied every time we call execute without having to pass it to the execute method. REALLY slick. Often really handy for plugging mass random values into a DB for testing purposes too.

These days, if you are doing string additons or putting variables in double-quotes into your query string, you're doing something wrong... and there's little legitimate reason to be wasting an extra variable on the query string unless you're loading it externally for 'named queries' or supporting more than one type of database engine...

Which is another reason to favor PDO... one codebase to target a half dozen or more different DB engines, not just mySQL. You just change the query string to the appropriate one for that engine, the rest of the code remains untouched.

 

Yes, deathshadow is 100% correct. Aside from the other benefits, PDO/prepared statements is your strongest insurance against SQL injection and hacking. It is almost like having a framework for queries with clean, maintainable code. The binding stuff is also awesome, I come from a .NET environment where it is easy to bind variables and their values update automatically.

ALWAYS use prepared statements. MySQL extension is completely gone from version 7. MySQLi in my opinion should be next. When I build PHP I only configure with PDO db libs.

I urge any new project to use PDO.

 

That's been my take on it for about eight years... pretty much from the day it was introduced in 5.1 I saw it as so superior to what had come before. It's sad that it's now a decade old and adoption is still slow.

 

The only issue I've seen with PDO is multiple queries. I tried to insert 100 items using two types of syntax (full statements separated by semicolons and a single VALUE clause (),(),() ...) and I got weird results in Postgresql.

From my SQL Admin utility the statements worked fine, but when they went thru PDO::exec I got very strange results (lots of duplicate records). I dumped the queries in the browser too to be sure, copied and pasted them to the utility. I ended up breaking them into individual queries. Weird.

 

When you say multiple queries, might there be that it's just a syntax issue? PDO do have some minor issues with different DB-systems, MariaDB springs to mind...

 

Something like:

$query = 'INSERT INTO customer (id, name) VALUES ("1", "Mary"), ("2", "Jane");'

Code (markup):

or

$query = '
INSERT INTO customer (id, name) VALUES ("1", "Mary");
INSERT INTO customer (id, name) VALUES ("2", "Jane");'

Code (markup):

Wouldn't quite work right.

$dbh->exec($query);

Code (markup):

 

Yeah... That's normal, because that's the wrong syntax. For the first one, you create one query, and iterate through the values, not the query, and for the second instance, you wrap the queries inside a transaction.

 

Great examples... what about connecting to the database?

 

I'm an ex sql server guy and 99% of everything was stored procs, so thanks for this info. This was a import script and not a website so I thought I could just get away with strings.

Ok, you obviously don't want to do this in production (better passwords, config file) but connecting is very simple. Then you execute against $DBH. Replace pgsql with the database of your choice(config may be slightly different, check the docs).

        $dsn = "pgsql:dbname=test;host=localhost";
        $login = "postgres";
        $DBH = new PDO($dsn, $login, $login);

Code (markup):

 

If you mean multiple queries separated by semi-colons, PDO does NOT allow you to do that. It's actually a security feature to help so that it's effectively impossible to pass any value that could force a second query to run.

Some people disagree with it, but really it makes more sense when you have PDOStatement directly related to a single query. It's another of those things we were allowed to do, that we REALLY shouldn't have been allowed to do.

PHP has a number of those, and some that still need fixing.

 

Basically, that last example @deathshadow showed a few posts back:

is what you would use for that first version of your code. You basically just set up the query, and then you re-assign the variables for each iteration.

 

Can also be more efficient if you turn "emulated prepares" off, as it means instead of sending a query string where each one needs to be parsed, you send the query once and then the data separately. It may be more handshaking on the connection, but it can make the SQL engine not have to work as hard as it already has the query.

To turn emulated prepares in PDO off:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

It's on by default as some crappy SQL engines don't support it, but the emulation defeats one of the entire reasons to use prepare/exec (sending the query separate from the data) and breaks a number of smaller features like limit.

For example:
LIMIT ?, ?

Will throw an error when emulated prepares are enabled, but works just fine with it disabled and actually letting the engine do it's job.

Emulated prepares is one of the dumber things in PHP, I still say if a engine cannot support them, then the engine shouldn't be supported by PHP.