Showing the last gallery?

Databases
1

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;
2

How about posting the queries? :slight_smile:

3

These are the queries I use right now:


Query1
	SELECT
  		gallery_id
  FROM 
  		galleries
	ORDER 
  	BY	gallery_id DESC

Query2
	SELECT
      	G.gallery_name
      ,	G.gallery_date  
  		,	GP.photo_id
			,	GP.gallery_id
      ,	GP.photo
      , GP.description

  FROM
  			galleries G
  INNER
  	JOIN 	gallery_photos GP
    	ON	G.gallery_id = GP.gallery_id	
  <cfif structKeyExists( Url, 'gallery' )>
  WHERE
  			GP.gallery_id = #Url.gallery#
  <cfelse>
  WHERE
  			GP.gallery_id = #getLastGallery.gallery_id#  
  </cfif>
4

How about:


  <cfelse>
  WHERE
        GP.gallery_id = 
           (SELECT
                MAX(gallery_id)
            FROM 
                galleries
           )
  </cfif>