SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: Query

  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query

    Hi,

    I have a query that I need to add a ORDER BY statement to..

    I am trying to make it grab a spacific image to display when loading aposed to the last image added.

    I have added a field called "showcase" in the car images table and am wanting showcase filed marked as "1" to be the default photo.

    Code:

    Code:
    	$sql = "SELECT c.carid
         , c.year
         , c.manufacturerid
         , c.model
         , c.mileage
         , c.price
         , c.description
         , c.availability
         , p.photofilename
         , m.make
      FROM cars as c
    INNER
      JOIN manufacturer as m 
        ON m.manufacturerid = c.manufacturerid		
    LEFT OUTER
      JOIN ( SELECT carid
                  , MAX(photoid) AS latest
               FROM carimages 
             GROUP
                 BY carid ) AS x
        ON x.carid = c.carid
    LEFT OUTER
      JOIN carimages as p 
        ON p.carid = c.carid
       AND p.photoid = x.latest
    ".$where."
    ORDER 
        BY c.carid DESC
    LIMIT $from
         , $max_results";
    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    that's astonishing... it looks ~exactly~ like something i might have written

    Code:
    SELECT c.carid
         , c.year
         , c.manufacturerid
         , c.model
         , c.mileage
         , c.price
         , c.description
         , c.availability
         , p.photofilename
         , m.make
      FROM cars as c
    INNER
      JOIN manufacturer as m 
        ON m.manufacturerid = c.manufacturerid		
    LEFT OUTER
      JOIN carimages as p 
        ON p.carid = c.carid
       AND p.showcase = 1
    ".$where."
    ORDER 
        BY c.carid DESC
     LIMIT $from
         , $max_results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Will a car with photos ALWAYS have a showcase photo?

    If not, how will the photo to be used as the "main image" be determined?

    Pending your answer the sub-query may be unnecessary with the addition of the showcase flag.

  4. #4
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You would be right.. You did put that query together for me some time ago now..

    Thanks!

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That works.. Except there may not be a show case marked image for every record.. So the ones marked as "0" are showing my place holder.

    Cheers

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Off Topic:

    Quote Originally Posted by _matrix_ View Post
    You did put that query together for me some time ago now..
    yeah, i searched for it, but couldn't find it

    was it here on sitepoint?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by _matrix_ View Post
    So the ones marked as "0" are showing my place holder.
    not possible, the query will return ~only~ showcase=1 images
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://www.sitepoint.com/forums/showthread.php?t=614395

    It was posted under the Database forum not MYSQL..


    Cheers

  9. #9
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm, I cant guarantee that every record will have a showcase image set

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    aha, thanks very much

    Quote Originally Posted by _matrix_ View Post
    Hmm, I cant guarantee that every record will have a showcase image set
    so you're looking for the latest image, unless there's a showcase image, in which case show that one?

    hmm... this is gonna get a bit messier...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    More or less.. But it doesnt have to be the latest image added.. A random image will be fine if it helps to simplify the query.

    Cheers

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by _matrix_ View Post
    A random image will be fine if it helps to simplify the query.
    let's stick with latest as the default... random is even messier!!
    Code:
    SELECT c.carid
         , c.year
         , c.manufacturerid
         , c.model
         , c.mileage
         , c.price
         , c.description
         , c.availability
         , COALESCE( p2.photofilename
                   ,  p.photofilename ) AS photofilename
         , m.make
      FROM cars as c
    INNER
      JOIN manufacturer as m 
        ON m.manufacturerid = c.manufacturerid		
    LEFT OUTER
      JOIN ( SELECT carid
                  , MAX(photoid) AS latest
               FROM carimages 
             GROUP
                 BY carid ) AS x
        ON x.carid = c.carid
    LEFT OUTER
      JOIN carimages as p 
        ON p.carid = c.carid
       AND p.photoid = x.latest
    LEFT OUTER
      JOIN carimages as p2 
        ON p2.carid = c.carid
       AND p2.showcase = 1
    ".$where."
    ORDER 
        BY c.carid DESC
    LIMIT $from
         , $max_results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That apears to work great.. Way over my head, I will have to study that to see how its working.

    Thanks alot! for your help


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
  •