Using MySQL queries in PHP

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:

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_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'];
	}
?>