Selecting the closest match from mysql table

Question

I have a database of clothing sizes, the user inputs their measurements and it returns a match, if there is no match it returns a list the closest matches to their users details.

What I want to do, is if there is no perfect match, show the match which is the closest to their measurements.

This is my query to get the matches.

 SELECT * FROM sizing where bust_cm like '$bust%' AND waist_cm like '$waist%' AND hips_cm like '$hips%'

however the best match could be the correct size on two but just one or two digits over on the third and so i am trying to find the best way to calculate which of the results matched the best.

question from:https://stackoverflow.com/questions/65843118/selecting-the-closest-match-from-mysql-table

Answer 1

Are the measurements in your table really strings?

If they are numeric values, you might want to look at doing it by finding rows with the minimum difference between the database values and your inputs.

SELECT * 
FROM sizing
ORDER BY (ABS(bust_cm - $bust) + ABS(waist_cm - $waist) + ABS(hips_cm - $hips)) ASC