NameSilo

Subqueries for each row? (php+mysql)

SpaceshipSpaceship
SpaceshipSpaceship
SpaceshipSpaceship
Watch

murder2

New Member
Impact
1
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?
 
0
Close
The views expressed on this page by users and staff are their own, not those of NamePros.
GoDaddyGoDaddy
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.
 
0
Close
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.
 
0
Close
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. :)
 
0
Close
DomainEasy: flexible LTO options. Close more domain deals with financing.

We're social

Escrow.com
Spaceship
Domain Recover
CryptoExchange.com

NamePros updates

Filters for activity feeds
Your news feed, Latest activity, and the activity tab on profiles now have filter buttons: All, Posts, Quick replies, and Other. Your...
AIVikings
Catchy
  • The sidebar remains visible by scrolling at a speed relative to the page’s height.
Back