Products search facility

[stualk]

I have a search facility on my website that works fine. However, using the code below the search only searches one field of my database. Ideally I want to search every field of the database so that the results are greater. My code is about 100 lines in length but these few lines are the main parts of the search. Can anyone see a simple way I can amend this code so that it searches multiple fields?

[code]
// this is to account for spaces in the search field
$code_short = str_replace(" ", "", $code_short);
$result = mysql_query("select prod_code from products_db where prod_code like '%$code_short%' order by id limit $offset,$limit") or die("Cant connect to the database. Please try later");
[/code]

I tried to change the 'select prod_code from' reference to 'select * from' but that didn't do the trick.

[c4onastick]

Try adding something like:
[code]mysql_query("SELECT prod_code FROM products_db WHERE prod_code LIKE '%$code_short%' OR prod_desc LIKE '%$code_short%' order by id limit $offset,$limit")[/code]

[stualk]

That now works well but suddenly my code for allowing spaces in the search field has stopped working. I can search for things in any field of the database now but only wihtout spaces. The minute I add a space it returns no results. Any ideas why that might be? Here's the code:

[code]
// this is to account for spaces in the search field
$code_short = str_replace(" ", "", $code_short);
$result = mysql_query("select * from products where prod_code like '%$code_short%' OR prod_description like '%$code_short%' OR prod_heading like '%$code_short%' OR prod_sub_heading like '%$code_short%' OR prod_weight like '%$code_short%' order by id limit $offset,$limit") or die("Cant connect to the database. Please try later");
[/code]

[c4onastick]

If I had to guess, I say this has something to do with it...
[quote][code]$code_short = str_replace(" ", "", $code_short);[/code][/quote]
It looks like you're removing all the spaces! 'LIKE' is SQL still needs something to grab on to. Removing the spaces  would turn something like this:
[code]$code_short = 'This is a short string';[/code]
Into this:
[code]$code_short = 'Thisisashortstring';[/code]
I'm not surprised that the latter doesn't return any results. Why are you removing the spaces? MySQL doesn't mind having spaces in there.

[stualk]

If you want an honest answer I don't know why I was using that line of code!!!

I have just removed it and it works fine now! D'oh!

Thanks for your help, much appreciated!

[c4onastick]

Ha! Glad to help!