CODEWITHSUNDEEP
phpmysql
Luke Bateson
Luke Bateson

Reputation: 69

MySQL/PHP - Return Closest Numerical Match

I want to make an order form where user can choose a clothing size. I have all the available sizes stored in a database, as code, size and category.

size is formatted like 66/68/76 (Waist/Hips/Leg Length). User is able to input these three values. If user's size is available - there's no problem. But if it's not I want the site to offer or change it to nearest available size. For example if user entered 65/66/74 and exact value doesn't exist (or unavailable right now) it will be changed to 66/65/74.

Upvotes: 0

Views: 46

Answers (2)

santaka
santaka

Reputation: 193

One way whould be to store 3 values as an integer 666876 (66/68/76).

Then, you can do a query of the minimun value of the substraction of productSize - userSize, that is greater than 0.

This approach takes always the clothing higher size.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271161

You need to define what you mean by "closest". Along the way, you should also store the three values in three different columns. Storing multiple values in a single column is a bad idea.

Sometimes, one is stuck with a particular data format because of someone else's poor design decisions.

One perhaps reasonable measure is Euclidean distance -- the sum of the squares of each component. You can calculate this in MySQL:

select t.*
from (select t.*,
             substring_index(size, '/', 1) as waist,
             substring_index(substring_index(size, '/', 2), '/', -1) as hips,
             substring_index(size, '/', -1) as legs
      from t
     ) t
order by pow(waist - $waist, 2) + pow(hips - $hips, 2) + pow(legs - $legs, 2)
limit 1;

Upvotes: 1

Related Questions