Subqueries for each row? (php+mysql)

[murder2]

I want to do a query, and for each row in that query, do another query based on an attribute value (foreign key) in that row. I can't get it to work, and I am wondering if this is possible??

code looks something like this

$query = "SELECT `id`, `name`, `foreign_key` FROM `the_table`";
$result = mysql_query($query) or die('Query failed: ' . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
  .....
  $query2 = "SELECT `this`, `that` FROM `table2` WHERE `thing` = $row['foreign_key']";
  $result2 = mysql_query($query2) or die('Query failed: ' . mysql_error());
  while ($row2 = mysql_fetch_assoc($result2)) {
   .......
  }
  mysql_free_result($result2);
}

any comments, hints or tips on how to solve this, please?

 

[RJ]

What error are you getting?

I see:
WHERE `thing` = $row['foreign_key']"

If foreign_key is not strictly a numerical value, you need to at least put 'apostrphes' around it.

 

[Crusader]

You should note that doing it the way you're doing now will result in a large amount of queries. I highly suggest you use an object oriented function to save yourself some server load. Loops $i++ are the key.

 

[murder2]

foreign_key is an integer yes.

And I am aware of that this is not an perfect solution, but the first query will only retur a couple of rows, so in this specific case there's no great efficiency-saver in solving this otherwise..

But I solved it, I just made a new variable before the query2

$num = $row['foreign_key']
$query2 = .... WHERE `thing` = $num

And it worked just the way I wanted it to.