CODEWITHSUNDEEP
phpmysqli
Gavriloski
Gavriloski

Reputation: 37

Is there any need to use fetch_object or fetch_array?

I recently found out that I can print results from database without using mysqli_fetch_object function.

So for example: Let's say we have simple sql select statment which would be executed by using something like this:

$conn = mysqli_connect('localhost', 'root', '', 'example_db');
$result = mysqli_query($conn, "SELECT * FROM users");

Next step would be something like this:

while($row = mysqli_fetch_object($result)) echo $row->username

Where username is corresponding column name in table of users.

Then I figured out this works too for printing it out:

foreach($result as $row) echo $row['username'];

After couple of var_dumps and fruther reading of php documentation. My only guess would be that this is possible because of this:

5.4.0 Iterator support was added, as mysqli_result now implements Traversable.

So if my claims are true. Is there any need for using something like mysqli_fetch_object or similar function mysqli_fetch_array ?

Upvotes: 0

Views: 978

Answers (1)

Dharman
Dharman

Reputation: 33400

There are basically 3 ways to get the results in PHP via MySQLi API.

In order of preference:

  1. Fetch all records into a PHP array. You can use the fetch_all() method to get all records into a multidimensional array. You can decide whether the records should be fetch as an associative array, numeric array or both. You have no way of fetching an array of objects. e.g.

    $results = $conn->query("SELECT * FROM users")->fetch_all(MYSQLI_ASSOC);

  2. You can also iterate the mysqli_result object directly. This will give you results as associative arrays and you can iterate as many times as you'd like. 

    $result = $conn->query("SELECT * FROM test1");

foreach ($result as $r) {
    var_dump($r['id']);
}
echo '------------------------------------------------------------';
foreach ($result as $r) {
    var_dump($r['id']);
}

  3. If the other two options are not flexible enough or you would like to get the results as objects then you need to rely on the other methods i.e. fetch_object(), or fetch_array() 

    $result = $conn->query("SELECT * FROM test1");

$objects = []; // collect objects into an array
while ($obj = $result->fetch_object()) {
    $objects[] = $obj;
}

    The problem here is that you need to get each row separately and you can't iterate again once you reach the end unless you rewind the object's internal pointer. This method can be useful sometimes if you need to apply some transformations when collecting the results.

Of course as a side note I recommend to use PDO instead, which is more powerful less buggy and easier to use.

Upvotes: 2

Related Questions