Mysql – Find Shoe Size Selection

MySQL

I'm developing a feature that allows users to input their foot dimensions and have the proper shoe size returned to them. I'm having difficulty deciding on the best way to implement this feature.

The query I have below is what I've come up with. It's returning the two closest results with the difference in length and width. The difference returned could be positive or negative, though, negative of course being an issue because that would indicate that the returned size is too small for the user's dimensions.

The piece I'm looking for help on is how to exclude sizes that would be smaller than the user input.

Query

SELECT
    size_name,
    length,
    ABS(length - ' . $input->length . ') AS length_difference,
    width,
    ABS(width - ' . $input->width . ') AS width_difference
FROM
    SIZES
LIMIT
    2
ORDER BY
    length_difference

Results

{
    "size_name": "8",
    "length": 10.3,
    "length_difference": 0.15,
    "width": 3.8,
    "width_difference": 0.4
},
{
    "size_name": "9",
    "length": 10.6,
    "length_difference": 0.15,
    "width": 3.9,
    "width_difference": 0.3
}

UPDATE

I've been tweaking and realized this doesn't have to be as hard as I was making it. In the screenshot below I have something close. I'm using PHP so I'm thinking I can just filter out the negative values and from there I clearly have size 9. Does this seem like an effective solution?

enter image description here

Best Answer

So it seems like you're missing the where clause which would filter out the undesirable results. In this example I'm assuming 2 is a decent threshold and using a between clause to limit the output. Greater than with a limit would also work but it really depends on the dataset. 

SELECT
    size_name,
    length,
    ABS(length - ' . $input->length . ') AS length_difference,
    width,
    ABS(width - ' . $input->width . ') AS width_difference
FROM
    SIZES
WHERE 
    length BETWEEN $input->length AND ($input->length + 2)
    AND width BETWEEN $input->width AND ($input->width + 2)

ORDER BY
    length_difference
LIMIT 2

Here's the example using greater than.

SELECT
    size_name,
    length,
    ABS(length - ' . $input->length . ') AS length_difference,
    width,
    ABS(width - ' . $input->width . ') AS width_difference
FROM
    SIZES
WHERE 
    length > $input->length
    AND width > $input->width

ORDER BY
    length_difference
LIMIT 2
Related Question