Is a query within a query possible?

HairyArse

I'm trying to run 2 queries at the same time to get bits of information from two different tables about the same thing so I can put this data in a table, the following code seems fine to me but doesn't work properly. Any ideas?

<?
echo("<table>");
findgenre();
$query  = "SELECT * FROM tbl_articles where article_type = 'Review' ORDER BY id DESC";
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result))
{
foreach ($row as $field=>$value) 
		{
			if ($field == id)
				{
				$reviewid = $value;
				}
			else if ($field == author)
				{
				$reviewauthor = $value;
				}							
			else if ($field == score)
				{
				$reviewscore = $value;
				}
			else if ($field == game_title)
				{
				$reviewgame_title = $value;
				}

		}



$query2  = "SELECT genre FROM tbl_games where game_title = $reviewgame_title";
$result2 = mysql_query($query2);

while ($row = mysql_fetch_assoc($result2))
		{
		foreach ($row as $field2=>$value2) 
			{
				if ($field2 == genre)
				{
				$reviewgenre = $value2;
				}			
			}

		}




echo("<tr>");
echo("<td>$reviewgame_title</td></td><td>$reviewgenre</td><td>Score $reviewscore/10</td>");
echo("</tr>");				
}
echo("</table>");

I always get the error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/hqxxyucx/public_html/aatg_test/reviews.php on line 21

Line 21 being the while loop for my 2nd query..

TimC

You could use compound SQL queries, like so:

SELECT FROM table1 WHERE something IN (SELECT FROM table2);

Is that what you want to do?

Nibinaear

I don't think this is a problem with running two queries, one after the other, if that's what you mean. Also, you don't need to use different variables, such as $query2, you can just reuse the same ones.

I think this is to do with supplying mysql_fetch_assoc() with data it doesn't recognise.

Sorry I can't say more, but I don't yet understand this error and how to fix it. However, I have a theory. I would say that the $result2 doesn't have any value, so mysql_fetch_assoc() doesn't know what to do with it. Also, try spitting out $reviewgame_title to see what's in that.

Aparently the mysql result resource error has something to do with not connecting to the database (most of the time.)

Weedpacket

 "SELECT genre FROM tbl_games where game_title = $reviewgame_title";

You're missing the quotes from around $reviewgame_title. So the SQL is bad, so the query isn't run (and mysql_error() would have said why it failed), so there is no result set, so mysql_fetch_assoc() triggers a warning.

Moharo

good thing to do when you don't know if your sql queries are correct, do something like this:

<?php

$sql = "SELECT * FROM aaa ";

$result = NULL;

if($result = mysql_query($query))
{
while($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
//do output
}
}
else
{
echo "there was an error" . mysql_error();
}