


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.
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 |
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.
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); ?>
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; } ?>
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_fields($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_fields($result1); foreach($rows as $row) { //Will output the id column echo $row['id']; //Will output the forename column echo $row['forename']; } ?>
![]() | Happy New Year everyone! #2021NewYear 26 days ago |
![]() | Happy New Year everyone! All the best, Jamie Balfour. 26 days ago |
![]() | Retweet @PCMag: Adobe Flash support officially ends today. https://t.co/NNLcFK2yPx ![]() 26 days ago |