single query instead of multiple to achieve the same
Posted: Sun Aug 07, 2005 3:25 am
Hi
I hope i'll manage to explain what i'm trying to do
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:
i would like to display this as:
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:
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:
and then i would loop over retrieved gallery records with query like:
(i hope i was clear...)
Is it possible to achieve the same with single db query?
I hope i'll manage to explain what i'm trying to do
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 treei would like to display this as:
Code: Select all
gallery_name image_name
--------------------------------------
Storms waves
Storms fisherman
Storms offshore
Sea wind
Sea fallen treethe 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 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);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']);
}Is it possible to achieve the same with single db query?