Query

Databases
1

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:


	$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

that’s astonishing… it looks ~exactly~ like something i might have written :slight_smile:

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
   [COLOR="Blue"]AND p.showcase = 1[/COLOR]
".$where."
ORDER 
    BY c.carid DESC
 LIMIT $from
     , $max_results
3

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

You would be right… You did put that query together for me some time ago now…

Thanks!

5

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

[ot]

yeah, i searched for it, but couldn’t find it

was it here on sitepoint?[/ot]

7

not possible, the query will return ~only~ showcase=1 images

8

It was posted under the Database forum not MYSQL…

Cheers

9

Hmm, I cant guarantee that every record will have a showcase image set

10

aha, thanks very much :slight_smile:

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…

11

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

let’s stick with latest as the default… random is even messier!!

SELECT c.carid
     , c.year
     , c.manufacturerid
     , c.model
     , c.mileage
     , c.price
     , c.description
     , c.availability
     , COALESCE( [COLOR="Red"]p2.photofilename[/COLOR]
               ,  [COLOR="Blue"]p.photofilename[/COLOR] ) AS photofilename
     , m.make
  FROM cars as c
INNER
  JOIN manufacturer as m 
    ON m.manufacturerid = c.manufacturerid		
[COLOR="blue"]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[/COLOR]
[COLOR="Red"]LEFT OUTER
  JOIN carimages as p2 
    ON p2.carid = c.carid
   AND p2.showcase = 1[/COLOR]
".$where."
ORDER 
    BY c.carid DESC
LIMIT $from
     , $max_results
13

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