Concat for search results

Databases
1

Hey…

From the db ‘products’ i have two fields, 'code1 and ‘code2’

Data could be:

Code 1 Code2
1002 5660
1025 1512

i have a simple query that is used for search results:

 $query = "SELECT * FROM products WHERE code1 LIKE \\"%$trimm%\\" OR code2 LIKE \\"%$trimm%\\" " ;

If i search ‘1002’ or ‘5660’ alls fine…however the actual product codes are (code1.code2), so when i search for 1002.5660 i get no results.

Ive had a quick look around… can i define CONCAT AS ‘test’ then have WHERE test LIKE \“%$trimm%\”…or am i missing the whole point!

cheers in advance

2

Is there no way you can tell if the user is searching for code1 or code2 or code1.code2?

If there never is a dot in either of the codes then you can at least tell that someone is looking for code1.code2 if there is a dot. Then I think this would work:


// If there is no dot in search term
WHERE code1 LIKE '$search' or code2 LIKE '$search'

// If there is a dot in search term, split search
WHERE code1 LIKE '$firstPartOfSearch' AND code2 LIKE '$lastPartOfSearch'
3

You can define CONCAT AS test and then have HAVING (not WHERE) test LIKE ‘%$trimm%’. However doing it like that would force the dbms to do concat on each and every row in the table. No index can be used. So basically it will be really slow for large tables.

If you always search for code1.code2 (like 1002.5660) then I would recommend splitting the search term in php and then running a query like:

WHERE code1 = '$firstPartOfSearchWord' and code2 = '$secondPartOfSearchWord'
4

hey joool

Unfortunatley the user myay chose a 3d profile (code1) or a colour scheme (code2) or the search for a specific product (code1.code2)