MySQL LIKE search from multi fields by PHP, PDO

Question

I have the MySQL table that has filename and description fields. Then, I wrote this code to do LIKE search. However my poor code cannot conduct what I want to do.

Error message says

Syntax error or access violation.

PHP 7.0 / MySQL 5.0.95 is my server's.

$keywords = str_replace( "\xc2\xa0", " ", $keywords );
$keywords = preg_replace("/[\s]+/", " ", trim($keywords));
$keywordsarray = array_unique(explode(' ', $keywords));
$sql1 = "SELECT * FROM file_table WHERE ";

$sql2 = array();
$key = array();
foreach ($keywordsarray as $word) {
$sql2[] = " (filename LIKE ? OR describe LIKE ? )";
$key[] = '%'.$word.'%';
$key[] = '%'.$word.'%';
}
$builtsql = $sql1.implode(' AND ', $sql2);
$query = $db->prepare($builtsql);
$query->execute($key);

Could you give me good ideas? Thanks.

`$key` shouldn't be made part of the execute and doesn't use an argument neither. — Funk Forty Niner, Sep 23 '17 at 01:42
I appreciate your kindly comment. Now I am re-writing code from first. — Kazyumaru, Sep 23 '17 at 01:45
@Fred-ii- confused as to your comment on how it doesn't use an argument. The OP code pretty much follows the [example given on PHP](http://php.net/manual/en/pdostatement.execute.php#example-1055). Main issue I see is the use of `describe` not being escaped as a [reserved keyword](https://dev.mysql.com/doc/refman/5.5/en/keywords.html). https://dev.mysql.com/doc/refman/5.7/en/describe.html — Will B., Sep 23 '17 at 02:29
Dear Fred Special thanks and I rewrote the code. Then, I could get the Sql responses that I want. In addition, thank you again for answering for my poor English sentences. — Kazyumaru, Sep 23 '17 at 08:59

score 1 · Answer 1 · answered Sep 23 '17 at 02:41

The issue is caused by not escaping the reserved word describe in your query.

You should be able to resolve the Syntax error issue by wrapping the column name(s) in identifier quotes (backtick).

$sql2[] = " (`filename` LIKE ? OR `describe` LIKE ? )";

MySQL LIKE search from multi fields by PHP, PDO

1 Answers1