Search code examples
phpmysqli

Given execute_query() allows convenient one-liners, can I have the data and the count (num_rows) in a single call?

I'm using the new mysqli::execute_query in PHP 8.2 and want to get the total number of orders placed by a customer and an array of the orders.

Simplified code:

$MySQLi = new mysqli( "host", "username", "password", "database");

$sql = "select * from orders WHERE userid = ? ";

$ordercount = $MySQLi->execute_query($sql, [$userid])->num_rows;
$orderdata = $MySQLi->execute_query($sql, [$userid])->fetch_all(MYSQLI_ASSOC);

Can it be done with just a single execute_query ?

Solution

  • There is a simple solution to this: don't do it in one line. Just because mysqli_execute_query() lets you do things in one line doesn't mean it's a good idea.

    You can save the result in a variable, then access the num_rows property and fetch the rows, all on separate lines.

    $result = $MySQLi->execute_query($sql, [$userid]);
$ordercount = $result->num_rows;
$orderdata = $result->fetch_all(MYSQLI_ASSOC);

    However, the num_rows property is quite useless (the same goes for its function equivalent). It is exactly the same as getting the count of elements in the fetched array.

    $orderdata = $MySQLi->execute_query($sql, [$userid])->fetch_all(MYSQLI_ASSOC);
$ordercount = count($orderdata);

    So, if you want to do it in one line and get the count of rows, you can just use count().