Nested Queries

[rushko99]

Hi there, I am trying to get images uploaded from a gallery to be organized under the author's id (displaying their name from the users table).

 

I don't know if there are supposed to be different results (result1 result2 result3) either.... Thank you!

 

Here is what the requirements are:

 

 

 

$result1 = mysql_query("SELECT DISTINCT author_id FROM mugallery");

while ($row = mysql_fetch_array($result1)){

// nested query 1 { }

// nested query 2 { }

} // end outside query

 

 

 

 

Inside this outside query, we will have 2 nested queries:

 

1. User data: One that uses the author_id from the mugallery table, and matches it to the user_id field of the users table. We can then retrieve first and last name of the author.

• While we have the author name, create a <div> to contain them and echo out the name as a heading of sorts.

 

2. Gallery data: Another that retrieves all thumbnails from the gallery, but only WHERE the author_id matches the particular author in the current iteration of the loop from the outside query.

 

 

Here is my code thus far:

 

$result1 = mysql_query("SELECT DISTINCT author_id FROM mugallery");

while ($row = mysql_fetch_array($result1)){

 

$result2 = mysql_query("SELECT * FROM mugallery JOIN users ON mubdata.author_id = users.user_id ");

while ($row = mysql_fetch_array($result2)){

 

 

$title = $row['title'] ;

$description = $row['description'];

$filename = $row['filename'];

$imageid = $row['imageid'];

$author_id = $row['author_id'];

//echo $title . " | " .$description . " | " .$filename . " | " .$imageid;

 

echo $author_id;

 

}// end nested query 1

 

 

$result3 = mysql_query("SELECT * FROM mugallery WHERE author_id = !!!!! What to put here??!!!!!!!! ");

while ($row = mysql_fetch_array($result3)){

$thisThumbHeader = " \n<div class=\"thumb\">";

$thisTitleToDisplay = "\n<div class=\"title\">$title</div>";

$titledescription = $title . ": " . $description;

$thisImageCodePre = "<a href=\"display.php?imageid=$imageid\">";

$thisImageCode ="<img src=\"uploads/thumbs120/$filename\" /></a>";

$thisImageToDisplay = "\n<div class=\"image\">$thisImageCodePre$thisImageCode</div>";

$thisThumbFooter = " \n</div>";

 

$thisThumbToDisplay = $thisThumbHeader . $thisTitleToDisplay .$thisImageToDisplay.$thisDescriptionToDisplay .$thisThumbFooter ;

echo $thisThumbToDisplay ;

 

 

}// end nested query 2

 

 

} // end outside query

[Kingy]

First of all I would stop using $row over and over as you will end up overwriting the previous results data.

 

$row = "Number 1" 
$row = "Number 2" // Number 1 no longer exists

 

So instead use things like $authorRow, $userRow, $galleryRow. This should solve your problem because in the last query you can use $authorRow['author_id'].

[albertdiones]

From what I understood from the post and the code:

 

You want to query all author id from mugallery table, and for each author id repetitively query the joined mugallery and user table (I assume to get the first name and last name) then output the gallery thumbnail.

 

I suggest having a simpler query, that is:

 

<?php
$query = mysql_query(
"
SElECT * FROM
users INNER JOIN mugallery
ON
users.user_id = mugallery.author_id
ORDER BY user_id"
);

while ($row = mysql_fetch_array($query)){
echo $thisThumbToDisplay ;
echo <<<EOT
<div class="thumb">
<div class="title"> $row[title]</div>
<div class="image">
<a href="display.php?imageid=$row[imageid]">
<img src="uploads/thumbs120/$row[filename]"/>
</a>
</div>
</div>
EOT;
}

(for some reason I can't make the capital l on this keyboard. lol)

Edited February 4, 2013 by albertdiones