CODEWITHSUNDEEP
phpmysqli
Lukas
Lukas

Reputation: 169

What is a reason of using mysqli_fetch?

I wanted to ask why should I use (used in foreach -> ) mysqli_fetch_array($result, MYSQLI_ASSOC) if foreach ($result as $row) gives the same result? I'm struggling to understand the benefit of mysqli_fetch if I can just use the basic foreach loop.

Upvotes: 1

Views: 206

Answers (1)

Dharman
Dharman

Reputation: 33400

There are a number of differences. If all you need are associative arrays in a loop then go for the clean foreach($result as $row). In most cases, you should never deal with mysqli_result directly in application logic anyway. You should use some kind of database abstraction layer.

foreach approach

This is the easiest and simplest way to loop over the result set. The result set will always be iterated from the beginning until the end and it can be done so multiple times without a need for manual rewinding.

$result = $stmt->get_result();
foreach($result as $row) {
    echo $row['field'];
}

The shortcomings are obvious: you always get an associative array. If you want numerical indices or if you want an object instead of an array then you need to fetch rows manually.

mysqli_result::fetch_array

This function returns a single row either as an associative array, numerical array, or both. There is an internal pointer that is incremented each time you call this function so that each time you call it you get the next row from the result set. You can, of course, rewind it at any time to any random row provided that the results are buffered client-side.

// traverses the result set in a reverse order
$result = $stmt->get_result();
for ($row_no = $result->num_rows - 1; $row_no >= 0; $row_no--) {
    $result->data_seek($row_no);
    $row = $result->fetch_array();
    echo " id = " . $row['id'] . "\n";
}

mysqli_result::fetch_assoc and mysqli_result::fetch_row

These are just syntactic sugar for the mysqli_result::fetch_array. They work in exact same way but only give you one type of array, either associative or numerical, respectively.

mysqli_result::fetch_object

This method works in a similar fashion to the previous three, but instead of an array it returns an object. Useful if you need to fetch results as instances of a specific class. The name of the class can be provided as an argument to this function.

class Movie {
    private $title;
    private $director;

    public function getTitle() {
        return $this->title.' by '.$this->director;
    }
}

$stmt = $mysqli->prepare('SELECT title, director FROM movie');
$stmt->execute();
$result = $stmt->get_result();
while ($obj = $result->fetch_object(Movie::class)) {
    printf("%s\n", $obj->getTitle());
}

mysqli_result::fetch_all

This is probably the most common option. It is used when you don't need to manipulate the result set in any specific way and you just want to get all results as an array. It works similarly to mysqli_result::fetch_array but instead of returning each row one at a time, it returns an array of arrays. Be careful, the default fetch mode is a numerical array.

$result = $stmt->get_result();
$data = $result->fetch_all(MYSQLI_ASSOC);

Upvotes: 3

Related Questions