如何使用mysqli_fetch_all（）从查询中返回空数组或结果集？

Question

My PHP code:

function get_something() {

  global $link;

  $sql = "SELECT * FROM new";

  $result = mysqli_query($link, $sql);

  $names = mysqli_fetch_all($result, MYSQLI_ASSOC);

  return $names;

}

What is my problem:

When my table new is empty, I get the following error:

mysqli_fetch_all() expects parameter 1 to be mysqli_result`

If it isn't empty everything is working fine.

I need to check if my database is empty and if it's not, I will call mysqli_fetch_all. Otherwise, my function should return an empty array.

How is this possible to do?

Answer 1

Use mysqli_num_rows($result) to check how many rows were returned from the query. But if table new doesn't exist, $result will be false so we have to check that $result is valid as well:

if ($result && mysqli_num_rows($result) > 0)
    $names = mysqli_fetch_all($result, MYSQLI_ASSOC);
else
    $names = array();
return $names;

Answer 2

mysqli_fetch_all($result, MYSQLI_ASSOC) generates an indexed array of associative arrays. When there are no rows found, it will deliver an empty array (which is what you want). It is absolutely pointless to call mysqli_num_rows(), so don't make your script do any unnecessary work.

Furthermore:

• many developers will advise you not to make global declarations to pass variables in your custom function scope. Instead, pass the connection variable into your function as a parameter.
• I always try to avoid declaring single use variables. For your case, $sql and $names will only be used/referenced once after being declared, so just don't declare them.
• As a matter of personal preference, I recommend using OO syntax with mysqli functions because it is less verbose, but in my following snippet I'll leave it like your original post.

Suggested Code:

function get_something($link) {
    if (!$result = mysqli_query($link, "SELECT * FROM new")) {
        return [];  // check mysqli_error($link) because your query failed
    }
    return mysqli_fetch_all($result, MYSQLI_ASSOC);
}