MySQLi and prepared statements

I’ve been following the recent Avoid the Original MySQL Extension article and I’ve got a question about MySQLi and prepared statements.

I like to use procedural rather than object code.

How do I use prepared statements with a simple SELECT? I.e. “SELECT * FROM members” which returns all columns.

After executing the statement I’d like to end up with an associated array that holds the fetched row (which is how I used to do things with the original MySQL extension), but from what I’ve read you have to use mysqli_stmt_bind_result() to bind the result to variables. This isn’t so bad if only a few columns are returned, but this fails badly if the row has lots (>20) of columns as the mysqli_stmt_bind_result() gets unwieldy.

Can anyone suggest anything? Have I overlooked something?

I see there’s a mysqli_stmt_get_result() function but I can’t use that because my host is still on PHP 5.2.17 and mysqli_stmt_get_result() is only available from 5.3.0 onwards.

http://us1.php.net/manual/en/mysqli.prepare.php

Scroll down to the Procedural Style example

Hmm, maybe I didn’t make myself clear.

Normally you can use mysqli_fetch_assoc() to fetch a result and store it in an array. But it appears, correct me if I’m wrong, that you can’t use this for prepared statements. Instead you have to use mysqli_stmt_bind_result() to bind the result to individual variables, and you have to reference every variable. So if your result returns 30 columns then you have to specify 30 variables. I guess the variables could be elements in an array, so this would be valid:

$memberRow = array();
 mysqli_stmt_bind_result($stmt, $memberRow['Name'], $memberRow['DOB'], $memberRow['LastLogin'], $memberRow['Address1'], $memberRow['Address2'], $memberRow['Address3']);

etc. But this is crazy if you have large rows! Surely there must be an easier way?

<time passes>

Hmm, I’ve found that someone has written a function that does what I’m looking for: http://php.net/manual/en/mysqli-stmt.fetch.php#82742 Still, it seems like a step backwards for MySQLi.

Why? If the query references that many variables then the old way would require all of them to be specified inside the one statement in order for the query to work. Splitting it into two commands instead of one isn’t changing the number of variables that you need to include in the code, it is just moving them all to a separate statement.

Also when the variables are named like you have in your example the old way would have to concatenate all the string pieces of the query with the variable names whereas the new way allows the actual query itself to be specified as a single string without needing the concatenation.

Even without considering the 100000000000000% improvement in security the length of the code required will not be any longer when you use prepare/bind.

In the original query where you’d need ‘. .’ around each variable name that would be replaces in the prepare with a ? and in the bind with a , and a single character identifying the field type - so you actually save one character for each variable used.

Compare the old MySQL way of doing things:

$productsRS = mysql_query('SELECT * FROM products WHERE Code="' . $productcode . '"');
while($productsRow = mysql_fetch_assoc($productsRS)) {
  echo '<p>' . $productsRow['Code'] . '</p>';
  // etc
}

With the MySQLi way:

$stmt = mysqli_prepare($link, 'SELECT Code, Category, SubCategory, Manufacturer, ShortDescription, LongDescription, WholesalePrice, RetailPrice, Weight, Size, Colour, TaxRate, ShippingCode, OnSale FROM products WHERE Code=?');
mysqli_stmt_bind_param($stmt, 's', $productcode);
mysqli_stmt_execute($stmt);
$productsRow = array();
mysqli_stmt_bind_result($stmt, $productsRow['Code'], $productsRow['Category'], $productsRow['SubCategory'], $productsRow['Manufacturer'], $productsRow['ShortDescription'], $productsRow['LongDescription'], $productsRow['WholesalePrice'], $productsRow['RetailPrice'], $productsRow['Weight'], $productsRow['Size'], $productsRow['Colour'], $productsRow['TaxRate'], $productsRow['ShippingCode'], $productsRow['OnSale']);
while (mysqli_stmt_fetch($stmt)) {
  echo '<p>' . $productsRow['Code'] . '</p>';
  // etc
}

Looks like a heap more lot of coding to me! :eek:

The reason I want to stick to using associative arrays is because I’ve got a pile of code that already uses them (every tutorial used to recommend using them) and I just want to change the database functions without having to change lots of other code.

Don’t get me wrong, I really want to use prepared statements as I can see that they’re much more secure and could run faster (in some circumstances), but it just seems like a step backwards to me.

Someone has pointed out to me the mysqli_stmt_get_result() function, which can then be used in the mysqli_fetch_array() function to return an associative array. This would be ideal but unfortunately my host is still running PHP 5.2.17 and mysqli_stmt_get_result() is only available from PHP 5.3.0 onwards. So it looks like the PHP guys realised that they needed to provide this functionality and did so in a later version.

BTW, felgall, I think you misunderstood me. I’m talking about bound results (i.e. binding the results to variables), not bound parameters. Having to bind every column to a variable seems like a step backwards.