How to loop through a mysql result set

Question

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.

score 18 · Answer 1 · answered Nov 18 '09 at 15:30

18

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();
    }
?>

answered Nov 18 '09 at 15:30

Gabriel Sosa

7,897
4
38
48

Given that the mysql_ extension is removed entirely in PHP 7 somebody should edit this code to use the mysqli_ functions – Charlie Brumbaugh Apr 23 '18 at 16:35
2

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. – mickmackusa Sep 29 '19 at 05:47

score 8 · Accepted Answer · edited Nov 25 '16 at 19:30

8

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

edited Nov 25 '16 at 19:30

miken32

42,008
16
111
154

answered Nov 18 '09 at 15:26

Palantir

23,820
10
76
86

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. – nash Nov 18 '09 at 15:40
This example is no longer valid in php7. There are some examples with mysqli here: http://php.net/mysqli-stmt.fetch – youcantexplainthat Jul 07 '16 at 16:13

score 7 · Answer 3 · edited Apr 01 '20 at 11:01

7

<?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;
?>

edited Apr 01 '20 at 11:01

Dharman

30,962
25
85
135

answered Aug 13 '16 at 03:55

Rahul Mukati

744
2
10
15

`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. – mickmackusa Mar 24 '21 at 05:46

score 4 · Answer 4 · answered Nov 18 '09 at 15:48

4

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.

answered Nov 18 '09 at 15:48

ax.

58,560
8
81
72

I am familiar with the mysqli extension and know to use it, but didn't know why it's preferred until you explained, thanks! – sassy_geekette Nov 18 '09 at 16:19

mickmackusa · Answer 5 · 2021-03-24T12:48:25.613

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>";
}

Kzqai · Answer 6 · 2009-11-18T15:46:59.803

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.

How to loop through a mysql result set

6 Answers6

Linked

Related