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
I hope I've explained it ok! Any help would be appreciated. Thx in advance.
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?
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.
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
where make_id = $make
or model_id = $model
or variant_id = $variant
or engine_id = $engine
or issueyear_id = $issueyear
order by matches desc