select with a concat

Anon

I have the following select statement:

select count(*) from article where $search_type like '%$search_request%

which works fine if $search_type is a specific field in my mysql database.

What I'd like to do is a row count if the specified search_request appears in ANY of several fields, so for instance the word could appear in "firstname" and/or "lastname".

In my select to retrieve data i use:

select id_article, title, concat('firstname', ' ', 'lastname') as author from article where $search_type like '%$search_request%'

How can I change change my select count(*) to work? Should I be using a concat or is there another way?

thanks

rrijnders

...Just group together your search criteria like so:

...WHERE (field1 LIKE '%search_criteria%') OR (field2 LIKE '%search_criteria%')OR (field3 LIKE '%search_criteria%')

-- Rich

Anon

Thankyou Richard!

That works now but I should have thought a little further ahead on this...

I now have a problem if search_criteria is "Joe Bloggs" as that string won't be found in firstname or lastname. Joe Bloggs will only appear if the search_criteria is "Joe" or "Bloggs".

Should i explode the search_criteria and then do (pretend there's a nice loop here to go through however many words are in the criteria):

...WHERE (field1 LIKE '%search_criteria_1%' OR LIKE '%search_criteria_2%') OR (field2 LIKE '%search_criteria_1%' OR LIKE '%search_criteria_2%')

or is there an easier way?

rrijnders

It depends how sophisticated you want to get in your searches. That's the way I would do it for a simple site search engine. If the user puts in joe and bloggs, you don't really know if he wants joe bloggs or anything with joe or bloggs in it. Otherwise you have to take into account multi-word litterals and check to see if the user put quotes around groups of words.

Another note of caution: in some DBs the LIKE operator is case sensitive; that is, Joe will not match JOE. In most DB's you can use the lower() function on the db field and strtolower() in PHP on your search criteria to match them up.

-- Rich