CODEWITHSUNDEEP
phpdatabasemysql
sassy_geekette
sassy_geekette

Reputation: 295

How to loop through a mysql result set

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.

Upvotes: 20

Views: 91235

Answers (6)

mickmackusa
mickmackusa

Reputation: 47874

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

Upvotes: 4

Rahul Mukati
Rahul Mukati

Reputation: 734

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

Upvotes: 7

Palantir
Palantir

Reputation: 24182

Here is a full example:

http://php.net/manual/en/mysqli-result.fetch-array.php

  1. Connect
  2. Select database
  3. Make query
  4. Cycle on the result and fetch array to get the row

Upvotes: 8

ax.
ax.

Reputation: 59927

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.

Upvotes: 4

Kzqai
Kzqai

Reputation: 23062

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.

Upvotes: 1

Gabriel Sosa
Gabriel Sosa

Reputation: 7956

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

Upvotes: 20

Related Questions