[thelist] Search closest match across multiple columns (mysql)

[Ken Schaefer]

What is the algorithm for determining the "closest" match?

Cheers
Ken

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org [mailto:thelist-
> bounces at lists.evolt.org] On Behalf Of Dave Stevens
> Sent: Tuesday, 15 April 2008 2:50 PM
> To: thelist at lists.evolt.org
> Subject: [thelist] Search closest match across multiple columns (mysql)
>
> Hi list,
>
> I have a database table which contains basic biographic data about
> various people (height, weight, date of birth). I then have a form that
> allows a user to input a specific value for each of these pieces of
> data
> and need to return the closest matching people in the table, with
> closest match first.
>
> So, for example, assuming my table contains this data:
>
> name                 height         weight         dateofbirth
> Joe Bloggs        60                50                1980-05-02
> John Smith        70                65                1972-01-17
> Jane Doe          45                20                 1987-12-14
>
> If my user entered:
>
> height: 50
> weight: 35
> date of birth: 1981-11-01
>
> My application would return Jane Doe first, then Joe Bloggs and finally
> John Smith.
>
> I'm using MySQL as my database and while I approached this problem
> confidently, I've not had any luck in solving it adequately.
>
> Can anyone offer any pointers?
>
> Thanks,
> Dave