Avoid executing MySQL queries within loops

The level of integration between PHP and MySQL makes it really easy to build dynamic web applications in very little time. Within just a couple of minutes you can be pulling data from a MySQL database and using it in your web app. Interacting with MySQL databases is one of the key things anyone would need to learn in order to be able to create powerful and useful web applications. When using MySQL databases to provide the data model to a web app there is one mistake in particular which a lot of people make particularly when starting out.

For this example we’re going to build a very simple web app which provides almost no value and isn’t very useful to anyone. It is however a good way of demonstrating this very common mistake and will provide a good example to apply an easy solution. The application we’re going to build will display a list of peoples names and beneath each of them display that persons favourite colours. We’re not going to cover any of the creation parts of the web app like adding new people or assigning colours so that’s left to you.

Create the MySQL tables

We need two MySQL tables, one to hold the people and another to hold the colours. The following SQL queries will set up the tables just as we need them.

CREATE TABLE  `person` (
`ID` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 30 ) NOT NULL
);

CREATE TABLE  `colour` (
`ID` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`personID` INT( 5 ) NOT NULL ,
`colour` VARCHAR( 30 ) NOT NULL
);

When you run the above SQL queries you should end up with the two tables needed for this web app. Before we go any further, let’s put some data into our tables. Run these queries too.

INSERT INTO `person` (`ID`, `name`) VALUES (NULL, 'Michael'), (NULL, 'Alyshea');
INSERT INTO `person` (`ID`, `name`) VALUES (NULL, 'Frank'), (NULL, 'Jim');

INSERT INTO `colour` (`ID`, `personID`, `colour`) VALUES (NULL, '1', 'Blue'), (NULL, '1', 'Black');
INSERT INTO `colour` (`ID`, `personID`, `colour`) VALUES (NULL, '2', 'Pink'), (NULL, '3', 'Green');
INSERT INTO `colour` (`ID`, `personID`, `colour`) VALUES (NULL, '3', 'Brown'), (NULL, '4', 'Yellow');

The wrong way of doing it

To begin with I’ll start by showing what the wrong way to do it is. A lot of people do this and there are several reason why you should avoid it at all costs. Let’s jump right in with the PHP code behind it.

// Query for all the people
$queryPeople = mysql_query("SELECT * FROM person") or die(mysql_error());

// Loop through each row
while($person = mysql_fetch_array($queryPeople))
{
	// Display the persons name
	echo "
" . $person['name'] . "

";

	// Query for this persons colours
	$queryColours = mysql_query("SELECT * FROM colour WHERE personID = '" . $person['ID'] . "'");

	// Loop through every colour
	while($colour = mysql_fetch_array($queryColours))
	{
		// Display the colour
		echo $colour['colour'] . "
";
	}
}

If you run the above bit of code which consists of a couple of MySQL queries and a couple of while loops you’ll end up with exactly what is expected – a list of all of the people with their favourite colours shown beneath. So what’s the problem?

The problem explained

The biggest problem with the PHP snippet above comes down to the fact that there’s a MySQL query inside a while loop. What this means is every single time that loop is executed another SQL query is also executed. Sure, in this useless web app we’ve got just four people and only a handful of colours so it’s not going to make that much of a difference. Imagine if this was a much larger system, a customer management application for example with tens of thousands of people in it. Keeping in mind that for every single person in the table a new SQL query is executed that adds up to an awful lot of queries, something which you really don’t want.

Large numbers of queries can cripple web applications and bring them to a grinding halt. With enough data in the tables or enough people accessing this script at once everything will start getting very slow very quickly.

So, what’s the solution?

How it should be done

The solution to this is actually very simple. In the above example with four people in the table there would be a total of 5 queries being run – one to grab all of the people and then a query for each person. With the following approach we can drop that down to just two queries. The great this about doing it this way is that whether you’ve got 4 people or 4,000 people there’s still only ever going to be two queries.

And this is how it’s done.

// Query for all the people
$queryPeople = mysql_query("SELECT * FROM person") or die(mysql_error());

// Loop through each row
while($person = mysql_fetch_array($queryPeople))
{
	// Put the person into an array using their ID as the array key
	$arrayPeople[$person['ID']] = $person;
}

// Query for all the colours
$queryColours = mysql_query("SELECT * FROM colour");
while($colour = mysql_fetch_array($queryColours))
{
	// Attach the colour onto the $arrayPeople array
	// We can do this because we know the personID from the colour table
	$arrayPeople[$colour['personID']]['colours'][] = $colour;
}

// Loop through each person
foreach($arrayPeople AS $person)
{
	// Display the persons name
	echo "
" . $person['name'] . "

";

	// Loop through every colour
	foreach($person['colours'] AS $colour)
	{
		// Display the colour
		echo $colour['colour'] . "
";
	}
}

To explain what’s happening, it’s really simple. Instead of querying for a persons colour each time in the while loop we’re getting every colour, regardless of who it belongs to before the loop even starts. The first important part to look at is where we loop through all of the people using a while loop. Within this loop we’re creating a new array $arrayPeople which will hold, well, all the people. Each time we add a new person to this array we’re setting the array element key to be the ID of that user.

Then, when we loop through all of the colours we’re able to attach the colour onto the right person because we know the personID which is taken from the colour table. This means that for every person that has one or more colours there’s an array as one of its elements which holds all of that persons colours. It’s then just another simple foreach loop to loop through that array and show the colour.

The end result is exactly the same and yet it’s done with 3 less queries. If there were 100 people in the table it would have meant 99 less queries.