Computer Science Canada

Searching two db columns sim
Author:  Token [ Mon May 18, 2009 3:46 pm ]
Post subject:  Searching two db columns sim
I have a sort of customer database which I am searching through. Within this database (specifically in the customers table)I have separate first and last name fields (fname and lname). The problem is that if somone enters the search string John Doe and I use my current SELECT command using LIKE it searches fname for the entire word "John Doe", comes out as false, and does the same for lname. My question is how do I put fname and lname together in the query in order to search through them both with my like command?

Note: $str is the search string

code:

$cust_result = db_query("SELECT * FROM customers WHERE
fname LIKE '%".$str."%' OR
lname LIKE '%".$str."%' OR
address LIKE '%".$str."%' OR
area_code LIKE '%".$str."%' OR
city LIKE '%".$str."%' OR
prov LIKE '%".$str."%' OR
country LIKE '%".$str."%' OR
ph_home LIKE '%".$str."%' OR
ph_mobile LIKE '%".$str."%' OR
notes LIKE '%".$str."%' OR
email LIKE '%".$str."%'");
Author:  DtY [ Mon May 18, 2009 3:53 pm ]
Post subject:  RE:Searching two db columns sim
Two way,

1) This one will store more, but will be more flexible, add a new field that has the whole name, and search that

or

2) On the search form put a first and last name box, or split at the space to get first and last name separately and search like:
SELECT .... WHERE `fname`=? AND `lname`=?
Author:  jeffgreco13 [ Mon May 18, 2009 4:16 pm ]
Post subject:  RE:Searching two db columns sim
you could split up the terms and perform a loop to search... use "explode" to convert the search variable into an array containing each word then loop through.. using some ingenuity you can also use this method to rank results' relevance..

for example you search the original search term, which is the sentence the user enters, anything matching that is most relevant, then from there you can loop to match the terms from your array... more words that match the higher the relevance.
Author:  DtY [ Mon May 18, 2009 4:20 pm ]
Post subject:  RE:Searching two db columns sim
Oh, I just thought of something that would work better than my two previous ideas, basically it's number one without a new field:

SELECT ... WHERE (fname+" "+lname)=?

You can also make it work with last name, first name system with:

SELECT ... WHERE (fname+" "+lname)=? OR (lname+", "+fname)=?

:


You can syndicate this boards posts using the file backend.php or view the topic map using sitemap.php.

Terms of Use | Privacy Policy