PHP mysqli_fetch_all() Tutorial 🎯

Welcome to this comprehensive guide on using mysqli_fetch_all() in PHP! By the end of this tutorial, you'll have a solid understanding of how to fetch all rows from a database result set using this handy function. Let's get started! 📝

Introduction 📝

mysqli_fetch_all() is a powerful function in PHP that allows you to fetch all rows from a database result set at once. It's part of the mysqli extension, which is an improved version of the old mysql extension. By using mysqli_fetch_all(), you can simplify your code and improve the efficiency of your PHP scripts when dealing with large result sets.

Prerequisites 📝

Before we dive into the tutorial, make sure you have the following prerequisites in place:

Basic knowledge of PHP programming
Familiarity with SQL and creating/managing databases
A local development environment (like XAMPP or WAMP) or a hosting service (like A2 Hosting or Bluehost)

Getting Started 💡

First, let's establish a connection to our database using mysqli.

php

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

Replace username, password, servername, and dbname with your actual database credentials.

Fetching All Rows with mysqli_fetch_all() 💡

Now that we have a connection to our database, let's create a simple SQL query to select all rows from a table.

sql

$sql = "SELECT id, name, email FROM users";

Next, we'll prepare and execute the SQL query.

php

// Prepare statement
$stmt = $conn->prepare($sql);

// Execute statement
$stmt->execute();

Finally, we can fetch all rows from the result set using mysqli_fetch_all().

php

// Fetch all rows as an associative array
$result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

// Print all rows
foreach ($result as $row) {
    echo $row['id'] . ": " . $row['name'] . " (" . $row['email'] . ")<br>";
}

In the code above, MYSQLI_ASSOC is used to fetch the result set as an associative array. Each row in the array can be accessed using its column names as array keys.

Quiz 📝

Advance Usage 💡

mysqli_fetch_all() can also fetch results as a numeric array or as both associative and numeric arrays. To do this, use the appropriate flag with MYSQLI_ASSOC, MYSQLI_NUM, or a bitwise OR of both flags.

php

// Fetch all rows as both associative and numeric arrays
$result = $stmt->get_result()->fetch_all(MYSQLI_ASSOC | MYSQLI_NUM);

// Print all rows as associative arrays
foreach ($result as $row_assoc) {
    echo $row_assoc['id'] . ": " . $row_assoc['name'] . " (" . $row_assoc['email'] . ")<br>";
}

// Print all rows as numeric arrays
foreach ($result as $row_num) {
    echo $row_num[0] . ": " . $row_num[1] . " (" . $row_num[2] . ")<br>";
}

Conclusion 💡

By now, you should have a good grasp of how to use mysqli_fetch_all() in PHP to fetch all rows from a database result set. This function not only simplifies your code but also improves the efficiency of your scripts when dealing with large result sets. Happy coding! 🎯