How do you match a dozen fields against multiple words?

AceE

How do you match multiple fields against multiple words using AND?

Say I want to find everyone with firstname, lastname, email, address, city, employer, key words (which I forgot to add), etc. of 'Smith', but also against '416' to match every phone_home, phone_cell, etc of people living in Toronto. But it could be searching all the different database fields, not just phones or postal codes.

I want to only find records where all of the words match the record in one field or another, thanks.

   $to_search = mysql_real_escape_string($contacts->search);
   $query = "SELECT *, MATCH
		  (firstname, lastname, phone_home, phone_cell, phone_fax, email, address, city, province_state, postal_code, country, employer, job_title)
		  AGAINST ('$to_search') AS score FROM contact_info WHERE MATCH
		  (firstname, lastname, phone_home, phone_cell, phone_fax, email, address, city, province_state, postal_code, country, employer, job_title)
		  AGAINST ('$to_search') ORDER BY $contacts->sort_by $contacts->sort_ord LIMIT $contacts->max_list";

Is currently only looking for any field with either 'Smith', 'AND', or ' 416'.

jheitz

Out of curisotity, can I ask what are you trying to do? It would seem that the query you are trying to find would be overly complex and very slow, is it really worth it? I Would initially advice against it.

AceE

Thanks for replying.

Searching through thousands of contacts, admin tool. It is a pretty big, potentially slow query, but it'll only be accessed by 2-3 people. I'm essentially trying to Google every field of the contact table together, at once. Being able to use a keyword like 'AND' to specify results.