MySQL retrieve results based on matching percentage or limit -

MySQL retrieve results based on matching percentage or limit -

i working on project people can search on dimensions.

we have next table structure:

product table: width length height

people can search db giving illustration next dimensions:

300x200x250

what want sort result based on best match. exact dimensions found in db have 100% match. not want find results 100% match illustration 310x200x250 or 300x220x260 fine, except match percentage lower.

we want allow results match of 90% or more or limit of 50 results.

the basic query of course of study simpel:

select * products length >= 300 , height >= 200 , height >= 100 and(formule calculate match higher 90% or max of 50 results.

any on how can give me force in right direction how solve percentage part , max limit part.

kind regards,

erwin

you can seek this

class="lang-sql prettyprint-override">select *, ceil((300/ width * 0.333 + 200/ length * 0.333 + 250 / height * 0.333) * 100) pcnt products width >= 300 , length >= 200 , height >= 250 having pcnt > 90 order pcnt desc limit 50

output:

| id | width | length | height | pcnt | |----|-------|--------|--------|------| | 1 | 300 | 200 | 250 | 100 | | 2 | 310 | 200 | 250 | 99 | | 3 | 300 | 220 | 260 | 96 |

here sqlfiddle demo

mysql limit percentage