get array of rows with mysqli result

Question

I need to get all the rows from result object. I’m trying to build a new array that will hold all rows.

Here is my code:

$sql = new mysqli($config['host'],$config['user'],$config['pass'],$config['db_name']);
if (mysqli_connect_errno())
{
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
$query = "SELECT domain FROM services";
$result = $sql->query($query);           
while($row = $result->fetch_row());
{
    $rows[]=$row;
}
$result->close();
$sql->close();
return $rows;

$rows is supposed to be the new array that contains all, rows but instead I get an empty array.

Any ideas why this is happening?

Answer 1

You had a slight syntax problem, namely an errant semi-colon.

while($row = $result->fetch_row());

Notice the semi-colon at the end? It means the block following wasn't executed in a loop. Get rid of that and it should work.

Also, you may want to ask mysqli to report all problems it encountered:

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$sql = new mysqli($config['host'], $config['user'], $config['pass'], $config['db_name']);

$query = "SELECT domain FROM services";
$result = $sql->query($query);
$rows = [];
while($row = $result->fetch_row()) {
    $rows[] = $row;
}
return $rows;

Answer 2

Newest versions of mysqli have some improvements that can simplify such a task.

First, of all, there is a useful function to return an array with all rows returned by a query, mysqli_fetch_all()
It means in case you need a simple enumerated array, the code would be much simpler:

$query = "SELECT domain FROM services";
$result = $sql->query($query);     
return $result->fetch_all(MYSQLI_ASSOC);

or even all in one line,

return $sql->query("SELECT domain FROM services")->fetch_all(MYSQLI_ASSOC);

However, if you need to use some column to index the resulting array, you still need a while loop like this:

$query = "SELECT id, domain FROM services";
$result = $sql->query($query);
$data = [];
while ($row = $result->fetch_assoc()) {
  $data[$row['id']] = $row;
}

Note that you should always initialize an array before filling it up, because such a variable could already exist.

Also, mysqli_result class is now Traversable. It means you can use it in the foreach loop right away, as though it's an array contains all rows from the database:

$query = "SELECT domain FROM services";
$result = $sql->query($query);
foreach ($result as $row) {
    echo $row['domain'];
}

But it is actually just a syntax sugar for the while loop - you cannot access values of this "array" directly, which makes this feature of a little use actually.

Obligatory notes.

This question is a decade old, and the way a connection is made and the query is performed, both in the question and the accepted answer, are obsoleted and frowned upon nowadays.

When a connection is made, there are several things to keep in mind. I wrote an article on how to connect with mysqli properly that provides a correct connection example emphasizing on the following issues:

• a proper error reporting mode must be set
• a proper character set must be set
• no manual error reporting code should be ever used (like die(mysqli_connect_error()))
• a connection has to be made only once, in a separate file, and then just included into every script that needs a database interaction. in case a database code is used in a function, a connection variable must be passed in as a function parameter.

When it goes to running a query, there are several things to keep in mind as well:

• when even a single variable is used in the query, a prepared statement must be used instead of mysqli_query()
• as a result, a special function called mysqli_stmt_get_result() should be used in order to use familiar fetch functions to get the resulting rows. In case this function is not available you are probably to tick some checkbox in your cpanel (look for one labeled mysqlnd).
• given a prepared statement with mysqli, although being obligatory, takes a lot code to write, it is advised to use a helper function for mysqli that would perform most of work automatically and make a mysqli prepared statement a smooth as a regular query.
• no manual error reporting code should be ever used (like die(mysqli_error())). Thanks to the proper error mode, mysqli will report all errors automatically.