How to Get PHP MySQLi Query Result to Array

Working with MySQL databases using PHP is essential for most dynamic web applications.

We’ve looked previously at how to use PHP to query a MySQL database for results but often, after querying a database, you need to process the results and store them in an array for further use. In this post, we’ll discuss how to retrieve the result of a MySQLi query and convert it into a PHP array.

This process can be useful when you want to manipulate or format the data before displaying it on a webpage or passing it to another function in your application.

Table of Contents

1. Introduction to MySQLi and PHP
2. How to Execute a MySQLi Query
3. Fetching Query Results into an Array
4. Using fetch_assoc() to Create an Associative Array
5. Using fetch_array() for Numeric and Associative Arrays
6. Best Practices for Fetching MySQLi Results
7. Conclusion

1. Introduction to MySQLi and PHP

MySQLi (MySQL Improved) is a PHP extension designed to interact with MySQL databases. It’s an upgrade from the original MySQL extension, providing enhanced features like prepared statements, object-oriented support, and more.

When querying a database with MySQLi, the result can be stored in an array for easy access and manipulation. This technique is particularly useful when working with large datasets or when the data needs to be displayed in a dynamic format, such as in a table or JSON response.

2. How to Execute a MySQLi Query

Before fetching the results into an array, we first need to execute a query against the database. Here’s an example of how to establish a database connection using MySQLi and execute a simple query.

<?php
// Database credentials
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "my_database";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

// Execute a MySQL query
$sql = "SELECT id, name, email FROM users";
$result = $conn->query($sql);
?>

In this code, we’ve established a connection to the MySQL database and executed a SELECT query to retrieve user data (id, name, email) from a users table.

3. Fetching Query Results into an Array

Once the query is executed, we need to convert the result into an array. MySQLi provides several methods for fetching data, including fetch_assoc(), fetch_array(), and fetch_row().

Let’s first understand how to retrieve the data and then focus on storing it into an array.

4. Using fetch_assoc() to Create an Associative Array

The fetch_assoc() method retrieves the query result as an associative array, where the column names become the keys of the array. This is particularly useful if you want to reference the data by the column names.

Here’s how you can use fetch_assoc() to store the result into an associative array:

<?php
// Array to hold the results
$data = array();

if ($result->num_rows > 0) {
    // Fetch each row and store it in the array
    while ($row = $result->fetch_assoc()) {
        $data[] = $row;  // Append each row to the $data array
    }
} else {
    echo "No records found.";
}

// Close the database connection
$conn->close();

// Output the array (for debugging purposes)
print_r($data);
?>

In this code:

• We first check if the query returned any rows ($result->num_rows > 0).
• Inside the while loop, each row is fetched as an associative array using fetch_assoc(), and we append it to the $data array.

Example Output:

If the users table has records, the output of print_r($data) would look like this:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => John Doe
            [email] => john@example.com
        )
    [1] => Array
        (
            [id] => 2
            [name] => Jane Smith
            [email] => jane@example.com
        )
)

Each row from the database is stored as an associative array, and all rows are combined into a single multidimensional array.

5. Using fetch_array() for Numeric and Associative Arrays

If you need both numeric and associative indices in your array, you can use the fetch_array() method. This method returns each row of data with both numeric and string-based indices, which can be useful in certain scenarios.

Here’s an example of using fetch_array():

<?php
$data = array();

if ($result->num_rows > 0) {
    while ($row = $result->fetch_array(MYSQLI_BOTH)) {
        $data[] = $row;
    }
}

$conn->close();
print_r($data);
?>

The MYSQLI_BOTH argument ensures that both associative and numeric indices are included.

Example Output:

Array
(
    [0] => Array
        (
            [0] => 1
            [id] => 1
            [1] => John Doe
            [name] => John Doe
            [2] => john@example.com
            [email] => john@example.com
        )
)

As you can see, the first array now contains both the numeric and associative keys for each column.

6. Best Practices for Fetching MySQLi Results

Here are a few best practices to keep in mind when working with MySQLi query results and arrays:

a. Use Prepared Statements

Prepared statements help prevent SQL injection attacks by separating SQL code from data. This is especially important when dealing with user input. Here’s an example of using prepared statements:

<?php
$stmt = $conn->prepare("SELECT id, name, email FROM users WHERE name = ?");
$stmt->bind_param("s", $name);
$name = "John Doe";
$stmt->execute();
$result = $stmt->get_result();

$data = array();
while ($row = $result->fetch_assoc()) {
    $data[] = $row;
}

print_r($data);
?>

b. Handle Empty Results Gracefully

Always check if the result contains any rows before attempting to process the data. This avoids errors and ensures the user gets feedback when no data is returned.

c. Free Result Memory

When working with large datasets, it’s important to free up memory by calling the free() method after processing the result:

$result->free();

d. Close the Database Connection

Always close the database connection when you’re done:

$conn->close();

This is a good practice for freeing up server resources and ensuring your application runs smoothly.

7. Conclusion

Converting MySQLi query results into an array is a fundamental task in PHP web development. It allows you to easily access and manipulate the data retrieved from your database. Whether you’re fetching data as an associative array or using both numeric and associative indices, MySQLi provides a variety of methods to suit your needs.

By following best practices, such as using prepared statements and properly handling empty results, you can ensure your PHP application is both secure and efficient.

For further reading on MySQLi functions, check out the PHP MySQLi documentation.