How to Put the Results of a MySQLi Prepared Statement into an Associative Array

How can I put the results of a MySQLi prepared statement into an associative array?

Try the following:

$meta = $statement->result_metadata(); 

while ($field = $meta->fetch_field()) { 
    $params[] = &$row[$field->name]; 
} 

call_user_func_array(array($statement, 'bind_result'), $params);            
while ($statement->fetch()) { 
    foreach($row as $key => $val) { 
        $c[$key] = $val; 
    } 
    $hits[] = $c; 
} 
$statement->close(); 

First you get the query metadata and from that obtain all the fields you've fetched (you could do this manually, but this code works for all queries rather than building by hand). The call_user_func_array() function calls the mysqli_stmt::bind_result() function for you on each of those parameters.

After that it is just a matter of running through each row and creating an associative array for each row and adding that to an array resulting in all the results.

Mysqli - Bind results to an Array

Finally this code is Working !!!!!

  <?php 
    $host = 'localhost'; 
    $user = 'root'; 
    $pass = '1234'; 
    $data = 'test'; 

    $mysqli = new mysqli($host, $user, $pass, $data); 
    /* check connection */ 
    if (mysqli_connect_errno()) { 
        printf("Connect failed: %s\n", mysqli_connect_error()); 
        exit(); 
    } 

    if ($stmt = $mysqli->prepare("SELECT * FROM sample WHERE t2 LIKE ?")) { 
        $tt2 = '%'; 

        $stmt->bind_param("s", $tt2); 
        $stmt->execute(); 

        $meta = $stmt->result_metadata(); 
        while ($field = $meta->fetch_field()) 
        { 
            $params[] = &$row[$field->name]; 
        } 

        call_user_func_array(array($stmt, 'bind_result'), $params); 

        while ($stmt->fetch()) { 
            foreach($row as $key => $val) 
            { 
                $c[$key] = $val; 
            } 
            $result[] = $c; 
        } 

        $stmt->close(); 
    } 
    $mysqli->close(); 
    print_r($result); 
    ?>

result of prepared select statement as array

Did you try something like this ?

$db = new mysqli("servername", "username", "pw", "dbname");

if($ps1 = $db->prepare("SELECT x1, x2 FROM my_table")) {
  $ps1->execute();
  $result = $ps1->fetchAll(PDO::FETCH_NAMED);
  $ps1->close();
}

UPDATE

I mean like this (in case you have installed the mysqlnd driver)

<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 150,5";

if ($stmt = mysqli_prepare($link, $query)) {

    /* execute statement */
    mysqli_stmt_execute($stmt);

    /* get result object */
    $result = mysqli_fetch_all(mysqli_stmt_get_result($stmt));

    /* close statement */
    mysqli_stmt_close($stmt);
}

/* close connection */
mysqli_close($link);
?>

---

Related Topics