EN VI

Php - SQL querybuilder with accented characters?

How to Php - SQL querybuilder with accented characters

I'm using querybuilder in my Symfony project to index all my person entities using several parameters like firstname, lastname, adress...

Each person can have 3 firstnames maximum. I'm using select2 in my form to allow the user to write several firstnames in the same inputfield.

Everything is working except the fact that if I'm typing an accented character in the field (like Rémy for example) I got the following error :

[Syntax Error] line 0, col 730: Error: Expected end of string, got 'é'

I tried everything but I cant find the error, here's the code in my PersonRepository which manages the firstname parameter:

    if (!empty($firstNames)) {
        $orConditions = $qb->expr()->orX(); 
    
        foreach ($firstNames as $firstName) {
            $orConditions->add(
                $qb->expr()->like('LOWER(ip.firstName)', ':firstName' . $firstName)
            );
            $qb->setParameter(':firstName' . $firstName, '%' . mb_strtolower($firstName, 'UTF-8') . '%');
        }
    
        $qb->andWhere($orConditions); 
    }

Can you help me guys?

Solution:

Based on your comments I think this should work. Since your array keys are the same as the values we'll just manually keep track of the index.

The way you are building the parameters you are effectively making :prenom_Rémy, and it appears you can't use such characters in the placeholders/parameters.

Your comment mentioned just removing the accents, however I'd instead recommend going with something you can better control such as an incrementing number.

if (!empty($prenoms)) {
    $orConditions = $qb->expr()->orX();
    $idx = 0;
    foreach ($prenoms as $prenom) {
        $orConditions->add($qb->expr()->like('LOWER(ip.prenom)', ':prenom_'.$idx));
        $qb->setParameter(':prenom_'.$idx, '%'.mb_strtolower($prenom, 'UTF-8').'%');
        $idx++;
    }
    $qb->andWhere($orConditions);
}