PHP AJAX Database
In this page:
Fetching Data from a Database for AJAX
A read-only AJAX endpoint runs a SELECT query and returns the results as JSON -- combining a database SELECT (as covered in the MySQL topics) with json_encode() to produce a response JavaScript can parse directly into usable data.
Note: Select only the specific columns an AJAX endpoint actually needs to return, rather than every column, to keep the JSON response lean.
Warning: Returning raw database rows without filtering out sensitive columns (like a password hash) is a common accidental data leak in AJAX endpoints.
Example: Fetching Data from a Database for AJAX
<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE products (name TEXT)");
$db->exec("INSERT INTO products VALUES ('Book'), ('Pen')");
$result = $db->query("SELECT * FROM products");
$rows = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) $rows[] = $row;
echo json_encode($rows);
?>Login to try C/C++/Java/PHP code in the editor
Saving AJAX-Submitted Data to a Database
A write AJAX endpoint reads submitted data (usually from $_POST), validates it, and inserts or updates it in the database using a prepared statement -- exactly the same safe pattern already covered for regular form submissions, just triggered by an AJAX call instead.
Note: Use a prepared statement for every AJAX-submitted database write, with no exceptions, since the data's origin (AJAX vs. a regular form) does not change its trust level.
Warning: Skipping validation before an AJAX-triggered database write can let malformed or malicious data into the database just as easily as an unvalidated form submission would.
Example: Saving AJAX-Submitted Data to a Database
<?php
$_POST['name'] = "Alice";
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
$stmt = $db->prepare("INSERT INTO users (name) VALUES (:name)");
$stmt->bindValue(':name', $_POST['name'], SQLITE3_TEXT);
$stmt->execute();
echo json_encode(["status" => "saved"]);
?>Login to try C/C++/Java/PHP code in the editor
Handling Database Errors Safely
A failed database query in an AJAX endpoint should be logged server-side (with the real error detail) but reported to the client with only a generic, non-revealing message -- exposing raw database error text can leak sensitive information about the database's structure to anyone inspecting network requests.
Note: Log the detailed mysqli_error() or exception message to a server-side log file, and send only a generic "something went wrong" message in the client-facing response.
Warning: Returning mysqli_error() directly in an AJAX JSON response can reveal table names, column names, or even fragments of your SQL structure to anyone who opens their browser's developer tools.
Example: Handling Database Errors Safely
<?php
try {
$db = new SQLite3(':memory:');
$db->exec("SELECT * FROM nonexistent_table");
} catch (Exception $e) {
error_log($e->getMessage());
echo json_encode(["status" => "error", "message" => "Something went wrong"]);
}
?>Login to try C/C++/Java/PHP code in the editor
Reusing a Database Connection Across Requests
Each AJAX request is a separate HTTP request, and PHP typically opens a fresh database connection for each one (since PHP scripts do not persist state between requests by default) -- persistent connections (mysqli_pconnect() or PDO's persistent option) can reduce this overhead under heavy, repeated AJAX traffic.
Note: Consider persistent connections specifically for high-traffic AJAX endpoints where connection setup overhead becomes measurable, not as a default for every project.
Warning: Persistent connections have their own tradeoffs (like connection state leaking between requests if not carefully managed) and are not automatically the right choice for every application.
Example: Reusing a Database Connection Across Requests
<?php
// mysqli_pconnect($host, $user, $pass, $db) or PDO's persistent option
$pdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_PERSISTENT => true]);
echo "Persistent connection reduces overhead under heavy AJAX traffic";
?>Login to try C/C++/Java/PHP code in the editor
A Complete AJAX Database Endpoint Example
Bringing every piece together: an endpoint reads AJAX-submitted data, validates it, runs a safely parameterized query, handles any database error gracefully, and returns a consistent JSON response -- the full realistic pattern behind most AJAX endpoints that touch a database.
Note: Use this same complete pattern -- read, validate, prepare, execute, respond -- as a template for any new AJAX database endpoint you build.
Warning: Missing any single step in this pipeline (skipping validation, skipping prepared statements, or leaking raw errors) reintroduces exactly the risks this pattern is designed to avoid.
Example: A Complete AJAX Database Endpoint Example
<?php
$_POST['email'] = "[email protected]";
if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
echo json_encode(["status" => "error", "message" => "Invalid email"]);
} else {
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE subscribers (email TEXT)");
$stmt = $db->prepare("INSERT INTO subscribers (email) VALUES (:email)");
$stmt->bindValue(':email', $_POST['email'], SQLITE3_TEXT);
$stmt->execute();
echo json_encode(["status" => "ok"]);
}
?>Login to try C/C++/Java/PHP code in the editor
- Skipping prepared statements for an AJAX endpoint's database query, on the mistaken assumption that AJAX-submitted data is somehow less risky than a regular form's.
- Opening a new database connection on every single AJAX request without any connection reuse strategy, which can add unnecessary overhead under heavy traffic.
- Returning raw database error messages directly in the AJAX response, potentially leaking sensitive schema or configuration details to the client.
- An AJAX endpoint that touches the database follows the same pattern as any PHP database script: connect, prepare, execute, fetch, respond.
- Prepared statements are just as essential for AJAX-submitted data as for a regular form, since both are equally untrusted user input.
- Database errors should be logged server-side but returned to the client as a generic, non-revealing error message.
mysqli and PDO database access work identically whether a PHP script is handling a full page load or an AJAX request.
Chapter Quiz — Complete all 12 topics to unlock
0/12 topics done
Complete these topics first: