SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast aweb4u's Avatar
    Join Date
    Jun 2007
    Location
    Auckland, New Zealand
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    http://us1.php.net/manual/en/mysqli.prepare.php

    Scroll down to the Procedural Style example
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Enthusiast aweb4u's Avatar
    Join Date
    Jun 2007
    Location
    Auckland, New Zealand
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

    PHP Code:
    $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.

  4. #4
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,785
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by aweb4u View Post
    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.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  5. #5
    SitePoint Enthusiast aweb4u's Avatar
    Join Date
    Jun 2007
    Location
    Auckland, New Zealand
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Compare the old MySQL way of doing things:

    PHP Code:
    $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:

    PHP Code:
    $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!

    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.

  6. #6
    SitePoint Enthusiast aweb4u's Avatar
    Join Date
    Jun 2007
    Location
    Auckland, New Zealand
    Posts
    75
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •