multiple search inputs into 1 seach input

[SilverX]

Hi

I'm trying to merge a multiple record/field search into one input box (MySQL). Like a basic search engine.

The method I use at the moment works fine, only for each field I'm using separate input boxes, which I don't want. My db is quite complex but I'll give a simplified example:

Record 1   	      Record 2      	   Record 3
Eyes: Blue Green      Eyes: Blue    	   Eyes: Blue Green
Hair: Red  	      Hair: Brown   	   Hair: Blonde
Name: Alan Davis      Name: Alan Smith     Name: Bob

Search Eyes: [blue]
Search Hair: [ ]
Search Name: [alan]

(Returns Record 1 and Record 2)

What I want is:

Search: [blue alan]

(Returns Record 1 and Record 2)

OR

Search: [blue green red]

(Returns Record 1)

Does that make sense?

Code I use at the moment is like:

mysql_query ("SELECT * FROM $table WHERE eyes LIKE '%$eyes%' AND hair LIKE '%$hair%' AND name LIKE '%$name%'")

I presume I need to explode the new single search $ - but I'm not sure what to do with it next.

Could someone write me an example code? (I'm a newbie so idiotproof would be good!)

Help greatly appreciated.

[nemonoman]

Since you are self-declared newbie, what I'm about to say might not make sense at first glance. But if you try to read and understand it, you'll be moving forward from newbiehood into good design.

What you want is 2 tables.

Table 1 would be something like "BaseRecord"

Table 2 would be something like "RecordAttribute"

Each BaseRecord would have one more associated Record Attribute

Layout is pretty simple:

BaseRecord

id (integer(5), auto_increment)

That's all that's needed for the scheme to work. BUT you might add more fields:

BaseRecord

id (integer(5), auto_increment)
name(varchar(200))

etc

RecordAttribute

id (integer(6), auto_increment)
BaseRecordId (foreign Key)
AttributeType (varchar(200))
AttributeValue (varchar(200))

HEre's how it works

Create base record 1

INSERT INTO BaseRecord SET id=1, name='Alan Davis'

Now create the RecordAttribute data:

INSERT INTO RecordAttribute
SET id=NULL,
BaseRecordId=1,
AttributeType='Eyes',
AttributeValue='Blue';

INSERT INTO RecordAttribute
SET id=NULL,
BaseRecordId=1,
AttributeType='Eyes',
AttributeValue='Green';

INSERT INTO RecordAttribute
SET id=NULL,
BaseRecordId=1,
AttributeType='Hair';
AttributeValue='Red';

INSERT INTO RecordAttribute
SET id=NULL,
BaseRecordId=1,
AttributeType='Name';
AttributeValue='Alan';

INSERT INTO RecordAttribute
SET id=NULL,
BaseRecordId=1,
AttributeType='Name';
AttributeValue='Davis';

INSERT INTO BaseRecord SET id=2, name='Alan Smith'

Now create the RecordAttribute data:

INSERT INTO RecordAttribute
SET id=NULL,
BaseRecordId=2,
AttributeType='Eyes',
AttributeValue='Blue';

INSERT INTO RecordAttribute
SET id=NULL,
BaseRecordId=2,
AttributeType='Hair';
AttributeValue='Brown';

INSERT INTO RecordAttribute
SET id=NULL,
BaseRecordId=2,
AttributeType='Name';
AttributeValue='Alan';

INSERT INTO RecordAttribute
SET id=NULL,
BaseRecordId=2,
AttributeType='Name';
AttributeValue='Smith';

Now you can find what you want in a very freeform fashion:

SELECT id, name FROM BaseRecord
WHERE id=BaseRecordId
AND (
AttributeValue="Blue"
OR AttributeValue="Green"
OR AttributeValue="Red"
)
GROUP BY BaseRecordId

etc