Queries that have been generated would be useless if they could not be output or worked on with PHP.
This article will describe how to use the data received from the database using PHP.
Sample queries
Below are some sample queries that will be referenced in this article
($query1
and $query2
):
<?php $myConnection = mysqli_connect($dbName, $dbUsername, $dbPassword); $query1 = "SELECT * FROM table1 WHERE id > 4"; $query2 = "SELECT * FROM table1 WHERE forename = 'John'"; $result1 = mysqli_query($myConnection, $query1); if(!$result1) echo "Failure!"; $result2 = mysqli_query($myConnection, $query2); if(!$result2) echo "Failure!"; mysqli_close($myConnection); ?>
Assume that the following table ("table1") has been used:
id | forename | surname | age |
---|---|---|---|
0 | James | Adams | 44 |
1 | Frank | Adams | 45 |
2 | John | Roosevelt | 45 |
3 | Mark | French | 46 |
4 | Frank | Skeldon | 43 |
5 | Justin | McGregor | 51 |
6 | Helga | Adams | 45 |
7 | John | Smith | 46 |
Working with the result
The important note is that the information that has been retrieved from the server is
now in the $result1
and $result2
variables.
This means that any kind of references to the database data will only refer to the variables. If the database changes after the query, these changes will not be reflected in the variables.
Count the number of results
MySQL can count the number of rows using the COUNT
aggregate function. This may be
useful if all that is required is the count of the results but in cases where the information is required multiple
times this would be slower than using PHP to count them.
PHP provides the mysqli_num_rows
function to get the number of rows found in a query:
<?php //Should output 3 echo mysqli_num_rows($result1); //Should output 2 echo mysqli_num_rows($result2); ?>
Getting the fields from the table
It may also be useful to obtain the fields (columns) from the results. This can be achieved with the
mysqli_fetch_fields
function:
<?php $fields = mysqli_fetch_fields($result1) ?>
The field names can then be echoed to the page using the following code:
<?php $fields = mysqli_fetch_fields($result1); foreach($fields as $field) { echo $field->name; } ?>
Getting the results from the query
Of course, none of these functions have worked on the actual result, giving out the values returned. This is
achieved, mostly, using the mysqli_fetch_array
function.
This function transforms the result into a PHP array. Each row itself is an associative array.
This means that the result can be iterated with a foreach
loop:
<?php $rows = mysqli_fetch_array($result1); foreach($rows as $row) { //Will output the id column echo $row[0]; //Will output the forename column echo $row[1]; } ?>
Alternatively, the column name can be used instead of using the index of the column:
<?php $rows = mysqli_fetch_array($result1); foreach($rows as $row) { //Will output the id column echo $row['id']; //Will output the forename column echo $row['forename']; } ?>