I have a PHP/mySQL search script that for some reason, after the latest update by the developer, has stopped working when searching with wildcards (*). What's worse, it not only stopped working, but it returns the following nasty (for visitors) mySQL error, which is based on a donut wildcard search:
SELECT a.product_id, a.category_id FROM products a
LEFT JOIN users u ON u.userid=a.ownerid
WHERE a.active=1 AND a.approved=1 AND a.deleted=0 AND a.creation_in_progress=0
AND a.name LIKE '%%'
AND ((a.name REGEXP '( )*(*donut*)( )*') OR (a.description REGEXP '( )*(*donut*)( )*'))
The error:
Mysql Error: Got error 'repetition-operator operand invalid' from regexp
Unfortunately, the developer can't (or won't) fix it and it's rendering my website useless if someone does a wildcard search.
Any ideas what the problem could be? Or an easy fix? I already tried putting a backslash before the $keywords_search but that didn't work. I also tried running the above mySQL query directly in PhpMyAdmin and it produced the same error.
I also tried to completely get rid of the REGEXP part but then it won't return any results at all.
The PHP search code of this part is as follows:
$query[] = "((a.name REGEXP '( )*(" . str_replace(' ', ')*( )*(', $keywords_search) . ")( )*')
OR (a.description REGEXP '( )*(" . str_replace(' ', ')*( )*(', $keywords_search) . ")( )*'))";
You want to escape your stars (*) if you want it to match a literal star. If you want it to translate "any string", you need to change it to .*. It's that simple. Just take the user's input and apply the replacing on it before you query the database.