SELECT WHERE ColumnA+ColumnB LIKE '$q%'

[markspark100]

Hi,

Is it possible to search through two colums concatenated for one value.

I have a products table which includes the columns Brand and Product name, I'm trying to make an autosuggest search bar so when people are typing I need the search to recognise the brand and then also the products listed under that brand so this is an example of what i mean:

SELECT ProductName, Brand FROM products WHERE Brand + ProductName LIKE '$q%'

Is this sort of search possible?? if not any ideas how I can get around the problem; I have considered splitting the search term into seperate words however some brands are more than one word so not really sure how to go about that either :confused:

Any help would be really appreciated.

Cheers

[Old Pedant]

Yes, but MySQL doesn't recognize the + operator for string concatention. Try:

SELECT ProductName, Brand FROM products WHERE CONCAT(Brand, ProductName) LIKE '$q%'

Of course, you could also simply do

SELECT ProductName, Brand FROM products
WHERE Brand LIKE '$q%'
OR ProductName LIKE '$q%'

The meaning is a bit different, of course.

For example, if you have a record where:
Brand = 'Ford'
ProductName='Mustang'
Then if your $q is 'Ford M' you will *NOT* get a match (because of the space between "d" and "M").
BUt neither would you get a match if you tested the fields separately.

ANYWAY...I guess the answer to your actual question is "Yes."

You *MIGHT* want to consider using

SELECT ProductName, Brand FROM products WHERE CONCAT(Brand, ' ', ProductName) LIKE '$q%'

if you expect a space between the brand and product.

[markspark100]

Old Pedant, the third option you gave was exactly what I was looking for!
Thanks.:thumbsup: