How to generate JSON data with PHP?
Generating JSON data from a database requires careful attention to security, performance, and code structure. The goal is often to serve a results.json file or an API endpoint that returns valid JSON. Looking at the provided source material reveals several critical issues in legacy approaches that modern developers must avoid.
The SQL schema defines a simple Posts table with an auto-incrementing ID, a title, and a URL. The accompanying PHP script attempts to query this data using MySQLi or similar extensions (mysql_query, mysql_fetch_array) and manually construct the JSON string. While this logic works in theory, it relies on deprecated functions and exposes the application to significant vulnerabilities.
The most immediate concern is SQL injection. Using raw queries like select * from Posts limit 20 without prepared statements is a fundamental security risk. If any part of your application were to accept user input for these fields, malicious actors could manipulate the database structure or exfiltrate data. Modern PHP standards enforce the use of parameterized queries to mitigate this. Furthermore, the mysql_* extension was deprecated in PHP 5.5 and removed entirely in PHP 7.0. Any code relying on these functions will fail on current server environments.
Manual string concatenation for JSON, as seen in the example where $title and $url are injected directly into a string, is prone to breaking. If a post title contains a single quote (') or a newline character, it will corrupt the JSON syntax, rendering the output invalid. Proper JSON generation must escape special characters automatically.
The Modern Approach with PDO
To generate a reliable results.json file today, you should switch to PDO (PHP Data Objects) and use prepared statements. Here is a robust implementation pattern:
<?php
// Enable error reporting for debugging
error_reporting(E_ALL);
ini_set('display_errors', 1);
$host = 'localhost';
$db = 'your_database';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $user, $pass, $options);
// Use a prepared statement to prevent SQL injection
$stmt = $pdo->prepare("SELECT title, url FROM Posts LIMIT 20");
$stmt->execute();
$posts = [];
while ($row = $stmt->fetch()) {
$posts[] = [
'title' => htmlspecialchars($row['title'], ENT_QUOTES, 'UTF-8'),
'url' => htmlspecialchars($row['url'], ENT_QUOTES, 'UTF-8')
];
}
// Output valid JSON
header('Content-Type: application/json');
echo json_encode(['posts' => $posts], JSON_PRETTY_PRINT);
} catch (\PDOException $e) {
throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>
This approach separates data fetching from formatting. The json_encode function handles all necessary escaping automatically, ensuring the output is always syntactically correct regardless of the content in the database. It also allows you to easily modify the structure returned to the client without altering the SQL query.
File Generation vs. HTTP Response
The source material mentions generating a results.json file. There are two distinct use cases here: serving data via an HTTP response or creating a static file on disk.
If you are building an API, the PHP script should output JSON to the standard output with the correct header (Content-Type: application/json). The web server then streams this directly to the client's browser or application.
If your requirement is literally to create a file named results.json inside your project directory, you must redirect the output to that file path. However, be aware of the implications. If you store JSON files generated from a database on disk and serve them statically later, you lose the connection between the data source and the content. Any change to the database won't reflect in the results.json file until the script runs again. This creates a synchronization problem that is often better solved by serving the data dynamically via an endpoint rather than managing static assets generated from dynamic sources.
Performance Considerations
When generating JSON for large datasets, memory usage becomes a factor. The example code fetches 20 rows, which is trivial. However, if you were to fetch thousands of rows and load them entirely into memory before encoding, you risk exhausting the PHP process's memory limit (memory_limit in php.ini).
For high-volume data, consider streaming the output or processing chunks. Additionally, ensure that your database connection is closed properly after the query completes. While PDO handles this automatically when using context managers or scopes, explicit resource management is good practice in long-running scripts to prevent connection pool exhaustion.
Conclusion on Best Practices
Avoiding deprecated functions like mysql_query and manual string concatenation is non-negotiable for maintaining a secure and compatible application. By leveraging PDO's prepared statements and the built-in json_encode capabilities, you ensure that your JSON output is both safe against injection attacks and structurally valid. This method scales better as your data grows and aligns with modern PHP development standards.