Convert all data in mysqli_result to a 2D array

When developing applications using PHP and MySQL, we often use mysqli to perform database queries. After executing the SELECT statement, mysqli_query() returns a mysqli_result object, but this object is not a directly available array format. In order to process data more easily, we usually need to completely convert the query results into a two-dimensional array.

The following is a specific implementation method.

Core code

We can use mysqli_fetch_all() to get all rows at once, but be aware that this function requires at least PHP 5.3 and above. If you want to be compatible with earlier versions, you can use mysqli_fetch_assoc() in combination with loops.

Here is an example of using mysqli_fetch_all() :

 <?php
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check the connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "SELECT * FROM your_table";
$result = $mysqli->query($sql);

if ($result) {
    // MYSQLI_ASSOC Indicates that the associative array is returned
    $data = $result->fetch_all(MYSQLI_ASSOC);
    print_r($data);
} else {
    echo "Query failed: " . $mysqli->error;
}

$mysqli->close();
?>

A better cycling method with better compatibility

If you need to be compatible with earlier versions of PHP, it is recommended to use a while loop:

 <?php
$mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "SELECT * FROM your_table";
$result = $mysqli->query($sql);

$data = [];
if ($result) {
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;
    }
    print_r($data);
} else {
    echo "Query failed: " . $mysqli->error;
}

$mysqli->close();
?>

Encapsulated into functions

For better reuse, you can also encapsulate it into a function:

 <?php
function fetchAllResults($mysqli, $query) {
    $result = $mysqli->query($query);
    $data = [];
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
        }
    }
    return $data;
}

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

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "SELECT * FROM your_table";
$data = fetchAllResults($mysqli, $sql);
print_r($data);

$mysqli->close();
?>

Things to note

1. Security : Remember to use preprocessing statements ( prepare ) for externally input data to prevent SQL injection.

2. Performance : Taking out a large amount of data at one time may take up a lot of memory. If the data volume is large, it is recommended to paging query.

3. Error handling : Always check whether $result is false to avoid continuing to execute subsequent code when the query fails.

Sample URL

Suppose the interface you want to use is: