I have a page with image galleries. By default the last added gallery should be the one visible the other galleries are displayed in the form of an archive list which is working fine. Right now I use two queries! (query1 & query2) One to grab the id of the last added gallery. The second query is to actually display the images. Within this last query, in the where clause, I decide which gallery to show using if/else: If no id is passed in the url(this would happen when a archive link was used) show the last gallery
WHERE gallery_id = query1.gallery_id
else show the gallery corresponding with the archive link
WHERE gallery_id = url.gallery
It is working but I’m sure I could do this with just one query.
The tables involved are:
CREATE TABLE IF NOT EXISTS `galleries` (
`gallery_id` smallint(2) unsigned NOT NULL auto_increment,
`gallery_name` varchar(128) default NULL,
`gallery_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`gallery_id`),
KEY `gallery_name` (`gallery_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `gallery_photos` (
`photo_id` smallint(2) unsigned NOT NULL auto_increment,
`gallery_id` smallint(2) unsigned NOT NULL,
`photo` varchar(128) default NULL,
`description` varchar(255) default NULL,
PRIMARY KEY (`photo_id`),
FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`gallery_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;