appy — 2011-05-18T07:09:22-04:00 — #1
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.
guido2004 — 2011-05-18T08:54:16-04:00 — #2
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?
appy — 2011-05-18T13:26:38-04:00 — #3
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.
swampboogie — 2011-05-18T16:04:42-04:00 — #4
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