Mysterious failure of SQL query in mysql

Numerous strange failures of mySQL queries have been dogging me for days. My having failed to find the cause, can anyone suggest where I should look for a solution? This SQL query is a particularly simple example:

SELECT * FROM `applicants` WHERE `first_name` LIKE "%Tim%" ORDER BY `date_created` DESC LIMIT 1

This causes a syntax error in mySQL, at a position near LIKE “%Tim%”. However, a copy/paste works in phpmyadmin. I’ve done a hex check but can see no invisible characters that might cause a problem. The relevant code lines are

$sql = generateSql($metaData);
$stmt = $pdo->prepare($sql);
$stmt->execute();

and in generateSql are:

$fields = ['', 'email', 'first_name', 'last_name', 'postcode', 'location'];
...
$field = $fields[$metaData['sfield']];
return 'SELECT * FROM `applicants` ' .
  'WHERE `' . $field . '` LIKE "%' . $metaData['stext'] . '%" ' .
  'ORDER BY `date_created` DESC LIMIT 1';

I’ve also re-written the SQL generating code as one line, but it makes no difference.

Other queries generated in the same function don’t cause errors (well, not at the moment, they don’t).

Answer

try avoid double quotes arund like string

SELECT * FROM `applicants` 
WHERE `first_name` LIKE concat('%','Tim', '%')
 ORDER BY `date_created` DESC LIMIT 1

SELECT * FROM `applicants` 
WHERE `first_name` LIKE '%Tim%'
 ORDER BY `date_created` DESC LIMIT 1

Advertisement

Answer