Page 1 of 1

single query instead of multiple to achieve the same

Posted: Sun Aug 07, 2005 3:25 am
by newmember
Hi

I hope i'll manage to explain what i'm trying to do :o

i'm having two related tables.
first table holds galeries and second table holds images.
So multiple images in second table are associated with single galery in first table (one to many relation)

I want to display N galleries per page and their images simulteniously.

for example:

Code: Select all

galeries table:


row #      gallery_id                  gallery_name            
-------------------------------------------------------- 
  1                  2                          Storms
  2                  3                             Sea




images table:

row #          image_galleryId                image_id            image_name
-----------------------------------------------------------------------------
  1                     3                             1                    waves
  2                     3                             2                  fisherman
  3                     2                             3                     wind
  4                     3                             4                    offshore
  5                     2                             5                fallen tree

i would like to display this as:

Code: Select all

gallery_name          image_name
--------------------------------------
    Storms                     waves
    Storms                   fisherman
    Storms                     offshore
    Sea                           wind
    Sea                         fallen tree

the problem is that i want to display say 5 gallery records per page and i don't know ahead how many images there are in each gallery...
so if i use query like:

Code: Select all

"SELECT gallery_name,image_name FROM gallery,image
  WHERE gallery_id=image_galleryId LIMIT ".$start.",5"
i get 5 records but the actual number of distinct galleries records are less than 5(it is equal to 5 only when each gallery contains exactly one image).
I realize that i could achieve that by 2 queries:
first one:

Code: Select all

$query="SELECT gallery_name,gallery_id FROM gallery LIMIT ".$start.",5"
$res=mysql_query($qr);
and then i would loop over retrieved gallery records with query like:

Code: Select all

while($row=mysql_fetch_assoc($res))
{
     $query="SELECT image_name FROM image WHERE image_galleryId='".$row['gallery_id']."'";
    $res1=mysql_query($query);
// and then inner loop to print the data
    while($row1=mysql_fetch_assoc($res1))
         echo($row['gallery_name'].'               '.$row1['image_name']);
}
(i hope i was clear...)

Is it possible to achieve the same with single db query?

Posted: Sun Aug 07, 2005 7:14 am
by feyd
I don't see a way of doing it off the top of my head.. unless you have subquery support, I don't think it's possible.

Posted: Sun Aug 07, 2005 7:29 am
by newmember
say, if i have 20 galleries per page...how bad is it then to use what i wrote in example?

Posted: Sun Aug 07, 2005 7:44 am
by feyd
it's likely not too bad..

Posted: Sun Aug 07, 2005 11:58 am
by pilau
First of all instead of leading your displaying the galeries with galleries, lead it with Images.
Go over the entire Images table, and display the Images saying which gallery they belong to.