How to fetch all in assoc array from a prepared statement?

by

In fact you can do this quite easily, you just can’t do it with the mysqli_stmt object, you have to extract the underlying mysqli_result, you can do this by simply calling mysqli_stmt::get_result(). Note: this requires the mysqlnd (MySQL Native Driver) extension which may not always be available.

However, the point below about recommending PDO over MySQLi still stands, and this is a prime example of why: the MySQLi userland API makes no sense. It has taken me several years of intermittently working with MySQLi for me to discover the mechanism outlined above. Now, I’ll admit that separating the statement and result-set concepts does make sense, but in that case why does a statement have a fetch() method? Food for thought (if you’re still sitting on the fence between MySQLi and PDO).

For completeness, here’s a code sample based (loosely) on the original code in the question:

// Create a statement
$query = "
    SELECT *
    FROM `mytable`
    WHERE `rows1` = ?
";
$stmt = $this->mysqli->prepare($query);

// Bind params and execute
$stmt->bind_param("i", $id);

// Extract result set and loop rows
$result = $stmt->get_result();
while ($data = $result->fetch_assoc())
{
    $statistic[] = $data;
}

// Proof that it's working
echo "<pre>";
var_dump($statistic);
echo "</pre>";

More Related Contents:

  1. Save the data into a database and return another value from database using PHP
  2. How to deal with mysqli problems? mysqli_fetch_array(): Argument #1 must be of type mysqli_result
  3. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  4. mysqli bind_param for array of strings
  5. Single result from database using mysqli
  6. Use an array in a mysqli prepared statement: `WHERE .. IN(..)` query [duplicate]
  7. Fatal error: Call to undefined function mysqli_connect()
  8. Warning: mysqli_query() expects parameter 1 to be mysqli, null given in
  9. Why is mysqli giving a “Commands out of sync” error?
  10. Warning: mysqli_select_db() expects exactly 2 parameters, 1 given
  11. mysqli fetch_all() not a valid function?
  12. Dynamic select mysqli query with dynamic parameters returns error doesn’t match number of bind variables [duplicate]
  13. Headers and client library minor version mismatch
  14. MySQLi Bind Param with an array for IN [duplicate]
  15. PHP PDO and MySQLi [duplicate]
  16. PHP + MySql + Stored Procedures, how do I get access an “out” value?
  17. mysqli query results to show all rows
  18. mysqli_real_connect(): (HY000/2002): No such file or directory
  19. php warning: mysqli_close() expects parameter 1 to be mysqli
  20. Getting error mysqli::real_connect(): (HY000/2002): No such file or directory when I try to access my project on live server [duplicate]
  21. Get number of rows matched by UPDATE query with PHP mysqli
  22. How to use mysqli_query() in PHP?
  23. mysqli_fetch_array returning only one result
  24. Using Prepared Statement, how I return the id of the inserted row?
  25. PHP prepared statements and transactions in a loop [duplicate]
  26. Warning: mysqli_query() expects at least 2 parameters, 1 given. What? [duplicate]
  27. Mysqli multiple row insert, simple multi insert query [duplicate]
  28. Should I use mysqli_real_escape_string or should I use prepared statements? [duplicate]
  29. check if the query returns zero rows [duplicate]
  30. Single Result From SUM With MySQLi

Leave a Comment