Help with Prepared Statements

[consultant1027]

I'm converting a site to use prepared statements.  I want to use MySQLi instead of PDO (read this if you want to know why: http://dealnews.com/developers/php-mysql.html )

 

Can someone provide an example of a simply query that first binds a couple parameters than loops through the results with WHILE statement.  I've figured everything out except I don't want to have to explicity bind all the results values to variables!  I just want to loop throug the results set as rows of associative arrays.  Having to bind all the results values would be a major PAIN! 

 

How do you just loop throught the results as associative arrays without having to bind the results when using prepared statements?

 

[MadTechie]

example of MySQL usage (from the manual)

<?php

$conn = mysql_connect("localhost", "mysql_user", "mysql_password");

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
}
  
if (!mysql_select_db("mydbname")) {
    echo "Unable to select mydbname: " . mysql_error();
    exit;
}

$sql = "SELECT id as userid, fullname, userstatus 
        FROM   sometable
        WHERE  userstatus = 1";

$result = mysql_query($sql);

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//       then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"];
    echo $row["fullname"];
    echo $row["userstatus"];
}

mysql_free_result($result);

?>

[consultant1027]

That example isn't using prepared statements.  Looks like I stumbled across a major short coming of prepared statements - looks like there is no method of returning results in an associative array without wirting your own function/class to do it.  Read the comments from this manual page.  You've got to be kidding me with prepared statements there's no $stmt->fetch_assoc() or $stmt->bind_assoc($some_array) function?!?!

 

http://us2.php.net/manual/en/function.mysqli-stmt-fetch.php

 

 

[consultant1027]

Looks like I may have to go with PDO anyway since it does handle it by mysqli prepared statements looks like they don't?

 

PDO::FETCH_ASSOC: returns an array indexed by column name as returned in your result set

 

[consultant1027]

You know, I'm thinking now, if the main reason I'm doing this is for security (protection against SQL injection), if I have to have a line of code that binds all the variables to the parameters in the SQL prepared statement anyway,  why not save the hassle and just write a simple function that I can pass one or more variables to which returns their values sanitized by mysql_real_escape_string (and also escapes % which mysql_real_escape_string doesn't).  Then I could leave all the rest of my code as-is and not convert to prepared statements.  I'm I going to get some other MAJOR benefits going with prepared statements, other than performance in the context of executing SQL statements in loops with changing parameters?