求助：json_encode处理MySQL查询结果数组失效问题

Hey there, let's work through this json_encode issue you're hitting. I've dealt with this exact problem more times than I can count, so here's the breakdown of what's probably going wrong and how to fix it:

Common Causes & Solutions #

1. Mismatched Character Encodings #

The most frequent culprit is that your MySQL connection or query results aren't using UTF-8 (which json_encode requires to work properly). MySQL often defaults to latin1 or other encodings, which will break JSON conversion.

Fix:
Right after establishing your database connection, set the charset to utf8mb4 (it supports all Unicode characters, including emojis, which plain utf8 doesn't):

mysqli_set_charset($con, 'utf8mb4');

You can also enforce the charset in your SQL query if needed, but the connection-level setting is cleaner:

SELECT * FROM productlist CHARACTER SET utf8mb4;

2. Invalid UTF-8 Characters in Results #

Even if you set the charset, sometimes your data might have corrupted or invalid UTF-8 characters (e.g., from past imports with wrong encodings). These silent errors will make json_encode return null without warning.

Fix:
Clean up each field in your result set to strip invalid characters. You can use iconv or mb_convert_encoding for this:

$rows = array();
while($r = mysqli_fetch_assoc($query)) {
    foreach($r as $key => $value) {
        // Strip invalid UTF-8 characters
        $r[$key] = iconv('UTF-8', 'UTF-8//IGNORE', $value);
    }
    $rows[] = $r;
}

3. Check for json_encode Errors #

When json_encode fails, it returns false—but you can get a specific error message to pinpoint the issue. This is super helpful for debugging.

Fix:
Add error checking right after calling json_encode:

$json = json_encode($rows);
if ($json === false) {
    die('JSON encoding failed: ' . json_last_error_msg());
}

This will tell you exactly what's wrong, like "Malformed UTF-8 characters" or "Control character error, possibly incorrectly encoded".

4. Special Data Types (Binary/NUL Characters) #

If your productlist table has binary columns or fields containing NUL characters (\0), json_encode will fail because these aren't allowed in JSON.

Fix:

• If you don't need these columns, avoid using SELECT *—only fetch the fields you actually need.
• If you do need the binary data, encode it with base64_encode() before adding it to your array:

  while($r = mysqli_fetch_assoc($query)) {
      if (isset($r['binary_column'])) {
          $r['binary_column'] = base64_encode($r['binary_column']);
      }
      $rows[] = $r;
  }
  

  You can then decode it on the frontend with atob() (JavaScript).

Full Working Example #

Putting it all together, here's your revised code with fixes:

// After connecting to the database
mysqli_set_charset($con, 'utf8mb4');

// Execute query
$sql = "SELECT * FROM productlist";
$query = mysqli_query($con, $sql) or die(nl2br("\n Failed to execute query"));

// Fetch and clean results
$rows = array();
while($r = mysqli_fetch_assoc($query)) {
    foreach($r as $key => $value) {
        $r[$key] = iconv('UTF-8', 'UTF-8//IGNORE', $value);
    }
    $rows[] = $r;
}

// Encode to JSON with error checking
$json = json_encode($rows);
if ($json === false) {
    die('JSON encode failed: ' . json_last_error_msg());
}

// Output the JSON
echo $json;

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