Php mysqli query fetch all

Fetch_all

In this article, we will focus on the mysqli_fetch_all() function in PHP, which is used to fetch all rows from a MySQLi result set as an associative or numeric array. We will provide you with an overview of the function, how it works, and examples of its use.

Introduction to the mysqli_fetch_all() function

The mysqli_fetch_all() function is a built-in function in PHP that is used to fetch all rows from a MySQLi result set as an associative or numeric array. This function is useful when you need to fetch all rows from a MySQLi query and store them in an array for further processing.

How to use the mysqli_fetch_all() function

Using the mysqli_fetch_all() function is very simple. You just need to call the function on a valid MySQLi result set. Here is an example:

 $mysqli = mysqli_connect("localhost", "username", "password", "database"); $query = "SELECT * FROM my_table"; $result = mysqli_query($mysqli, $query); if ($result) < $rows = mysqli_fetch_all($result, MYSQLI_ASSOC); foreach ($rows as $row) < echo $row['column1'] . " - " . $row['column2'] . "\n"; > > mysqli_close($mysqli); ?>

In this example, we call the mysqli_connect() function to connect to a MySQL database with a username and password. We then execute a query using the mysqli_query() function and store the result in a variable. We check if there was a result using the $result variable. If there was a result, we call the mysqli_fetch_all() function to fetch all rows from the result set as an associative array. We then loop through the array and output the values of two columns for each row.

Advanced usage

The mysqli_fetch_all() function can also be used in more advanced scenarios. For example, you can use the function to fetch rows as a numeric array instead of an associative array. Here is an example:

 $mysqli = mysqli_connect("localhost", "username", "password", "database"); $query = "SELECT * FROM my_table"; $result = mysqli_query($mysqli, $query); if ($result) < $rows = mysqli_fetch_all($result, MYSQLI_NUM); foreach ($rows as $row) < echo $row[0] . " - " . $row[1] . "\n"; > > mysqli_close($mysqli); ?>

In this example, we call the mysqli_connect() function to connect to a MySQL database with a username and password. We then execute a query using the mysqli_query() function and store the result in a variable. We check if there was a result using the $result variable. If there was a result, we call the mysqli_fetch_all() function to fetch all rows from the result set as a numeric array. We then loop through the array and output the values of two columns for each row.

Conclusion

In conclusion, the mysqli_fetch_all() function is a useful tool for fetching all rows from a MySQLi result set and storing them in an array for further processing. By understanding how to use the function and its advanced usage scenarios, you can take advantage of this feature to create powerful and flexible MySQLi queries.

Источник

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

Источник

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 ) )

Источник