SQL using an Array of words

Question

Is the following I have a search field in a form where the user type for example a color or product and is returned to it a list of products with the characteristics typed in the form, the problem and the following, when I pass the array in SQL is returned to me: Notice: Array to string Conversion, however I saw here in pt.stack publications where arrays were passed within sql. The following is the PHP code responsible for creating the Array:

            $values = explode(' ',$values);
            $keywords = array();
            foreach($values as $value){
                $value = mysql_real_escape_string($value);
                $value = '%' . $value . '%;';
                $keywords[] = $value;
            }
            $query = "SELECT title, urlvideo FROM $colunm WHERE title ILIKE ANY ($keywords)";
            echo $query;

Base link: Link to en.stackoverflow

It would be possible to do this in PDO as well?

Answer 1

1. That question Select with indefinite amount of conditions is about Postgresql and its code is about Mysql, so it probably doesn’t work.
2. ILIKE not supported by Mysql, you can use several LIKEs or REGEXP in Mysql to get the same effect.
3. ANY in Mysql is different from Postgresql, while in Postgresql you use to query using arrays, in Mysql you use to query with sub-queries (http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html)
4. PHP must be a string, you must use implode to join the array in a string in the format of array for Mysql, this way (chance you come to use Postgresql):
5. Note that Mysql supports query case-insensitive you must use the Collections with the end _ci, for example: utf8_general_ci

Postgresql:

'{"' . implode('", "', $keywords) . '"}'

And remove the dot and comma on this line:

$value = '%' . $value . '%;'; leaving so $value = '%' . $value . '%';

A tip: I believe that the correct thing would be for you to recover the value, using as $k => $v

Mysql LIKE with implode:

Note that in this example it is necessary 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);

Must 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 must remove the signs % of this line $value = '%' . $value . '%'; leaving her like this $value = $value;, because these signs are necessary 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) . '\'';

Must 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, in accordance with this reply from Soen.

Note that it is necessary to pass the % in the execute, because if you use in prepare thus: prepare('SELECT title, urlvideo FROM table WHERE title title LIKE \'%?%\''); the server will interpret the code like this: SELECT title, urlvideo FROM table WHERE title title LIKE '%'?'%', ie will add apostrophes (AKA single quotes) within the instruction '%...%'

Example of use:

$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);