Up to now, you've learned how to create databases and tables, and insert data into them. Now, let's retrieve the data that was inserted in the previous tutorial. The SQL SELECT
statement is used to fetch records from database tables. Its basic syntax is as follows:
Let's create a SQL query using the SELECT
statement. Afterwards, we will execute this query using the PHP mysqli_query()
function to retrieve the table data.
Assume our persons database table contains the following records:
+----+------------+-----------+----------------------+ | id | first_name | last_name | email | +----+------------+-----------+----------------------+ | 1 | Peter | Parker | peterparker@mail.com | | 2 | John | Rambo | johnrambo@mail.com | | 3 | Clark | Kent | clarkkent@mail.com | | 4 | John | Carter | johncarter@mail.com | | 5 | Harry | Potter | harrypotter@mail.com | +----+------------+-----------+----------------------+
The PHP code in the next example retrieves all data stored in the persons table (using the asterisk character (*
) to select all columns).
<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
// Attempt select query execution
$sql = "SELECT * FROM persons";
if($result = mysqli_query($link, $sql)){
if(mysqli_num_rows($result) > 0){
echo "<table>";
echo "<tr>";
echo "<th>id</th>";
echo "<th>first_name</th>";
echo "<th>last_name</th>";
echo "<th>email</th>";
echo "</tr>";
while($row = mysqli_fetch_array($result)){
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['first_name'] . "</td>";
echo "<td>" . $row['last_name'] . "</td>";
echo "<td>" . $row['email'] . "</td>";
echo "</tr>";
}
echo "</table>";
// Free result set
mysqli_free_result($result);
} else{
echo "No records matching your query were found.";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
// Close connection
mysqli_close($link);
?>
In the example above, the mysqli_query()
function returns data stored in the $result
variable. Each call to mysqli_fetch_array()
retrieves the next row from the result set as an array. The while loop iterates through all rows in the result set. Accessing individual field values from the row can be done by using either the field index or field name with the $row
variable, such as $row['id']
or $row[0]
, $row['first_name']
or $row[1]
, $row['last_name']
or $row[2]
, and $row['email']
or $row[3]
.
To use a for
loop, you can obtain the number of rows returned by passing the $result
variable to mysqli_num_rows()
. This count determines the loop's iteration count.