使用mysql，php和pdo选择不区分大小写

Question

I'm trying to select some data from a mysql table but I cannot get the Where comparison to be case insensitive, I tried using LOWER:

$wildcard = $_GET['q'];
        $query = "SELECT id, name, departamento FROM gestionDoc_cargos WHERE (LOWER(name) LIKE '%' LOWER(:wildcard) '%' OR LOWER(departamento) LIKE '%' LOWER(:wildcard) '%')";

        try{
            $result = DB::getInstance()->prepare($query);
            $result->bindParam(':wildcard', $wildcard, PDO::PARAM_STR);
            $result->execute();
            $result = $result->fetchAll(PDO::FETCH_ASSOC);
        }catch(PDOException $e){
            die($e->getMessage());
        }
        print_r($result);
        echo json_encode(array_values($result));

but I get the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOWER('C') '%' OR LOWER(departamento) LIKE '%' LOWER('C') '%')' at line 1

and if I remove the LOWER from the query I get a case sensitive select.

Answer 1

This

...snip... ) LIKE '%' LOWER(:wildcard) '%' OR ...snip

is incorrect. You've got a string ('%') followed by a function call (LOWER()) followed by another string, and they're just sitting there - no connecting logic, no concatenation, blah blah blah .

It should be

... LIKE CONCAT('%', LOWER(:wildcard), '%') OR ...

And by default, mysql comparisons ARE case insensitive, unless you force a binary comparison, or you're using a case sensitive collation on your db/table.