CODEWITHSUNDEEP
phpsql-serverpdosql-likesql-in
AndyPet74
AndyPet74

Reputation: 659

PHP SQL Match like value from array

So I am using PHP and SQL (PDO) to write a query that would take values from an array of check box's (names[]) so for instance someone could submit the values "Bob", "John" and "Bill" and I want to match those with a column in the database.

Problem: Database is an import from Excel spreadsheet used for years and there is no set format for the column I match with, some examples of values stored the database could look like any of the following:

"Bill & Bob"
Bill
John and Bill
"Bill"
John, Bill, and Bob
John will perform the task

As you can see I need to use Like %value% to match values because The post request will send just Bill, John and Bob. My problem is how do I do it with the array passed because it could be just John or it could be 2 or all 3, but you can't use something like WHERE column LIKE in(array) correct? Would my best bet be to run the query once for each name and append the results to a single array? I know I could make some sort of dynamic statement that is something like:

WHERE column LIKE '%Bob%' OR
 column LIKE '%John%' OR
 column LIKE '%Bill%';

My problem doing it that way is that the above method is susceptible to SQL injection if the input isn't satisfied correct? Even if I use a bindValue() and make each one a bindable variable then I have to figure out how many binds there are and I feel like if I have to add names in the future it would be more difficult than it should be. Is the best way to do it what I said above about one query executed multiple times for, once for each name and then append the results together?

Thanks in advance.

EDIT: Driver is PDO, Engine is MSSQL

Upvotes: 0

Views: 578

Answers (2)

Alex
Alex

Reputation: 17289

You should definitely normalize your database.

But just quick approach and since php was mentioned you can generate query like

$names = ['John','Bob','Bill'];
$query = 'SELECT * FROM my_weird_table WHERE ';
$first = true;
foreach ($names as $name) {
    if ($first) {
        $query .= '`column` LIKE "%'.$name.'%" ';
        $first  = false;
    } else {
         $query .= 'OR `column` LIKE "%'.$name'.'%" ';
    }
}

Upvotes: 0

Alexandre Painchaud
Alexandre Painchaud

Reputation: 462

You can do it like this.

//Prepare an array with names
$names = [
  ':name_1' => '%Bob%',
  ':name_2' => '%Bill%',
];
//build the where 
$whereQuery = '';
foreach($names as $bind => $value) {
  if(empty($whereQuery)) $whereQuery .= ' OR ';
  $whereQuery .= ' column LIKE '.$bind;
}
//here is missing the code with SQL and pdo preparing query
//after preparing query just execute with $names
$sth->execute($names);

Be careful this example provide only code helping you to figure out the solution.

Hope this helps

Upvotes: 2

Related Questions