LIKE? Query Help

phousley

I'm working on a project that I just can't seem to wrap my head around the query structure for... It seems simple, please assist or send me in the right direction.

A table exisits with stat information let's say these are the fields:
ID
Name
HairColour
EyeColour
Weight
Height
Age

What I want is to provide the query with description information (haircolour, eyecolour, weight, height, age). Then have it return the top most closely matched people in the table in order. I'm thinking the LIKE function might do some of this but do I use LIK over and over in the query? Please help!

Thanks!

Frederick

I would use LIKE for the haircolor and eye color, but for weight and height, I would use a number match such as:

$sql = mysql_query("SELECT FROM tablename 
WHERE weight BETWEEN ($weight-5) AND ($weight+5) 
AND age BETWEEN ($age-2) AND ($age+2)
") or die (mysql_error());

Roger_Ramjet

I don't see how LIKE is going to do much for you, except when it comes to a partial name match. Auburn may be a type of brown hair colour in the real world, but sql is not going to match it. You are after a means of wieghting people so that you can order them. I'm not familiar with the stored procedure and user defined function parts of mysql, but that is what it one would use in SQLServer, Access, or Oracle. In php/mysql I would use a script that reads in all records and processes them something like this:

For each field that matches you add a score, then subtract the age differance between target and subject. Those with the highest score have the closest match. You could vary the value scored for different field matches according to their priority or importance. Likewise with body wieght, subtract a value for how much they differ.

To do part of it in sql, you would need a query that count the occurances in a subquery. The subquery would be a UNION query of queries that match one field each.

SELECT count(id), id
(SELECT id WHERE haircolour=$hair
UNION
SELECT id WHERE eyecolour=$eyes
UNION
SELECT id WHERE name LIKE $name
etc

I hope you get the idea.