method within a a while function

[pablodelapena]

Hello all, once again I have ran into a small problem.

I am coding a site using classes. Im fairly new at OOP but have got the hang of it fairly easily. However I have run into a problem.

I have a class named SQL. And within SQL i have a function to query the database ($SQL->query_db()😉.

Its working a charm untill here.

I have 2 tables. Albums and Tracks. I query the albums table to find all the albums and put the result into a while function and start echoing them out.

However within that function I need to find the tracks related to that album, so I query Tracks.

My problem is that the while loop 'stops' as soon as the query for tracks comes up. This is my code....

<?php
class SQL {
function query_db($the_query) {

$this->query_id = @mysql_query($the_query);
if (mysql_errno() > 0)
{
$_GET['msg'] = mysql_error()."<p>Bad Query: ".$the_query;
require_once("error.php");
exit();
}

$this->query_count++;
return $this->query_id;

} // end query_db();
}




//code with problem...vvv
	$query = "SELECT * FROM albums ORDER BY Title";
	$SQL->query_db($query);
	if ($SQL->num_rows() == 0) {
		echo "Nope, we couldn't find any albums in the database.
So no MP3s for you yet.<p>Check back soon.";
	} else {

	echo "<center>";
	while ($row = $SQL->fetch_row()) {
	echo "<b>".$row['Title']."</b><br>";
	echo "<img src=\"../graphics/albumart/".$row['Title'].".jpg\"><p>";

	$query = "SELECT MediaID,Title FROM media 
WHERE type='audio' AND AlbumID='".$row['AlbumID']."'";
		$SQL->query_db($query);
			while ($track = $SQL->fetch_row()) {
			echo "<a href=media.php?a=dl&
MediaID=".$track['MediaID']."&type=audio>".$track['Title']."</a>";
			}

	}


?>

I've ahd trouble with this before without classes and to fix id change the mysql_query() variable. But this is different because its a seperate function i use all the time and I want the query count to be correct.

Note: Ive added line breaks in the code so it doesn't stretch the page. How nice am i?

[swr]

The problem is pretty fundamental to the way you are doing it.

You have two queries active at the same time, but you only have one variable to store the result resource. Sequence of events is like this:

1- select from albums result goes into $SQL->query_id
2- fetch row from select from albums
3- select from media result goes into $SQL->query_id
4- fetch all rows from media
5- you try to get the next row from albums, but instead get the next row from media (because that's the query $SQL->query_id now points to) so there are no more rows

You could work around this by having two SQL objects.
$sql1 = new SQL(); $sql2 = new SQL();
Then do $sql1->query_db("select from albums") and $sql2->query_db("select from media"). Each instances has its own query_id so they don't stomp on each other. They may need to use different database connections though, which could be a problem.

Why not handle this in SQL with a join? Then you only need one query and one while loop. SQL is designed to handle this sort of thing.

Like this:

select albums.*, media.MediaID, media.Title from albums, media where media.type = 'audio' and media.AlbumID = albums.AlbumID