MySQLi prepared results array

Question

I have been trying to find a solution to MySQLi stmt not having a fetch array function and I came upon this interesting bit of code. Do you think this code is worth using, no huge security flaws?

/*
 * Utility function to automatically bind columns from selects in prepared statements to
 * an array
 */
function bind_result_array($stmt)
{
    $meta = $stmt->result_metadata();
    $result = array();
    while ($field = $meta->fetch_field())
    {
        $result[$field->name] = NULL;
        $params[] = &$result[$field->name];
    }

    call_user_func_array(array($stmt, 'bind_result'), $params);
    return $result;
}

/**
 * Returns a copy of an array of references
 */
function getCopy($row)
{
    return array_map(create_function('$a', 'return $a;'), $row);
}

credit: http://gunjanpatidar.wordpress.com/2010/10/03/bind_result-to-array-with-mysqli-prepared-statements/

Requested by common sense:

 $db = new PDO("mysql:host='localhost';dbname='testing'", 'username', 'password') or die('Could not connect to server');
$get_posts = mysqli_stmt_init($db);
mysqli_stmt_prepare($get_posts, 'select * from Chatposts where Chatid = ? and CPid > ? and Deleted = ? order by CPid desc limit ?');
mysqli_stmt_bind_param($get_posts, 'iiii', $chatroomid, $lastpost, $deleted, $limit);
mysqli_stmt_execute($get_posts);
mysqli_stmt_bind_result($get_posts, $newcolumn['ID'], $newcolumn['Chatid'], $newcolumn['Name'], $newcolumn['URL'], $newcolumn['Text'], $newcolumn['Datetime'], $newcolumn['IPaddress'], $newcolumn['Deleted']);
mysqli_stmt_store_result($get_posts);
mysqli_stmt_fetch($get_posts); // Trying to fetch array
mysqli_stmt_close($get_posts);

Your Common Sense Your Common Sense · Accepted Answer · 2013-03-14T05:36:05

Although I see no "huge security flaws" in this code, I don't think it's worth using anyway. Look, mysqli makes you troubles out of nowhere. You had no such problem with old mysql, you have no such problems with PDO. Only mysqli makes your life complicated with no reason.

Sometimes you can solve this problem by using get_result(), but it's not guaranteed to work and isn't even bound to PHP version - so, you cannot even tell it beforehand.

Not to mention you will have the same problem trying to bind arbitrary number of placeholders to the query, and there is not even a semi-working solution!

So, again - instead of solving all these problems - why not to use a sensible driver, like PDO?
With PDO you can use familiar fetch() right out of a box, without such crutches like the code above.

Another solution would be to refrain from using native prepared statements, and utilize their manually parsed equivalent, using mysqli_query() which resembles old good mysql_query in any way.
But this approach seems too complicated for an average user - so, it's again better to use PDO.

Here is your code using PDO

$dsn = "mysql:host=localhost;dbname=test;charset=utf8";
$opt = array(
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => FALSE,
);
$pdo = new PDO($dsn,'root','', $opt);

$stm = $pdo->prepare('select * from Chatposts where Chatid = ? and CPid > ? and Deleted = ? order by CPid desc limit ?');
$stm->execute(array($chatroomid, $lastpost, $deleted, $limit));
$posts = $stm->fetchAll();
// now you have all requested posts in $posts array

MySQLi prepared results array

1 Answers