assistance creating a SQL Query

3 comments

1. Let me know how / if this works. If you dump both of those tables (out of phpMyAdmin) in a SQL format I will create local databases and give it a run myself (assuming this doesn’t work).

   SELECT
       `wp_ngg_gallery`.`name`,
       `wp_ngg_pictures`.`filename`
   FROM
       `wp_ngg_gallery`
       LEFT JOIN
           `wp_ngg_pictures`
       ON
           `wp_ngg_gallery`.`gid` = `wp_ngg_pictures`.`galleryid`
   ORDER BY
       `wp_ngg_pictures`.`imagedate` DESC
   LIMIT
       0,1;
   

   This is assuming you need a JOIN. It could probably be done with a decent GROUP BY statement.

   Log in to Reply

2. I’m going to guess:

   select wp_ngg_gallery.name, wp_ngg_pictures.filename
   from wp_ngg_gallery, wp_ngg_pictures
   where wp_ngg_pictures.galleryid = wp_ngg_gallery.gid
   order by wp_ngg_pictures.imagedate DESC 
   limit 0,1
   

   If there’s a specific GID you want, you could do:

   select wp_ngg_gallery.name, wp_ngg_pictures.filename
   from wp_ngg_gallery, wp_ngg_pictures
   where wp_ngg_pictures.galleryid = wp_ngg_gallery.gid
   and wp_ngg_gallery.gid = $gid
   order by wp_ngg_pictures.imagedate DESC 
   limit 0,1
   

   (The $gid is valid if you’re query is a string in PHP, you didn’t say, though, if that’s what you’re using)

   It’s not clear if wp_ngg_pictures.galleryid is a foreign key to wp_ngg_gallery.gid but it’s the most likely choice given the info you supplied. If it’s not (if you can have galleries with no images), you might want to modify the other guy’s query (using the outer join) by adding and wp_ngg_gallery.gid = $gid to it.

   Log in to Reply

3. If you want it for multiple gids (choose the other options for a single one):

   SELECT gid,name, a.filename
   FROM wp_ngg_gallery
   JOIN  wp_ngg_pictures a
   ON a.galleryid = gid 
   LEFT JOIN wp_ngg_pictures b
   ON a.galleryid = gid 
   AND b.imagedate > a.imagedate
   WHERE b.galleryid IS NULL
   AND gid IN (1,2,3)
   

   Log in to Reply