PHP MySQLi Fetch Methods

Welcome to our in-depth tutorial on PHP MySQLi Fetch Methods! In this lesson, we'll explore how to retrieve data from MySQL databases using various fetch methods in PHP.

By the end of this tutorial, you'll be able to:

Understand the importance of fetch methods
Learn about different fetch methods: mysqli_fetch_array(), mysqli_fetch_assoc(), mysqli_fetch_object(), and mysqli_fetch_row()
Use fetch methods in practical examples
Write clean, efficient, and maintainable code

Let's get started!

Understanding Fetch Methods

Fetch methods are used to extract rows from the result set returned by a MySQL query. They help you access data from your database easily and efficiently.

Why are fetch methods important?

They allow you to navigate through the result set, accessing the data row by row.
They provide different ways to represent the data, making it easier to work with.
They help in optimizing memory usage as they only load the current row.

mysqli_fetch_array()

mysqli_fetch_array() is one of the most commonly used fetch methods. It returns an associative array, a numeric array, or both (by setting the MYSQLI_BOTH option).

📝 Note: In the following examples, we'll assume you have a table named users with columns id, name, email, and password.

Basic Example

php

<?php
$conn = mysqli_connect("localhost", "username", "password", "database");

$query = "SELECT * FROM users";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_array($result)) {
    echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Email: " . $row['email'] . ", Password: " . $row['password'] . "\n";
}

mysqli_close($conn);
?>

💡 Pro Tip: Use the MYSQLI_ASSOC option to get an associative array.

php

mysqli_fetch_array($result, MYSQLI_ASSOC);

mysqli_fetch_assoc()

mysqli_fetch_assoc() returns an associative array where the keys are the column names.

php

<?php
// ... (same as above)

while ($row = mysqli_fetch_assoc($result)) {
    echo "ID: " . $row['id'] . ", Name: " . $row['name'] . ", Email: " . $row['email'] . ", Password: " . $row['password'] . "\n";
}

mysqli_close($conn);
?>

mysqli_fetch_object()

mysqli_fetch_object() returns an object where the property names are the column names.

php

<?php
// ... (same as above)

while ($row = mysqli_fetch_object($result)) {
    echo "ID: " . $row->id . ", Name: " . $row->name . ", Email: " . $row->email . ", Password: " . $row->password . "\n";
}

mysqli_close($conn);
?>

mysqli_fetch_row()

mysqli_fetch_row() returns a numerically indexed array (0-based).

php

<?php
// ... (same as above)

while ($row = mysqli_fetch_row($result)) {
    echo "ID: " . $row[0] . ", Name: " . $row[1] . ", Email: " . $row[2] . ", Password: " . $row[3] . "\n";
}

mysqli_close($conn);
?>

Quiz

We hope this in-depth PHP MySQLi Fetch Methods tutorial has helped you understand the different ways to access data from your database. Happy coding! 🎯