vchris 3 Posted November 7, 2007 Report Share Posted November 7, 2007 I'm trying to create a subquery but it doesn't seem to be working.$sql1 = "SELECT * FROM db1.table1 WHERE field1=(SELECT post_id FROM db1.table2 WHERE field1=9795)";$result1 = mysql_query($sql1,$dbc1);while($row1 = mysql_fetch_assoc($result1)){ echo $row1['field1'].'<br />';}I get this error:Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in...Both queries work fine by themselves and looks to me like the subquery syntax is fine. Anything I could be missing? Quote Link to post Share on other sites
Ingolme 1,027 Posted November 7, 2007 Report Share Posted November 7, 2007 Maybe you should do the other query first and add it into the second query after. Like this:$res0 = mysql_query("SELECT post_id FROM db1.table2 WHERE field1=9795",$dbc1);$row0 = mysql_fetch_array($res0);$sql1 = "SELECT * FROM db1.table1 WHERE field1=(".$row0['post_id'].")";$result1 = mysql_query($sql1,$dbc1);while($row1 = mysql_fetch_assoc($result1)){echo $row1['field1'].'<br />';} Quote Link to post Share on other sites
vchris 3 Posted November 7, 2007 Author Report Share Posted November 7, 2007 But why would I have to do that? That's why they invented subqueries. Quote Link to post Share on other sites
Ingolme 1,027 Posted November 7, 2007 Report Share Posted November 7, 2007 I don't know, I'm just suggesting because I've never used subqueries, so I can't be sure exactly how they work. If performing the query before works ok then at least your script will be working.Maybe somebody else can find a different solution. Quote Link to post Share on other sites
Synook 47 Posted November 7, 2007 Report Share Posted November 7, 2007 Trying echoing mysql_error() after performing the query. That will tell you what is wrong. Quote Link to post Share on other sites
justsomeguy 1,135 Posted November 8, 2007 Report Share Posted November 8, 2007 You guys are funny, just trying stuff without looking up the syntax first. You don't use the equal sign with subqueries, you use IN.$sql1 = "SELECT * FROM db1.table1 WHERE field1 IN (SELECT post_id FROM db1.table2 WHERE field1=9795)";Also, make sure that the version of MySQL you're using supports subqueries. I believe they added that in version 4. If not, you can use a join instead.$sql1 = "SELECT db1.table1.* FROM db1.table1, db1.table2 WHERE db1.table1.field1=db1.table2.post_id AND db1.table2.field1=9795"; Quote Link to post Share on other sites
SpOrTsDuDe.Reese 1 Posted November 8, 2007 Report Share Posted November 8, 2007 Yet AGAIN. Justsomeguy has the correct answer. Quote Link to post Share on other sites
vchris 3 Posted November 9, 2007 Author Report Share Posted November 9, 2007 Works! Thank you. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.