That question Select with undefined number of conditions is about PostgreSQL and your code is about MySQL, so it probably can not work.
ILIKE is not supported by MySQL, you can use multiple LIKE s or REGEXP in MySQL to achieve the same effect.
ANY in MySQL is different from PostgreSQL, while in PostgreSQL you use it to query using arrays, in MySQL you use it to query with sub-queries ( link )
PHP should be a string , you should use implode to join array to string with the format of array to MySQL, in this way ( you may come to use PostgreSQL ):
Note that MySQL support case-insensitive query you should use collections with the final _ci , for example: utf8_general_ci
PostgreSQL:
'{"' . implode('", "', $keywords) . '"}'
And remove the semicolon from this line:
$value = '%' . $value . '%;'; thus leaving $value = '%' . $value . '%';
One tip: I believe the correct one would be to retrieve the value, using as $k => $v
MySQL LIKE with implode :
Note that in this example you need to use "apostrophes": $value = '\'%' . $value . '%\'';
foreach($values as $k => $v){
$value = mysql_real_escape_string($v);
$value = '\'%' . $value . '%\''; //Adicionado apóstrofos
$keywords[] = $value;
}
$query = 'SELECT title, urlvideo FROM ' . $colunm . ' WHERE title LIKE ' . implode(' OR title LIKE ', $keywords);
It should return something like:
SELECT title, urlvideo FROM table WHERE title LIKE '%A%' or title LIKE '%B%' or title LIKE '%C%'
MySQL REGEXP :
Note that using REGEXP you should remove the % signs of this line
$value = '%' . $value . '%'; thus leaving $value = $value; , because these signals are needed in this case only with LIKE .
foreach($values as $k => $v){
$value = mysql_real_escape_string($v);
$value = $value; //Removido %
$keywords[] = $value;
}
$query = 'SELECT title, urlvideo FROM ' . $colunm . ' WHERE title REGEXP \'' . implode('|', $keywords) . '\'';
It should return something like:
SELECT title, urlvideo FROM table WHERE title REGEXP 'A|B|C'
Using PDO:
It is possible to work arrays directly with prepare , as this answer from SOen .
Note that it is necessary to pass % to execute , because if you use prepare like this:
prepare('SELECT title, urlvideo FROM table WHERE title title LIKE \'%?%\''); the server will interpret the code as: SELECT title, urlvideo FROM table WHERE title title LIKE '%'?'%' , that is, it will add apostrophes (AKA single quotes) within the '%...%'
Example usage:
$values = explode(' ', $values);
$keywords = array();
foreach($values as $k => $v){
$keywords[] = '%' . $v . '%';
}
$db = new PDO(...);
$keys = 'title LIKE ' . str_repeat('? OR title LIKE ', count($keywords) - 1) . '?';
$sth = $db->prepare('SELECT title, urlvideo FROM ' . $colunm . ' WHERE ' . $keys);
$response = $sth->execute($keywords);
if ($response) {
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
var_dump($row);
}
} else {
echo 'Erro: ';
var_dump($sth->errorInfo());
}
Documentation PDOStatement :: execute
Using PDO and REGEXP:
$values = explode(' ', $values);
$db = new PDO(...);
$keys = str_repeat('?|', count($values) - 1) . '?';
$sth = $db->prepare('SELECT title, urlvideo FROM ' . $colunm . ' WHERE title REGEXP \'' . $keys . '\'');
$sth->execute($values);
