Efficient query for gallery front page

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
jolinar
Forum Commoner
Posts: 61
Joined: Tue May 24, 2005 4:24 pm
Location: in front of computer

Efficient query for gallery front page

Post by jolinar »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Sorry the title isn't very descriptive, but it's a strange problem I'm trying to solve which may take a while to explain.

I'm trying to build a gallery, and on the front page I need to build a list of categories and albums.  The idea is that each album will have it's name, description and a sample image (the most recent added)

These albums are then grouped by category.  The problem is that I need to get data from 3 different tables, and ideally I'd like to do it all in the same query.

This is the query I used for getting album data and grouping it by cat:
[syntax="sql"]SELECT * FROM gallery_category INNER JOIN gallery_album ON(category_id=album_category) ORDER BY category_id;
Then I decided that I needed preview images, this is where I became very confused. The only way I know to link this to the photo urls is through an inner join, but this produces a large amount of redundant data.

Code: Select all

SELECT * FROM (gallery_category INNER JOIN gallery_album ON(category_id=album_category)) INNER JOIN gallery_photo USING(album_id) ORDER BY category_id;
With this being the worst query imaginable (Since it produces rows for each photo, with all the album/category information associated with it)

Does anyone have any ideas on how to solve this?


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

Does the gallery_photo table have a date field that indicates when the photo was added?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Have you tried a GROUP BY clause for "album_id"? You will probably need to add another clause to the ORDER BY as well, either using the date field (if one exists) or potentially the id numbers of the photos.
User avatar
jolinar
Forum Commoner
Posts: 61
Joined: Tue May 24, 2005 4:24 pm
Location: in front of computer

Post by jolinar »

Thanks feyd, the GROUP BY clause fixed it :D (I guess I'm still a numpty when it comes to SQL)
Post Reply