SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast appy's Avatar
    Join Date
    Feb 2002
    Location
    UK
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Query - Order by best matches

    Hi All,

    I'm certainly no mySQL guru so i'm looking for some assistance with a query. The query below brings back 1 exact match fine but the data in the table is not fully complete. What I want to do is return the all results in order of 'all 5 match', '4 match', '3 match', '2 match', '1 match'.

    SELECT * FROM master WHERE
    make_id=$make AND
    model_id=$model AND
    variant_id=$variant AND
    engine_id=$engine AND
    issueyear_id=$issueyear
    LIMIT 1"

    I hope I've explained it ok! Any help would be appreciated. Thx in advance.

    Pete

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    To get all results, get rid of the LIMIT

    To order them based on the number of matching columns is a bit trickier I think. No time to think about it now, maybe someone else knows the solution?

  3. #3
    SitePoint Enthusiast appy's Avatar
    Join Date
    Feb 2002
    Location
    UK
    Posts
    99
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the advice, I've gone down the multiple queries and PHP route for know but will look around to see if i can get it down to the 1 query.

    Pete

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select <columnsOfInterest>,
           case when make_id = $make then 1 else 0 end + 
           case when model_id = $model then 1 else 0 end + 
           case when variant_id = $variant then 1 else 0 end + 
           case when engine_id = $engine then 1 else 0 end + 
           case when issueyear_id = $issueyear then 1 else 0 end as matches 
      from master 
     where make_id = $make 
        or model_id = $model
        or variant_id = $variant 
        or engine_id = $engine
        or issueyear_id = $issueyear
     order by matches desc


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
  •