相同SQL查询在phpMyAdmin生效但PHP PDO无结果的问题排查

Hey Kasper, I’ve run into this exact issue before—special characters and mismatched encoding between PDO and MySQL are tricky, but let’s break down the fixes step by step:

1. Stop Concatenating SQL (Critical Fix for Security + Encoding) #

The biggest problem here is that you’re directly inserting $langID and $searchText into your SQL string. This doesn’t just put you at risk of SQL injection—it also bypasses PDO’s built-in encoding handling for special characters like 'å'.

Replace your query with a parameterized statement instead. PDO will automatically handle character escaping and encoding matching:

$conn = Database::getConnection();
// Use placeholders (:lang_id and :search) instead of raw variables
$stmt = $conn->prepare('
    SELECT `p_id` 
    FROM `p_desc` 
    WHERE `lang_id` = :lang_id 
      AND (`p_name` LIKE :search OR `p_desc` LIKE :search)
');
// Bind the values (PDO handles encoding automatically)
$searchPattern = "%{$searchText}%";
$stmt->bindValue(':lang_id', $langID, PDO::PARAM_INT);
$stmt->bindValue(':search', $searchPattern, PDO::PARAM_STR);
$stmt->execute();
$matchingIDs = $stmt->fetchAll();

2. Verify Character Consistency Across All Layers #

Even with parameterization, you need to make sure encoding matches everywhere:

• Database/Table/Field Check: Confirm your p_desc table and the p_name/p_desc fields are actually using latin1_swedish_ci (you can check this in phpMyAdmin under the table structure tab).
• PDO Connection Configuration: Ensure your DSN charset and init command are aligned. Since your DB uses latin1, stick with that:

  self::$charset = 'latin1';
  self::$collation = 'latin1_swedish_ci';
  $dsn = "mysql:host=" . self::$serverHost . ";dbname=" . self::$databaseName . ";charset=" . self::$charset;
  $options = [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
      PDO::ATTR_EMULATE_PREPARES => false,
      PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES '{$self::$charset}' COLLATE '{$self::$collation}'",
  ];
  

• Check Active Connection Encoding: After connecting, run this query to confirm PDO is using the right charset:

  $stmt = $conn->query("SELECT @@character_set_connection, @@collation_connection");
  var_dump($stmt->fetch());
  

  This should return latin1 and latin1_swedish_ci.

3. Fix the Search Text Encoding #

If your PHP script files are saved in UTF-8 (which is standard), the $searchText variable will be in UTF-8 encoding—but your database expects latin1. This means the 'å' in your PHP code is a different byte sequence than the one stored in MySQL.

Convert the search text to latin1 before binding it:

$searchTextLatin1 = mb_convert_encoding($searchText, 'latin1', 'utf-8');
$searchPattern = "%{$searchTextLatin1}%";
$stmt->bindValue(':search', $searchPattern, PDO::PARAM_STR);

4. (Optional) Migrate to utf8mb4 for Future-Proofing #

While fixing latin1 works for now, utf8mb4 supports all Unicode characters (including emojis) and is the modern standard. If you can, migrate your database, tables, and fields to utf8mb4_unicode_ci, then update your PDO charset to utf8mb4—this will avoid encoding issues long-term.

内容的提问来源于stack exchange，提问作者Kasper H