Create a Dynamic MySQL Query using an Array - PHP

Question

I am trying to run a MySQL query wherey piece is being lookedup using LIKE.

Table Structure: TableExample

id    name     piece   
0     jon      piece0
1     james    piece3
2     sarah    piece6

The snipped I have so far:

$pieces  = "piece0 piece1";  //variable
$piecearrayexplode = explode(" ", $pieces);
$piece0 = $piecearrayexplode[0];
$piece1 = $piecearrayexplode[1];

$sql = "SELECT * FROM TableExample WHERE piece LIKE '%$piece0%' OR pieces LIKE '%$piece1%'";

The problem I have is that $pieces is a variable and I need $sql to be dynamic and automatically feature the correct number of LIKE statements.

E.g. if $pieces = "piece0 piece1 piece2", I want $sql to be:

$sql = "SELECT * FROM TableExample WHERE piece LIKE '%$piecearrayexplode[0]%' OR pieces LIKE '%$piecearrayexplode[1]%' OR pieces LIKE '%$piecearrayexplode[2]%'";

Note: $pieces is always separated by space.

I can do a word count.

$count = str_word_count($pieces);

I don't know where to go from there.

I did look at this Create a dynamic mysql query using php variables

It doesn't seem to be what I'm looking for because the LIKEs are successive and not 1 single statement like WHERE. Am I missing something here?

Answer 1

So build your query dynamically too:

$foo = '... list of pieces ...';
$parts = explode(' ', $foo);

$likes = array();
foreach($parts as $part) {
   $likes[] = "piece LIKE '%$part%'";
}

$sql = "SELECT ... WHERE " . implode(' or ', $likes);

But note that this is vulnerable to sql injection attacks.