single query instead of multiple to achieve the same

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

single query instead of multiple to achieve the same

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post by newmember »

say, if i have 20 galleries per page...how bad is it then to use what i wrote in example?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it's likely not too bad..
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post 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.
Post Reply