Subqueries for each row? (php+mysql)

murder2 · 10-27-2005, 02:32 PM

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

Code:

$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 · 10-27-2005, 02:37 PM

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 · 10-27-2005, 04:13 PM

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 · 10-28-2005, 06:23 AM

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

Code:

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

And it worked just the way I wanted it to.

10-27-2005, 02:32 PM	#1 (permalink)
murder2 New Member Join Date: Oct 2005 Posts: 6 12.00 NP$ (Donate)	Subqueries for each row? (php+mysql) 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 Code: $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?

10-27-2005, 04:13 PM	#3 (permalink)
Crusader Senior Member Join Date: Aug 2003 Location: Canada Posts: 1,293 1,264.40 NP$ (Donate)	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. __________________ Near Fantastica \| Matthew Good - Vancouver >> Do you Frawlik? <<

10-28-2005, 06:23 AM	#4 (permalink)
murder2 New Member Join Date: Oct 2005 Posts: 6 12.00 NP$ (Donate)	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 Code: $num = $row['foreign_key'] $query2 = .... WHERE `thing` = $num And it worked just the way I wanted it to.

10-27-2005, 02:37 PM	#2 (permalink)
RJ NamePros Founder Administrator Join Date: Feb 2003 Location: Bay Area, CA Posts: 13,173 104,201.68 NP$ (Donate)	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.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Administrator