mysql nested subqueries with php

Question

I have two tables. I want to be able to get the orders of each id in the credit table from the orders table code below:

$downlinequery = "SELECT recid, Level, sp1 FROM credit 
                  WHERE sp1 = '$id' or sp2 = '$id' or sp3 = '$id' or sp4 = '$id' 
                  or sp5 = '$id' or sp6 = '$id' or sp7 = '$id' or sp8 = '$id'" ; 
$downlineresult = mysql_query($downlinequery) ;
while ($downlinerow = mysql_fetch_array($downlineresult)) {
  extract($downlinerow) ;
  $orderquery = "SELECT date,cc,cop FROM order WHERE userid='1127'" ;
  $orderresult = mysql_query($orderquery) or die("unable to get orders");
  while($orderrow = mysql_fetch_array($orderresult)){
    extract($orderrow);
    echo "$date,$cc,$cop" ;
  }
}

but i keep getting the error: unable to get orders

Is it possible to make queries while another is running ?

where you are getting error and please specify error. This will help me to give you best answer — Ravi
– Ravi, Commented Dec 23, 2012 at 12:32

Damien Pirsy · Accepted Answer · 2012-12-23 12:51:21Z

2

The error might happend because "ORDER" is a reserved word in MySQL. You should escape it with backticks:

$orderquery = "SELECT date,cc,cop FROM `order` WHERE userid=1127" ;

Same should be for "date", although that's being tolerated (see further down in the same page I linked)

As for your question, of course you can do queries in a loop (although that's not really the best in terms of performance). But if your tables have a foreing key (I'm guessing 'recid' and 'userid') you can build a JOINed query instead

edited Dec 23, 2012 at 12:51

answered Dec 23, 2012 at 12:45

Damien Pirsy

25.5k8 gold badges72 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kabuto178 Over a year ago

Good find, also he should change the name of the table all together. General rule is not to use reserved words for variables.

jcjr · Accepted Answer · 2012-12-23 12:40:43Z

1

To your actual question: I don't think the question is right. The first query is not running. It ran and it filled result in $downlineresult (mysql_query) and then, you are just iterating thru the parts of the result (mysql_fetch_array).

It seems you have error in your MySQL query, so you should use:

echo mysql_error();

See the description of the methods you use:

edited Dec 23, 2012 at 12:40

answered Dec 23, 2012 at 12:35

jcjr

1,50125 silver badges40 bronze badges

Collectives™ on Stack Overflow

mysql nested subqueries with php

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related