I’m trying to search in a column with LIKE ‘%string%’ using ADODb prepared statement (using MySQLi PHP driver) with this:
$q_record = $DB->Prepare(" SELECT author, title FROM books WHERE (author LIKE '%?%' OR title LIKE '%?%') ORDER BY submit_date "); $record = $DB->GetAll($q_record, array( $q, $q ) ) or die($DB->ErrorMsg());
Error message is like
“You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘nano’%’ OR title LIKE ‘%’nano’%’) ORDER BY submit_date DESC ) ‘ at line 4”
Where “nano” is the search keyword.
I have suspicion that I have to escape ‘?’ but I don’t know how to do that.
Advertisement
Answer
Plain and simple:
$q_record = $DB->Prepare(" SELECT author, title FROM books WHERE (author LIKE CONCAT('%', ?, '%') OR title LIKE CONCAT('%', ?, '%')) ORDER BY submit_date ");
In other words, the parameter placeholder ?
does not work like string interpolation. Use it like you would use an SQL variable.