← Back to PHP Course | Chapter 15: Web Development | Lesson 10 of 12

PHP AJAX Database

Many AJAX requests exist specifically to read or write data in a database -- fetching a live list of comments, saving a new task, updating a status -- combining everything already covered: reading the AJAX request, running a database query (safely, with prepared statements), and returning the result as JSON.

In this page:

  1. Fetching Data from a Database for AJAX
  2. Saving AJAX-Submitted Data to a Database
  3. Handling Database Errors Safely
  4. Reusing a Database Connection Across Requests
  5. A Complete AJAX Database Endpoint Example
Syntax
php 
$stmt = $pdo->prepare("SELECT * FROM table_name WHERE column = ?");
$stmt->execute([$value]);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

header("Content-Type: application/json");
echo json_encode($rows);

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 
<?php
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE products (name TEXT)");
$db->exec("INSERT INTO products VALUES ('Book'), ('Pen')");
// Declare `$result`, set to `$db->query("SELECT * FROM products")`
$result = $db->query("SELECT * FROM products");
// Declare `$rows` as an empty array
$rows = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) $rows[] = $row;
// Print `json_encode($rows)` to the output
echo json_encode($rows);
?>

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 
<?php
// Set `$_POST['name']` to "Alice"
$_POST['name'] = "Alice";
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (name TEXT)");
// Declare `$stmt`, set to `$db->prepare("INSERT INTO users (name) VALUES (:name)")`
$stmt = $db->prepare("INSERT INTO users (name) VALUES (:name)");
$stmt->bindValue(':name', $_POST['name'], SQLITE3_TEXT);
$stmt->execute();
// Print `json_encode(["status" => "saved"])` to the output
echo json_encode(["status" => "saved"]);
?>

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 
<?php
// Try running this block; jump to `catch` if it throws
try {
    // Create a new `SQLite3` instance with ':memory:', stored in `$db`
    $db = new SQLite3(':memory:');
    $db->exec("SELECT * FROM nonexistent_table");
// Catch Exception $e
} catch (Exception $e) {
    // Call `error_log($e->getMessage())`
    error_log($e->getMessage());
    // Print `json_encode(["status" => "error", "message" => "Something went wrong"])` to the output
    echo json_encode(["status" => "error", "message" => "Something went wrong"]);
}
?>

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 
<?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";
?>

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 
<?php
// Set `$_POST['email']` to "[email protected]"
$_POST['email'] = "[email protected]";
// Check whether `!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)`
if (!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)) {
    // Print `json_encode(["status" => "error", "message" => "Invalid email"])` to the output
    echo json_encode(["status" => "error", "message" => "Invalid email"]);
// Otherwise, run this branch
} else {
    // Create a new `SQLite3` instance with ':memory:', stored in `$db`
    $db = new SQLite3(':memory:');
    $db->exec("CREATE TABLE subscribers (email TEXT)");
    // Declare `$stmt`, set to `$db->prepare("INSERT INTO subscribers (email) VALUES (:email)")`
    $stmt = $db->prepare("INSERT INTO subscribers (email) VALUES (:email)");
    $stmt->bindValue(':email', $_POST['email'], SQLITE3_TEXT);
    $stmt->execute();
    // Print `json_encode(["status" => "ok"])` to the output
    echo json_encode(["status" => "ok"]);
}
?>
Related Topics

PHP AJAX PHP · PHP AJAX XML · PHP AJAX Intro · PHP AJAX Live Search

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. 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.
  2. Opening a new database connection on every single AJAX request without any connection reuse strategy, which can add unnecessary overhead under heavy traffic.
  3. Returning raw database error messages directly in the AJAX response, potentially leaking sensitive schema or configuration details to the client.
Chapter Summary
  • 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.
Browser Support

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:

← Previous Lesson PHP AJAX PHP Next Lesson → PHP AJAX XML

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.