[SOLVED] Query within a while loop.

[poleposters]

Hi,

 

I have a page with links to different photo albums.The name and link to the albums are drawn from the database using a while loop.

 

I want to display the first picture from each album as a graphic link.

 

I have two tables in my database. Photos and Albums.The Album table has two fields, album_id and album(ie the name of the album). The photos has phot_id and photo_url

 

This is the code I've attempted, but it displays the same picture for each album. Can someone point out what I'm doing wrong?

$query="SELECT * FROM albums";
$result=mysql_query($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
$i=0;
while($albums=mysql_fetch_array($result))
{
$name=$albums['album'];
$query2="SELECT * FROM photos WHERE album='$name' LIMIT 0,1";
$result2=mysql_query($query2) or trigger_error("Query: $query2\n<br />MySQL Error: " . mysql_error());
		if(mysql_num_rows($result2)>0)
		{$cover2=mysql_fetch_array($result2);
		$cover=$cover2['photo_url'];
		}


	print "<div class=\"album_cover_right\">";
	print "<div class=\"album_image\"><img src=\"$cover\"/></div>";
	print "<div class=\"album_link\"><a href=\"http://localhost/gallery/album/$name/\" class=\"list\">$name</a></div>";
}

 

[Mark Baker]

Save yourself a lot of hassle (and improve performance) by executing a single query that joins the albums and photos tables

[poleposters]

Thank you.

 

I tried using a LEFT JOIN however it printed The albums as many times as there were pictures contained in them.

 

I'm having trouble understanding Joins, I thought my query within a while loop would steer around that.

 

Is there a join which would allow me to print each album only once and only one photo from each?

[jcrew]

Nevermind. The script I posted only works for one piece of data; not multiple.

 

 

 

[poleposters]

The more I read about joins, the less I think that they're the solution to the problem.

 

Does anyone have any ideas? I'm stumped.

[taquitosensei]

use left join, build an array then work from that

   $query="SELECT * FROM albums LEFT JOIN photos on albums.album=photos.album";
   $result=mysql_query($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());
   $i=0;
   while($albums=mysql_fetch_array($result))
   {
    $array[$albums['album']][]=$albums['photo_url']; 
    }
         foreach($array as $name=>$photos) 
         {
      
                print "<div class=\"album_cover_right\">";
                foreach($photos as $cover) {
                print "<div class=\"album_image\"><img src=\"$cover\"/></div>";
                }
                print "<div class=\"album_link\"><a href=\"http://localhost/gallery/album/$name/\" class=\"list\">$name</a></div>";
}

this isn't checked for any typos

[poleposters]

Thank you for your effort.

 

One of each album displays as necessary, however as many photos as the albums contained are printed alongside.

 

Is it possible to limit the number of photos retrieved?

 

Just to be clear. I need to display the albums and one photo from the album as the cover.

[chronister]

This is not a direct response to your issue... but may be of some help.

 

I had issues working with joins and could not get the data I needed properly. I started to do my joins "manually" like this....

SELECT table1.field1, table1.field2, table2.field1 FROM table1, table2 WHERE table1.field1 = table2.field1 && table1.field2 = '$someVariable'

 

I am sure some folks with hoop and holler that it is easier to do a left/right/union join... rather than doing it this way, but from what I read, the joins are translated into this type of query internally.

 

hope this helps some.

 

Nate

 

 

[Psycho]

Here is all you need

SELECT a.album, p.photo_url
FROM albums a
LEFT JOIN photos p ON a.album = p.album
GROUP BY a.album

 

The JOIN will get you a record for each album/image combination. But, by using a GROUP BY you 'collapse' the records down to one row for each albim and only the first image will be associated with it. If you needd a particular image associated with each album you can use an ORDER BY on something like the photo name. Or you could have a column in the photo table to identify the photo to be used for the album photo. Then just use a where clause. As long as you have a one to one association you wouldn't need a GROUP BY.

[poleposters]

Brilliant. And such a simple solution. It seemed too common a task to have a complicated answer.

 

Thank you all for your help.