single query instead of multiple to achieve the same

[newmember]

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:

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:

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:

"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:

$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:

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?

[r937]

i'm not certain it's possible, but it would be something like this --

select gallery_name
     , image_name 
  from gallery
inner
  join image 
    on gallery_id
     = image_galleryId 
   and image_galleryId in
       ( 
       select gallery_id 
         from gallery as X
        where ( 
              select count(*) 
                from gallery
               where gallery_id > X.gallery_id ) 
           between ".$start." and ".$start." + 5 
       )

i do know that if you try this --

select gallery_name
     , image_name 
  from gallery
inner
  join image 
    on gallery_id
     = image_galleryId 
   and image_galleryId in
      ( SELECT gallery_id 
          FROM gallery 
        order by gallery_id limit ".$start.", 5 )

then you will get the following error message in 4.1 --

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

[newmember]

but you using subquery... it is not supported yet on most hosts

if it is not possible i guess i would proceed as i did in example....
thanks

[r937]

if you are not yet on 4.1, you should have said so

please see this thread

[newmember]

at my home i do have 4.1 but my host is still using 4.0

[newmember]

...
image_galleryId in
( 
       select gallery_id 
         from gallery as X
        where ( 
              select count(*) 
                from gallery
               where gallery_id > X.gallery_id ) 
           between ".$start." and ".$start." + 5 
)

probably i don't understand the query but something seems not right...
inside in() clause you have nothing refering to image_galleryId but you use it outside of in()...
is it correct?

[r937]

inside the IN() subquery, i am retrieving gallery_IDs of the galleries, where these galleries are selected based on a range using $start, instead of a LIMIT

outside the IN() subquery, i select images and galleries based on those gallery_IDs

[newmember]

considering that i only need to retrive 20 gallery records per time, what is more efficient/faster in this case, to do one query with several subqueries or do what i did in example ?

meanwhile i'll study your example

[r937]

20 gallery records means you are doing 20 queries inside a loop? yikes!

what do you mean, "one query with several subqueries"??

[newmember]

what do you mean, "one query with several subqueries"??

your query contain several subqueries.

20 gallery records means you are doing 20 queries inside a loop? yikes!

but there is no other options other that making my host to upgrade db...
and i read that sometimes making several small queries is faster, because can be optimized, than making one comlex one... so i thought that maybe in my case it might be true

[r937]

well, yes, but you really only need 2 queries, and the 2nd one is not in a loop

first query gets gallery_IDs, second query gets all images for those galleries

[newmember]

ok, finally i got you...instead me having to loop, you say, let mysql loop through SET() .
i should do something like this:

$query="SELECT gallery_id FROM gallery LIMIT ".$start.",".$numOfRecs;
$res=mysql_query($query);
// this selects galleries in the desired range...

$ids=array();
while($row=mysql_fetch_assoc($res))
{
     $ids[]=$row['gallery_id'];
}
$set='('.implode(','.$ids).')';

//and finally get all images which associated in galleries in  $set
"SELECT image_name FROM image WHERE image_galleryId in ".$set.";
....
//and the rest... 

also mysql manul says:

If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants

so these two queries surely outperfom 20 separate queries

THANKS A LOT for the help and idea of thinking backwards...

[r937]

you're welcome

thinking backwards is a skill, eh