SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Showing the last gallery?

    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
    Code MySQL:
    WHERE gallery_id = query1.gallery_id
    else show the gallery corresponding with the archive link
    Code MySQL:
    WHERE gallery_id = url.gallery
    It is working but I'm sure I could do this with just one query.
    The tables involved are:
    Code MySQL:
    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;
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    How about posting the queries?

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    How about posting the queries?
    These are the queries I use right now:
    Code MySQL:
    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>
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    How about:
    Code:
      <cfelse>
      WHERE
            GP.gallery_id = 
               (SELECT
                    MAX(gallery_id)
                FROM 
                    galleries
               )
      </cfif>


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •