Mysql Nested Query Trouble

Question

I have created a mysql seect statement with a nested select statement. Now my mysql skills (or lack there of) are very limited. Below is the code that I wrote. I was getting blank results or the Warning. mysql_fetch_Array error. Now I am currently receiving an error that says "Subquery returns more than 1 row" Can anyone point me in the right direction on how I can begin to fix this problem. Thanks for the help.

 <?php
session_start();

$memberId = $_GET['id'];

$loggedId = $_SESSION['id'];

include('../connect_DB.php');

$sql = 'SELECT bins.tag_Id, tagging_Info.plant_Id, tagging_Info.photo_Id FROM bins inner join tagging_Info on bins.tag_Id = tagging_Info.tag_Id inner join collections on collections.id = bins.collection_Id WHERE collections.member_Id ='.$memberId.' and collections.id=(SELECT id FROM collections where member_Id='.$memberId.')'; 

$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)) {


   $collection = "Success"; // test to see if working

}
echo $collection;

?>

Have you tried executing just the subquery to see how many records are actually being returned and to ensure that $memberId is actually set? It would make sense for a user to have multiple collections so you should probably adjust your main query to use IN instead of = on the subquery results. — daemonofchaos
– daemonofchaos, Commented Oct 5, 2012 at 19:39
Your subquery is returning multiple rows. You either need to amend it to return just 1 row, or amend the main SELECT to use IN instead of = — andrewsi
– andrewsi, Commented Oct 5, 2012 at 19:39
KayakJim and Andrewsi thank you both for your input. Thats exactly what is was. I limited the subquery to return one result and it worked perfectly. Thank you so much to the both of you. I really appreciate it. — Ryan Beaulieu
– Ryan Beaulieu, Commented Oct 5, 2012 at 19:41
@KayakJim, Andrewsi: You should post answers as answers rather than comments. The question will then no longer show as unanswered (plus you get rep for upvotes, yay). — JYelton
– JYelton, Commented Oct 5, 2012 at 20:45
@JYelton, I didn't post as an answer because it was initially a question. I have copied my post as an answer for consideration of being selected. — daemonofchaos
– daemonofchaos, Commented Oct 7, 2012 at 21:12

daemonofchaos · Accepted Answer · 2012-10-07 21:12:03Z

2

Have you tried executing just the subquery to see how many records are actually being returned and to ensure that $memberId is actually set?

It would make sense for a user to have multiple collections so you should probably adjust your main query to use IN instead of = on the subquery results.

answered Oct 7, 2012 at 21:12

daemonofchaos

7955 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Mysql Nested Query Trouble

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related