Returning Multiple Rows with MySqli and Arrays
41,456
Solution 1
You have to use a loop to get them all at once:
<?php
function resultToArray($result) {
$rows = array();
while($row = $result->fetch_assoc()) {
$rows[] = $row;
}
return $rows;
}
// Usage
$query = 'SELECT DISTINCT $fields FROM `posts` WHERE `profile_user_id` = $user_id LIMIT 4';
$result = $mysqli->query($query);
$rows = resultToArray($result);
var_dump($rows); // Array of rows
$result->free();
Solution 2
Why not use directly like this:
$result = mysqli_fetch_all($mysqli->query($query), MYSQLI_ASSOC);
Author by
thank_you
Updated on August 07, 2022Comments
-
thank_you over 1 year
For the past two days or so I've been converting my functions to mysqli. I've run into a problem. I have a function that returns an array containing a row from the database. However, I want the array to contain multiple rows versus one. Also, how would I be able to echo out the individual posts. Here is my failed attempt that only displays one row in the array.
$mysqli = new mysqli("localhost", "user", "password", "database"); function display_posts ($mysqli, $user_id) { $fields = "`primary_id`, `poster_id`, `profile_user_id`, `post`"; $user_id = (int)$user_id; $query = "SELECT DISTINCT $fields FROM `posts` WHERE `profile_user_id` = $user_id LIMIT 4"; if ($result = $mysqli->query($query)) { $row = $result->fetch_assoc(); return $row; $result->free(); $stmt->close(); }}
Here I am trying to display the data.
$user_id = 1; $posts = display_posts($mysqli, $user_id); //Not sure what to do with $posts. A While loop perhaps to display each post?
-
thank_you over 11 yearsAlthough I was hoping to get an answer how to echo out each individual value I went ahead and figured it out myself. Thanks for the help nevertheless. I decided to take out the function
resultToArray
and append it to my original function. -
Your Common Sense over 10 years@KateGregory there is nothing to explain actually. Beside the fact that this function is only available since 5.3 and only under mysqlnd.
-
SNEILΛ over 10 yearsNot much to explain. Rather than do a while cycle, use the available mysqli methods to get the entire set of results. Also avoiding to declare an extra function for it. And yes, it's >5.3 up. But as far as i know mysqli is also 5.3 and up
-
logicK almost 6 yearsYou don't want to make your $mysqli global. That's a huge security risk.
-
SNEILΛ almost 5 yearsWorst way to do it!!, why pass the work to PHP when MYSQL can do this by default. See my answer below.