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

Dave Stevens evolt at davestevens.co.uk
Tue Apr 15 16:49:42 CDT 2008

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?


More information about the thelist mailing list