The 2 are functions are from the mysqli_result object:
1.- fetch_array(): Get a row in an array with only one record.
In this case, do you query for each row the database?
2.- fetch_all(): Gets all the rows at once and returns an array with all of them.
And in this case, only the query once (the database)?
I don't know what would be the best way to do it and the most optimal. I don't know if it would be better to get the row one by one or if getting them all at once would be worse.
Examples:
1.-fetch_array():
$resultUsers = getAllUsers($db);
while($row = $resultUsers->fetch_array()){
echo $row['name'];
}
2.- fetch_all():
$resultUsers = getAllUsers($db);
foreach ($resultUsers->fetch_all(MYSQLI_ASSOC) as $value) {
echo $value['name'] . "<br>";
}
2.A.- That the getAllUsers() function returns the entire array with fetch_all() inside the array and here it goes through it.
foreach (getAllUser($db) as $value) {
echo $value['name'] . "<br>";
}
What I don't know is how the Database is queried, if in the fetch_array() query one by one of the database and fetch_all() all at once. Or in both, the query is done all at once and what changes is how to go through the data.
Or if there is a better way to make it more optimal or approach it in another way, put it, I hope your help (from someone who knows about the subject). All the best.
I find it an interesting question. It's a shame that it seems abandoned by the OP.
However, it is advisable to contribute something to it.
In most programming situations regarding php-mysql it will be necessary to get data from the database to compare it, display it on the screen, etc.
There are basically 3 methods that are the most used to get result sets in MySQLi:
mysqli_fetch_assoc()
: Which gets an associative array of data.mysqli_fetch_array()
: Returns a combination of associative elements as well as numerically indexed data.mysqli_fetch_object()
: Returns an object with properties that correspond to the obtained row.An interesting comparison at spearheadsoftwares.com (see image) examines all three methods using benchmark tests performed by up-to-date software under various load conditions.
In the test the data results of 10, 100, 1000, 10000, 100000, 1000000, 10000000 records are obtained to see how fast and efficient each function is.
Each dataset contains a column of type
int
that is unique, and a column of typevarchar
. That is, a normal data set, like the ones we usually use in any typical query.As it is said that a picture is worth a thousand words (although there are a lot of lies in that sentence... :) ... Let's leave that for another forum.
This is the graph of the result:
Looking at these results, the following can be concluded:
MySQLi's default functionality,
mysqli_fetch_assoc
, is running with better performance than the other 2 methods (mysqli_fetch_array
andmysqli_fetch_object
).The performance of
mysqli_fetch_object
is justified when it returns objects instead of native arrays which always bring better memory usages than any other type of output array. And to use object oriented result set approach the other 2 functions cannot satisfy.In the case of
mysqli_fetch_array()
, the problem here is in the uses of daily programming, many php programmers have usedmysqli_fetch_array()
it as such, without going into the details of its use. By default,mysqli_fetch_array
it returns two types of result sets based on its default valueresult_type MYSQLI_BOTH
:Result_type: MYSQLI_NUM
- which gives us just an array of numbered indices, for example:array ([0] => 'apple')
Result_type: MYSQLI_ASSOC
- which is just an associative array, eg:array ('fruit' => 'apple')
where'fruit'
will be the name of the database column or alias used.So when we use we will
mysqli_fetch_array
get a duplicate set of data consisting of both numbered indices and associative sets, which of course is our performance blocker.Therefore whenever you are using the function
mysqli_fetch_array
always specify thereturn_type
of the required result set array.conclusion
Both for performance and because, in most cases, we will need an associative array during interactive data management in php, the most recommended function will be
mysqli_fetch_assoc()
In effect, the query is only executed once. The only big difference is the way they allow you to work with the records. One by one or all at once.
fetch_array() Working a single row of results.
And hey, mysqli_fetch_all() returns all the rows in an array in one step. This function consumes more memory than fetch_array()
That will depend on you because if you have a lot of results or expect a large number of results you could block the memory So it would be best to go through them one by one instead of extracting all of them So this will depend on the use you are going to give it and the implementation you want to use according to your needs