What are some different ways to loop through a mysql result set? I'm new to PHP and MySQL so I'm looking for simple ways to loop through and an explanation as to how the provided code works.
Add a comment
|
6 Answers
the first example that comes to my mind:
<?php
$link = mysql_connect(/*arguments here*/);
$query = sprintf("select * from table");
$result = mysql_query($query, $link);
if ($result) {
while($row = mysql_fetch_array($result)) {
// do something with the $row
}
}
else {
echo mysql_error();
}
?>
2 Comments
Charlie Brumbaugh
Given that the mysql_ extension is removed entirely in PHP 7 somebody should edit this code to use the mysqli_ functions
mickmackusa
Every once in a while I see SO questions with the query written inside of
sprintf() for no good reason. Old posts like this teach researchers to do funny/suboptimal things.Here is a full example:
http://php.net/manual/en/mysqli-result.fetch-array.php
- Connect
- Select database
- Make query
- Cycle on the result and fetch array to get the row
2 Comments
nash
One thing to note: mysql_query can return FALSE, which will fail the while loop in the php.net example. So, do an
if first (like in Gabriel Sosa's answer). Other than that, example code on the php.net page is good.
youcantexplainthat
This example is no longer valid in php7. There are some examples with mysqli here: php.net/mysqli-stmt.fetch
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset('utf8mb4');
$sql = "SELECT * FROM table";
$result = $conn->query($sql)->fetch_all(MYSQLI_ASSOC);
foreach ($result as $row):
?>
//Loop Content... Example:-
<li><?= $row['name']; ?></li>
<?php
endforeach;
?>
1 Comment
mickmackusa
fetch_all() should not be used if the script is processing the result set in the same "layer". In fact, a mysqli result set object can be fed directly into a foreach() and treated as an indexed array of associative arrays, so no fetching function calls are actually needed. This answer is missing its educational explanation.If you are using MySQL versions 4.1.3 or later, it is strongly recommended that you use the mysqli extension instead [of the mysql extension that is not further developed, does not support features of MySQL 4.1+, no prepared and multiple statements, has no object-oriented interface, ...]
see mysqli-stmt.fetch for the procedural and object oriented ways to loop over a mysqli result set.
1 Comment
sassy_geekette
I am familiar with the mysqli extension and know to use it, but didn't know why it's preferred until you explained, thanks!
In modern php, you don't need to call any functions to fetch the data of a result set. The result set object is immediately iterable and a foreach() will allow you to traverse the data as if it was an indexed array of associative arrays. (Since PHP5.4.0, circa March 1, 2012 - "MySQLi: Added iterator support in MySQLi. mysqli_result implements Traversable.")
Example:
foreach ($conn->query("SELECT one, two, three FROM my_table") as $index => $row) {
echo "<div>$index: {$row['one']}, {$row['two']}, {$row['three']}</div>";
}
Comments
I'd recommend creating a database function that acts as a wrapper for your database fetch. Makes it easier to switch out database function calls, and even, down the road, the type of database itself (e.g. mysql->postgresql or mysql->couchdb or using the PDO object or something).
Some function that -you- create that takes a query and returns a fully associative array, and then you stick the database connection code inside there.
It may also be good to check into using PDO down the road, since it abstracts away database specific functions for you, working with mysql, postgresql, etc.
