SQL Loop within a SQL loop

I have an online database of different magazines that are archived and only some people have access to some magazines. I want to list the magazines that a user is allowed to see, and also the issues associated with that magazine.

For example the output would be:

Field & Stream
-Issue #1 Jan 2010
-Issue #2 Feb 2010

Time Magazine

  • Issue #1 Jan 2010
  • Issue #2 Feb 2010

My table structure is like this:

magazines
==============================
mag_id	|	name
------------------------------
1	|	Field & Stream
2	|	Time Magazine

magazine_permissions
==============================
mag_id	|	user_id
------------------------------
1	|	1
2	|	1


magazine_issues
=====================================
mag_id	|	issue		|	date
-------------------------------------
1	|	Issue #1	|	Jan 2010
1	|	Issue #2	|	Feb 2010
2	|	Issue #1	|	Jan 2010
2	|	Issue #2	|	Feb 2010

My script is something like this:


// select all the magazines the user is allowed to see
$query = "SELECT * FROM magazine_permissions WHERE user_id='1'";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc($result)){
	
	$mag_id = $row['mag_id'];
	
	// loop through and get the title of each magazine
	$query = "SELECT * FROM magazine WHERE mag_id='$mag_id'";
	$result = mysql_query($query) or die(mysql_error());
	
	while ($row = mysql_fetch_assoc($result)){
	
		$name = $row['name'];
		
		echo $name."<br/>";
		
		// loop through and get the issue of each magazine
		$query = "SELECT * FROM magazine_issues WHERE mag_id='$mag_id'";
		$result = mysql_query($query) or die(mysql_error());
			
		while ($row = mysql_fetch_assoc($result)){
			
			$issue = $row['issue'];
			$date = $row['date'];
		
			echo "$name $date<br/>";
		}
		
		echo "<p>";
	}
}

Am i allowed to have subsequent SQL queries within loops of other queries? Perhaps my approach is wrong and I should be using a more complex SQL statement to handle this request in one query?

An inner join could do the job quite well.


SELECT magazine.name, magazine_issues.issue, magazine_issues.date
FROM magazine_permissions
    INNER JOIN magazines ON magazine.mag_id = magazine_permissions.mag_id
    INNER JOIN magazine_issues ON magazine_issues.mag_id = magazine.mag_id
WHERE user_id = 1
ORDER BY name INC, issue INC

The people in the SQL Forum are likely to have some ideas on improving that too.

spot on
and if things are too complicated and tables are too big it usually join two tables and call a generic query function to return a value from third table…same thing but that way it increases the code understandibility(atleast for me :slight_smile: not recommended but thats how i how in some cases rather than joining 3 tables)

Ok guys thanks, I did a table join with 2 SQL statements, then did another SQL to loop through the issues of the magazine which worked well. :slight_smile:

you can improve performance a lot more by not doing a query in a loop

hello sir,
is it
$value=two joins -> query with value(with in loop)<3 tables joins
in performance?

is it negligible or is it something we should change immediately?

as said for readability and make things less complex i usually follow the same techinque
when i join 3 big tables ,isn’t it better and faster if we join 2 table first …then free those resource and make a 2nd query call?

thanks

You can easily measure the time it takes to do each of those.
Measure that time, and you will have an idea about the performance difference.
Commonly though, do that sort of work at the database end.

no

:slight_smile:

Hi Rudy :slight_smile: I did a table join with all three tables, but the issue from there is I don’t know how to work with the data in php from there.

You would loop through the results, printing them out, and at the end of showing each result you would save the magazine name to a temporary variable, such as $previousHeading.

At the start of the loop you would then check if the current heading is different from the previous heading. If it is then show the heading.

Here’s some pseudo code that summarises the technique

previous heading = null
start loop
    heading = row[heading]
    if (previous heading <> heading)
        show heading
    show issue
    previous heading = heading
end loop

thanks, for some reason in my tiny head I considered putting them into an associative array by title and then looping through them but just seemed more complicated than throwing another query/while loop in there.

thanks for all the help :slight_smile: