Query with a PHP foreach using OR and LIKE %?

I have sort of a tricky problem, and I want to see if there is an easier way about solving it.

I have let’s say

$numbers= $_GET['numbers']; //resulting in $numbers=array(1,2,3)

JavaScript
​x
 
$numbers= $_GET['numbers']; //resulting in $numbers=array(1,2,3)​

The count in the array may vary depending on the $_GET value…
These need to search using ‘%” . $number. “%’ because there might be more than one number in the row separated by commas

My ideal result would perform a search for the $numbers(1,2,3) with:

SELECT * FROM database WHERE numbers LIKE('%1%' OR '%2%' OR '%2%')

JavaScript
 
SELECT * FROM database WHERE numbers LIKE('%1%' OR '%2%' OR '%2%')​

Answer

No need to inject many OR operators for each element of the Array. Instead you can use rlike (the regex matcher operator of MySQL) and simplify your code to a great extent like this:

$numbers = array(1,2,3);
$sql = 'SELECT * from `database` WHERE number rlike "^'.implode('|', $numbers).'$"';
// $sql becomes: SELECT * from `database` WHERE number rlike "^1|2|3$"

JavaScript
 
$numbers = array(1,2,3);$sql = 'SELECT * from `database` WHERE number rlike "^'.implode('|', $numbers).'$"';// $sql becomes: SELECT * from `database` WHERE number rlike "^1|2|3$"​

Advertisement

Answer