MySQLi Tutorial: Selecting Multiple Entries

Hey again, I’ll be showing you how to handle multiple rows from a query, using MySQLi, for example:

SELECT * FROM `people`;

The table I’ll be using is the same table from this tutorial. Since you probably already have that table ready, let’s get started!

<?php
/* make our connection, remember the order:
host, username, password, database */
$sql = new mysqli('localhost','username','password','database');
 
/* let's write our query, we'll select everyone, showing the oldest first. */
$query = "SELECT * FROM `people` ORDER BY `age` DESC;";
 
/* set our result information */
$result = $sql->query($query);
 
/* now that we have our result information, we can cycle through the
database rows much like we used to in the procedural days */
echo 'Display using objects.<br />';
while ( $row = $result->fetch_object() ) {
    echo "{$row->name} is {$row->age} years old.<br />";
}
 
/* if you prefer to use associative arrays, you can do the following */
echo 'Display using an associative array.<br />';
while ( $row = $result->fetch_assoc() ) {
    echo "{$row['name']} is {$row['age']} years old.<br />";
}
 
/* ok we're done here, close our connection */
$sql->close();
?>

Running the above code should yield the following results:

Display using objects.
Mike is 26 years old.
Angela is 21 years old.
Joe is 19 years old.
Stephanie is 18 years old.
Display using an associative array.
Mike is 26 years old.
Angela is 21 years old.
Joe is 19 years old.
Stephanie is 18 years old.

You may wish to check that there are actually results before trying to display them, this is often a good idea. You can do this by check the number of rows returned by your query, you can do this with the following:

<?php
/* so again, make your connection */
$sql = new mysqli('localhost','username','password','database');
 
/* write our query */
$query = "SELECT * FROM `people` ORDER BY `age` DESC;";
 
/* set our result information */
$result = $sql->query($query);
 
if ( $result->num_rows > 0 ) {
    echo "We found {$result->num_rows} people:<br />";
    while ( $row = $result->fetch_object() ) {
        echo "{$row->name} is {$row->age} years old.<br />";
    }
} else {
    echo 'There are no results to display.';
}
 
/* and close up */
$sql->close();
?>

In our case, you should get:

We found 4 people:
Mike is 26 years old.
Angela is 21 years old.
Joe is 19 years old.
Stephanie is 18 years old.

And there you have it, a quick and painless way to display information from a query using the MySQLi class. Hope you found it useful.

Author: Mike on March 27, 2011
Category: MySQLi, Tutorials
Tags: , ,

One thought on “MySQLi Tutorial: Selecting Multiple Entries”

  1. Danice says:

    I cannot tell a lie, that rlelay helped.

Comments are closed.

Last articles