How to search in mysql for a word that contains in a PHP variable?

Question

I have multiple variables that contains customer names and looks like for example as below:

$customer =  $row["customer_name"];

This will contain i.e.: test Microsoft test test test

Now, in my database I have the column customer and the column contains Microsoft Netherlands Office

To look into the database, I have like below but it doesn't give me any results:

$query="SELECT * FROM specific_req WHERE customer LIKE '%$customer%'";

I want to search in the database table that contains any word from $customer.

Answer 1

One way that you can do this is to split your $customer variable into an array of search terms, this allows you to search each term separately in the table.

$customer = "test Microsoft test test test";
$customers = explode(" ", trim($customer));

$queryString = "";
$customers = array_unique($customers);
foreach($customers as $c) {
    $queryString .= "`customer` LIKE '%{$c}%' OR ";
}
$queryString = rtrim($queryString, " OR ");

$query = "SELECT * FROM specific_req WHERE {$queryString}";

echo $query;

This would make $query output

SELECT * FROM specific_req WHERE `customer` LIKE '%test%' OR `customer` LIKE '%Microsoft%'

All we are doing is using PHP's explode() function, breaking your variable at every space character (After using trim() to remove beginning and ending spaces to avoid searches like '%%'). This gives us an array of values, which we then run through the foreach() loop, building $queryString to match the SQL syntax required.

WARNING: This solution may be vulnerable to SQL Injection, which could be fixed by escaping the $c variable inside the loop.

Answer 2

Build regular expression of alternative matches in PHP and run it in SQL:

$customer = "(" . implode('|', array_unique(explode(' ', $customer))) . ")";

$sql = "SELECT * FROM specific_req WHERE customer REGEXP '{$customer}'";