SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
-
Aug 7, 2005, 02:55 #1
- Join Date
- Apr 2004
- Location
- country
- Posts
- 82
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
single query instead of multiple to achieve the same
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:
Code: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: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:
PHP Code:"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:
PHP Code:$query="SELECT gallery_name,gallery_id FROM gallery LIMIT ".$start.",5"
$res=mysql_query($qr);
PHP Code: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?
-
Aug 7, 2005, 04:30 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
i'm not certain it's possible, but it would be something like this --
Code: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 )
Code: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 )
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
-
Aug 7, 2005, 04:43 #3
- Join Date
- Apr 2004
- Location
- country
- Posts
- 82
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Aug 7, 2005, 04:46 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
if you are not yet on 4.1, you should have said so
please see this thread
-
Aug 7, 2005, 04:48 #5
- Join Date
- Apr 2004
- Location
- country
- Posts
- 82
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
at my home i do have 4.1 but my host is still using 4.0
-
Aug 7, 2005, 05:04 #6
- Join Date
- Apr 2004
- Location
- country
- Posts
- 82
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Code:... 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 )
inside in() clause you have nothing refering to image_galleryId but you use it outside of in()...
is it correct?
-
Aug 7, 2005, 05:09 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Aug 7, 2005, 05:11 #8
- Join Date
- Apr 2004
- Location
- country
- Posts
- 82
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Aug 7, 2005, 05:14 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
20 gallery records means you are doing 20 queries inside a loop? yikes!
what do you mean, "one query with several subqueries"??
-
Aug 7, 2005, 05:24 #10
- Join Date
- Apr 2004
- Location
- country
- Posts
- 82
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
what do you mean, "one query with several subqueries"??
20 gallery records means you are doing 20 queries inside a loop? yikes!
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
-
Aug 7, 2005, 05:32 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
Aug 7, 2005, 06:32 #12
- Join Date
- Apr 2004
- Location
- country
- Posts
- 82
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
ok, finally i got you...instead me having to loop, you say, let mysql loop through SET() .
i should do something like this:
PHP Code:$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...
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
THANKS A LOT for the help and idea of thinking backwards...
-
Aug 7, 2005, 06:45 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
you're welcome
thinking backwards is a skill, eh
Bookmarks