PHP PDO search with match against with umlauts not working

Question

I want to query entries with match against form a MariaDB (InnoDB, utf8mb4_unicode_ci).

It works with a search term without umlauts and the SQL code works even with umlauts at phpmyadmin. If I use umlauts, I get an empty array back.

// DB connection
$dboptions = array('charset=utf8mb4'); // with and without: no difference
$pdo = new PDO("mysql:host=$dbservername;dbname=$dbname", $dbusername, $dbpassword, $dboptions);

// Call
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$statement = $pdo->prepare(
"   SELECT
        Date(a_timestamp) AS thedate,
        count(a_timestamp) AS counted,
        a_comb_f
    FROM `$dbtable`
    WHERE Year(a_timestamp) >= '$chartyear'
    AND MATCH (a_comb_f) AGAINST ('+Müller' IN BOOLEAN MODE)
    GROUP BY thedate
    ORDER BY thedate
");
$statement->execute();
$result = $statement->fetchAll();
var_dump($result);

Result for Müller:

array(0) {} not working!

Result for Example:

it works!

thedate    | counted | a_comb_f
2008-08-20 | 1       | Test Example Test

The pure SQL works fine in phpmyadmin etc.

SELECT
        Date(a_timestamp) AS thedate,
        count(a_timestamp) AS counted,
        a_comb_f
    FROM `dbs4`
    WHERE Year(a_timestamp) >= '$chartyear'
    AND MATCH (a_comb_f) AGAINST ('+Müller' IN BOOLEAN MODE)
    GROUP BY thedate
    ORDER BY thedate

Result:

works!

Background:

InnoDB, utf8mb4_unicode_ci
a_comb_f   Index   varchar(1000)   utf8mb4_unicode_ci

Update 1

Indizes
Schlüsselname   Typ Unique  Gepackt Spalte  Kardinalität    Kollation   Null    Kommentar
a_comb_f    FULLTEXT    Nein    Nein    a_comb_f    141798      Nein

The queried column has an FULLTEXT index.

Update2

I didn't found any solution, so i tried mysqli instead of PDO and it works!

$conn = new mysqli($dbservername, $dbusername, $dbpassword, $dbname);
if ($conn->connect_error) {
    die("Connection Error " . $conn->connect_error);
}
if (!$conn->set_charset("utf8mb4")) {
    printf("Error loading utf8mb4: %s\n", $conn->error);
    exit();
}

$sql = 
"   SELECT
        Date(a_timestamp) AS thedate,
        count(a_timestamp) AS counted,
        a_comb_f
    FROM `$dbtable`
    WHERE Year(a_timestamp) >= '$chartyear'
    AND MATCH (a_comb_f) AGAINST ('+Müller' IN BOOLEAN MODE)
    GROUP BY thedate
    ORDER BY thedate
";
$result = mysqli_query($conn, $sql);
$result = mysqli_fetch_all($result, MYSQLI_ASSOC);

Can anybody tell me why it works with mysqli and not with PDO?

PS: I know the code contains vulnerabilities (sql injection). It's only for testing on localhost.

InnoDB tables require a FULLTEXT index on all columns of the MATCH() expression to perform boolean queries. What is the index type on that column ? — Andreas, Jun 29 '19 at 15:44
Your code is vulnerable to SQL injection. You are following cargo cult: https://phpdelusions.net/pdo/cargo_cult_prepared_statement — Dharman, Jun 29 '19 at 15:49
[**Required reading**](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) — Martin, Jun 30 '19 at 19:01
Possible duplicate of [PDO and UTF-8 special characters in PHP / MySQL?](https://stackoverflow.com/questions/8002822/pdo-and-utf-8-special-characters-in-php-mysql) — Martin, Jun 30 '19 at 19:08

score 0 · Answer 1 · edited Jun 30 '19 at 18:56

0

If you work with PDO, then you have to set the charset at the DNS (Data Source Name):

$pdo = new PDO("mysql:host=$dbservername;dbname=$dbname;charset=utf8mb4", $dbusername, $dbpassword, $dboptions);
                                                        ^^^^^^^^^^^^^^^

Remove the line:

$dboptions = array('charset=utf8mb4');

edited Jun 30 '19 at 18:56

Martin

22,212
11
70
132

answered Jun 30 '19 at 18:46

wado55

66
4

PHP PDO search with match against with umlauts not working

Update 1

Update2

1 Answers1