Jump to content

Search %like% in a table from multiple columns


Zephni

Recommended Posts

Hi guys, having a problem with a search query, I have a customer base, and want to search through by forename and surname using one search query, or every line of address. I want somthing similar to this:

 

SELECT * FROM customers WHERE client_id='$_SESSION[client_id]' AND (col1, col2, col3) LIKE '%$search_term%

 

This works great if I replace (col1, col2, col3) with one column name like 'forename'. So what is the most simplistic method to do what i'm trying to do?

 

Thanks in advance!

Link to comment
Share on other sites

Thanks mate that worked well! But what if i wanted the user to be able to type either;

 

'Craig' or 'Dennis' or 'Craig Dennis'

 

Is there a way to do:

 

(forename LIKE '%$search_term%'  OR surname LIKE '%$search_term%') OR (forname AND surname LIKE '%$search_term%')

 

Dyu see what I mean? I know thats not the correct syntax, but is there a way to say forename and surname together and test against them.

Link to comment
Share on other sites

I have sorted it but in a round about way, I have split the $search term up into seperate values using the PHP function explode() using " " (a space) as a delimmiter, so now i have $search_arr[0] as the first word and $search_arr[1] as the second. Then I used this query:

 

SELECT * FROM customers WHERE (client_id='$_SESSION[client_id]') AND ((forename LIKE '%$search_term%' OR surname LIKE '%$search_term%') OR (forename LIKE '%$search_arr[0]%' AND surname LIKE '%$search_arr[1]%'))

 

woah

 

But if anyone else knows a better way please let me know =)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.