PHP mysqli_fetch_all() Function
A PHP result object (of the class mysqli_result) represents the MySQL result, returned by the SELECT or, DESCRIBE or, EXPLAIN queries.
The mysqli_fetch_all() function accepts a result object as a parameter, retrieves all the rows in the given result object.
Syntax
mysqli_fetch_all($result, [$type]);
Parameters
This is an identifier representing a result object.
This is an integer value specifying the type of the returned array. The value of this cane be one of the following −
Return Values
The PHP mysqli_fetch_all() function returns an array (associative or, numeric) which contains the rows of the result object.
PHP Version
This function was first introduced in PHP Version 5 and works works in all the later versions.
Example
Following example demonstrates the usage of the mysqli_fetch_all() function (in procedural style) −
This will produce following result −
Table Created. Record Inserted. Array ( [0] => Array ( [0] => 1 [1] => Sikhar [2] => Dhawan [3] => Delhi [4] => India ) [1] => Array ( [0] => 2 [1] => Jonathan [2] => Trott [3] => CapeTown [4] => SouthAfrica ) [2] => Array ( [0] => 3 [1] => Kumara [2] => Sangakkara [3] => Matale [4] => Srilanka ) )
Example
In object oriented style the syntax of this function is $result->fetch_all(); Following is the example of this function in object oriented style $minus;
query("CREATE TABLE Test(Name VARCHAR(255), AGE INT)"); $con -> query("insert into Test values('Raju', 25),('Rahman', 30),('Sarmista', 27)"); print("Table Created. \n"); $stmt = $con -> prepare( "SELECT * FROM Test WHERE Name in(?, ?)"); $stmt -> bind_param("ss", $name1, $name2); $name1 = 'Raju'; $name2 = 'Rahman'; //Executing the statement $stmt->execute(); //Retrieving the result $result = $stmt->get_result(); //Fetching all the rows $rows = $result->fetch_all(); print_r($rows); //Closing the statement $stmt->close(); //Closing the connection $con->close(); ?> This will produce following result −
Table Created. Array ( [0] => Array ( [0] => Raju [1] => 25 ) [1] => Array ( [0] => Rahman [1] => 30 ) )
mysqli_fetch_all
Returns a two-dimensional array of all result rows as an associative array, a numeric array, or both.
Note:
Prior to PHP 8.1.0, available only with mysqlnd.
Parameters
This optional parameter is a constant indicating what type of array should be produced from the current row data. The possible values for this parameter are the constants MYSQLI_ASSOC , MYSQLI_NUM , or MYSQLI_BOTH .
Return Values
Returns an array of associative or numeric arrays holding result rows.
Changelog
Examples
Example #1 mysqli_result::fetch_all() example
mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = new mysqli ( «localhost» , «my_user» , «my_password» , «world» );
$result = $mysqli -> query ( «SELECT Name, CountryCode FROM City ORDER BY ID LIMIT 3» );
$rows = $result -> fetch_all ( MYSQLI_ASSOC );
foreach ( $rows as $row ) printf ( «%s (%s)\n» , $row [ «Name» ], $row [ «CountryCode» ]);
>
mysqli_report ( MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT );
$mysqli = mysqli_connect ( «localhost» , «my_user» , «my_password» , «world» );
$result = mysqli_query ( $mysqli , «SELECT Name, CountryCode FROM City ORDER BY ID LIMIT 3» );
$rows = mysqli_fetch_all ( $result , MYSQLI_ASSOC );
foreach ( $rows as $row ) printf ( «%s (%s)\n» , $row [ «Name» ], $row [ «CountryCode» ]);
>
The above examples will output:
Kabul (AFG) Qandahar (AFG) Herat (AFG)
See Also
- mysqli_fetch_array() — Fetch the next row of a result set as an associative, a numeric array, or both
- mysqli_fetch_column() — Fetch a single column from the next row of a result set
- mysqli_query() — Performs a query on the database
User Contributed Notes 4 notes
I tested using «fetch all» versus «while / fetch array» and :
fetch-all uses less memory (but not for so much).
In my case (test1 and test2): 147008,262848 bytes (fetch-all) versus 147112,262888 bytes (fetch-array & while.
So, about the memory, in both cases are the same.
However, about the performance
My test takes :350ms (worst case) using fetch-all, while it takes 464ms (worst case) using fetch-array, or about 35% worst using fetch array and a while cycle.
So, using fetch-all, for a normal code that returns a moderate amount of information is :
a) cleaner (a single line of code)
b) uses less memory (about 0.01% less)
c) faster.
php 5.6 32bits, windows 8.1 64bits
get array of rows with mysqli result
I need to get all the rows from result object. I’m trying to build a new array that will hold all rows. Here is my code:
$sql = new mysqli($config['host'],$config['user'],$config['pass'],$config['db_name']); if (mysqli_connect_errno()) < printf("Connect failed: %s\n", mysqli_connect_error()); exit(); >$query = "SELECT domain FROM services"; $result = $sql->query($query); while($row = $result->fetch_row()); < $rows[]=$row; >$result->close(); $sql->close(); return $rows; $rows is supposed to be the new array that contains all, rows but instead I get an empty array. Any ideas why this is happening?
2 Answers 2
You had a slight syntax problem, namely an errant semi-colon.
while($row = $result->fetch_row()); Notice the semi-colon at the end? It means the block following wasn’t executed in a loop. Get rid of that and it should work.
Also, you may want to ask mysqli to report all problems it encountered:
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $sql = new mysqli($config['host'], $config['user'], $config['pass'], $config['db_name']); $query = "SELECT domain FROM services"; $result = $sql->query($query); $rows = []; while($row = $result->fetch_row()) < $rows[] = $row; >return $rows; Newest versions of mysqli have some improvements that can simplify such a task.
First, of all, there is a useful function to return an array with all rows returned by a query, mysqli_fetch_all()
It means in case you need a simple enumerated array, the code would be much simpler:
$query = "SELECT domain FROM services"; $result = $sql->query($query); return $result->fetch_all(MYSQLI_ASSOC); return $sql->query("SELECT domain FROM services")->fetch_all(MYSQLI_ASSOC); However, if you need to use some column to index the resulting array, you still need a while loop like this:
$query = "SELECT id, domain FROM services"; $result = $sql->query($query); $data = []; while ($row = $result->fetch_assoc())
Note that you should always initialize an array before filling it up, because such a variable could already exist.
Also, mysqli_result class is now Traversable. It means you can use it in the foreach loop right away, as though it’s an array contains all rows from the database:
$query = "SELECT domain FROM services"; $result = $sql->query($query); foreach ($result as $row)
But it is actually just a syntax sugar for the while loop — you cannot access values of this «array» directly, which makes this feature of a little use actually.
Obligatory notes.
This question is a decade old, and the way a connection is made and the query is performed, both in the question and the accepted answer, are obsoleted and frowned upon nowadays.
When a connection is made, there are several things to keep in mind. I wrote an article on how to connect with mysqli properly that provides a correct connection example emphasizing on the following issues:
- a proper error reporting mode must be set
- a proper character set must be set
- no manual error reporting code should be ever used (like die(mysqli_connect_error()) )
- a connection has to be made only once, in a separate file, and then just included into every script that needs a database interaction. in case a database code is used in a function, a connection variable must be passed in as a function parameter.
When it goes to running a query, there are several things to keep in mind as well:
- when even a single variable is used in the query, a prepared statementmust be used instead of mysqli_query()
- as a result, a special function called mysqli_stmt_get_result() should be used in order to use familiar fetch functions to get the resulting rows. In case this function is not available you are probably to tick some checkbox in your cpanel (look for one labeled mysqlnd ).
- given a prepared statement with mysqli, although being obligatory, takes a lot code to write, it is advised to use a helper function for mysqli that would perform most of work automatically and make a mysqli prepared statement a smooth as a regular query.
- no manual error reporting code should be ever used (like die(mysqli_error()) ). Thanks to the proper error mode, mysqli will report all errors automatically.
mysqli query results to show all rows
This is fine if there is only one result as I can just echo $row[‘title’] but if there are lots of results, how do I get this to loop through and print every row? I’m sure this is really simple but I’m just not sure what I need to search for in Google. I’m looking for a mysqli equivalent of this:
while( $row = mysql_fetch_array($result) ) < echo $row['FirstName'] . " " . $row['LastName']; echo "
"; > 4 Answers 4
Just replace it with mysqli_fetch_array or mysqli_result::fetch_array 🙂
while( $row = $result->fetch_array() ) < echo $row['FirstName'] . " " . $row['LastName']; echo "
"; > Almost all mysql_* functions have a corresponding mysqli_* function.
Simple mysqli solution:
$db = new mysqli('localhost','user','password','database'); $resource = $db->query('SELECT * FROM table WHERE 1'); while ( $rows = $resource->fetch_assoc() ) < print_r($rows);//echo ""; > $resource->free(); $db->close(); With Error Handling: If there is a fatal error the script will terminate with an error message.
// ini_set('display_errors',1); // Uncomment to show errors to the end user. mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); $db = new mysqli('localhost','user','password','database'); $resource = $db->query('SELECT field FROM table WHERE 1'); while ( $row = $resource->fetch_assoc() ) < echo ""; > $resource->free(); $db->close(); Using iterators: Support was added with PHP 5.4
$db = new mysqli('localhost','user','password','database'); foreach ( $db->query('SELECT * FROM table') as $row ) < print_r($row);//echo ""; > $db->close(); Fetch a single record: This code does not require a loop.
$db = new mysqli('localhost','user','password','database'); $resource = $db->query('SELECT field FROM table'); $row = $resource->fetch_assoc(); echo ""; $resource->free(); $db->close();