PHP+DataTables因特殊字符返回无效JSON响应求助

Hey, I see exactly what's going on here—you're manually constructing JSON strings by concatenating values, which is a recipe for disaster when dealing with special characters like quotes, line breaks, or backslashes. Let's get this sorted properly.

Why Your Current Code Fails #

Your addslashes() approach only handles single quotes, but JSON requires escaping a whole range of characters (double quotes, backslashes, newlines \n, tabs \t, etc.). When a row contains an unescaped double quote, it breaks the JSON structure entirely, which is why only 59 out of 40k rows load—something in row 60 is crashing the JSON parsing.

The Correct Solution: Use PHP's json_encode() #

PHP has a built-in function specifically designed to generate valid JSON, and it automatically handles all special character escaping for you. Ditch the manual string concatenation and let json_encode() do the heavy lifting.

Modified PHP Code (ajaxGetBOM.php) #

<?php
// First, ensure your database connection uses UTF-8 to avoid garbled text
mysqli_set_charset($new_conn, "utf8mb4");

// Fetch only the columns you need (best practice instead of SELECT *)
$query = mysqli_query($new_conn, "SELECT allotment_code, activity, category_name, description FROM bill_of_materials");

// Store all rows in an array
$data = [];
while($row = mysqli_fetch_assoc($query)) {
    $data[] = $row;
}

// Encode the array to valid JSON
// JSON_UNESCAPED_UNICODE keeps non-ASCII characters (like Chinese) readable
echo json_encode(['data' => $data], JSON_UNESCAPED_UNICODE);
?>

Key Improvements: #

• No manual escaping: json_encode() automatically escapes all characters that would break JSON (double quotes, backslashes, line breaks, etc.).
• Cleaner code: Storing rows in an array first makes the logic easier to read and maintain.
• UTF-8 support: mysqli_set_charset($new_conn, "utf8mb4") ensures your data is fetched in the correct character set, preventing garbled text in the JSON response.
• JSON_UNESCAPED_UNICODE: Optional but recommended—this keeps non-ASCII characters (like accented letters or Chinese) as plain text instead of converting them to \uXXXX escape sequences.

Verify Your jQuery/DataTables Code #

Your existing jQuery code is mostly fine, but here's a quick check:

• Ensure the base_url() function returns the correct path to your ajaxGetBOM.php file (test it by visiting the URL directly in your browser).
• The contentType: 'application/json' and dataType: 'json' settings are correct, but DataTables will automatically detect valid JSON responses even if you omit these.

Debugging Tip #

If you still run into issues, directly access the ajaxGetBOM.php URL in your browser. Copy the returned text and paste it into a JSON validator (you can use the browser console: run JSON.parse('your-json-text-here') to see where the parsing fails). This will help you identify any remaining issues with your data or code.

内容的提问来源于stack exchange，提问作者Francis De Gracia