Chapter 29: AJAX Database
AJAX + Database Example, explained as if I am your patient teacher sitting next to you — going step by step, explaining every part, why we do it this way, what can go wrong, and how real applications usually handle this.
We will build a classic but very realistic example:
Live search / autocomplete for products/students/employees from a real database (using AJAX to talk to PHP + MySQL)
What we will create
- A search box
- As you type → AJAX sends the search term to PHP
- PHP queries the database and returns matching results
- JavaScript shows the results below the input (dropdown style)
- Click on a result → fills the input field
Technologies we use
- HTML + CSS + JavaScript (frontend)
- AJAX (using modern fetch)
- PHP (backend)
- MySQL (database)
Step 1: Create the database (MySQL)
Run this SQL once (phpMyAdmin or MySQL Workbench or command line)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
CREATE DATABASE ajax_demo; USE ajax_demo; CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, price DECIMAL(10,2) NOT NULL, category VARCHAR(80) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Insert some sample data INSERT INTO products (name, price, category) VALUES ('Wireless Mouse Logitech', 1499.00, 'Electronics'), ('Bluetooth Headphones Sony', 3499.00, 'Electronics'), ('Dell XPS 13 Laptop', 98999.00, 'Laptops'), ('Samsung Galaxy S23', 74999.00, 'Smartphones'), ('Mechanical Keyboard RGB', 3999.00, 'Accessories'), ('iPhone 14 Pro Max', 129999.00, 'Smartphones'), ('4K Monitor LG 27"', 24999.00, 'Monitors'), ('USB-C Hub Multiport', 2499.00, 'Accessories'), ('Gaming Chair DXRacer', 18999.00, 'Furniture'), ('External SSD 1TB Samsung', 9999.00, 'Storage'); |
Step 2: PHP file that talks to the database
File: search.php
PHP
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
<?php // ============================================= // Very important security & header settings // ============================================= header('Content-Type: application/json; charset=utf-8'); header('Access-Control-Allow-Origin: *'); // only for local testing - remove or restrict in production! // ============================================= // Database connection (change these!) // ============================================= $host = 'localhost'; $dbname = 'ajax_demo'; $username = 'root'; // ← your MySQL username $password = ''; // ← your MySQL password try { $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { http_response_code(500); echo json_encode(['error' => 'Database connection failed: ' . $e->getMessage()]); exit; } // ============================================= // Get search term from AJAX // ============================================= $term = isset($_GET['term']) ? trim($_GET['term']) : ''; if (strlen($term) < 2) { echo json_encode([]); exit; } // ============================================= // Search query - prevent SQL injection with ? // ============================================= $sql = " SELECT id, name, price, category FROM products WHERE name LIKE :search OR category LIKE :search ORDER BY name LIMIT 12 "; $stmt = $pdo->prepare($sql); $stmt->execute(['search' => "%$term%"]); $results = $stmt->fetchAll(PDO::FETCH_ASSOC); // Format nice response $output = []; foreach ($results as $row) { $output[] = [ 'id' => $row['id'], 'label' => $row['name'] . " (" . $row['category'] . ")", 'value' => $row['name'], 'price' => number_format($row['price'], 2), 'category' => $row['category'] ]; } echo json_encode($output); exit; ?> |
Step 3: HTML + JavaScript frontend (AJAX part)
File: index.html
HTML
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 |
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0"/> <title>AJAX + PHP + MySQL – Live Search Example</title> <style> body { font-family: "Segoe UI", Arial, sans-serif; margin: 40px; background: #f9fafb; line-height: 1.6; color: #1f2937; } h1 { color: #111827; } .container { max-width: 700px; margin: 0 auto; } .search-box { position: relative; width: 100%; } #searchInput { width: 100%; padding: 14px 18px; font-size: 17px; border: 2px solid #d1d5db; border-radius: 8px; box-sizing: border-box; outline: none; } #searchInput:focus { border-color: #3b82f6; box-shadow: 0 0 0 3px rgba(59,130,246,0.2); } #suggestions { position: absolute; top: 100%; left: 0; right: 0; background: white; border: 1px solid #d1d5db; border-top: none; border-radius: 0 0 8px 8px; max-height: 340px; overflow-y: auto; z-index: 1000; box-shadow: 0 10px 15px -3px rgba(0,0,0,0.1); display: none; } .suggestion-item { padding: 14px 18px; cursor: pointer; border-bottom: 1px solid #f3f4f6; } .suggestion-item:last-child { border-bottom: none; } .suggestion-item:hover, .suggestion-item:focus { background: #eff6ff; } .product-name { font-weight: 600; } .product-info { color: #6b7280; font-size: 0.95em; } #result { margin-top: 40px; padding: 24px; background: white; border-radius: 10px; box-shadow: 0 4px 6px -1px rgba(0,0,0,0.1); min-height: 120px; } .loading { color: #6b7280; font-style: italic; } </style> </head> <body> <div class="container"> <h1>AJAX + PHP + MySQL Live Search</h1> <p>Type product name or category (minimum 2 characters)</p> <div class="search-box"> <input type="text" id="searchInput" placeholder="Search products..." autocomplete="off"/> <div id="suggestions"></div> </div> <div id="result">Start typing to search...</div> </div> <script> const searchInput = document.getElementById('searchInput'); const suggestionsDiv = document.getElementById('suggestions'); const resultDiv = document.getElementById('result'); let debounceTimer; searchInput.addEventListener('input', function() { clearTimeout(debounceTimer); const term = this.value.trim(); // Hide suggestions if input is too short if (term.length < 2) { suggestionsDiv.style.display = 'none'; resultDiv.innerHTML = 'Start typing to search...'; return; } debounceTimer = setTimeout(() => { fetchProducts(term); }, 350); // 350ms debounce - feels responsive but avoids too many requests }); // Hide suggestions when clicking outside document.addEventListener('click', function(e) { if (!searchInput.contains(e.target) && !suggestionsDiv.contains(e.target)) { suggestionsDiv.style.display = 'none'; } }); async function fetchProducts(term) { resultDiv.innerHTML = '<div class="loading">Searching...</div>'; try { const response = await fetch(`search.php?term=${encodeURIComponent(term)}`); if (!response.ok) { throw new Error(`HTTP {response.status}`); } const data = await response.json(); if (data.length === 0) { resultDiv.innerHTML = `<strong>No products found for:</strong> "${term}"`; suggestionsDiv.style.display = 'none'; return; } // Show suggestions dropdown let html = ''; data.forEach(item => { html += ` <div class="suggestion-item" onclick="selectProduct('${item.value}', ${item.id})" tabindex="0"> <div class="product-name">${item.label}</div> <div class="product-info"> ₹${item.price} • ${item.category} </div> </div> `; }); suggestionsDiv.innerHTML = html; suggestionsDiv.style.display = 'block'; resultDiv.innerHTML = `<strong>Found ${data.length} product(s)</strong>`; } catch (err) { resultDiv.innerHTML = `<div class="error">Error: ${err.message}</div>`; suggestionsDiv.style.display = 'none'; } } function selectProduct(name, id) { searchInput.value = name; suggestionsDiv.style.display = 'none'; resultDiv.innerHTML = ` <strong>Selected product:</strong><br><br> <strong>Name:</strong> ${name}<br> <strong>ID:</strong> ${id}<br> <em>You can now do something with this selection (add to cart, show details, etc.)</em> `; } </script> </body> </html> |
Summary – What we built & learned
- Real AJAX live search connected to MySQL
- Used modern fetch + async/await
- Debouncing (wait 350 ms after typing stops)
- Show/hide suggestions dropdown
- Click suggestion → fill input & show result
- Basic error handling
- Security note: always use prepared statements (:search) — never concatenate strings directly!
Next possible lessons
Tell me what you want next:
- Add loading spinner animation
- Show “No results found” nicely
- Click outside to close suggestions
- Show product details in modal after selection
- Add to cart functionality (session or database)
- Pagination (“Show more” button)
- Search with multiple fields (name + category + price range)
Just tell me which direction you want to go! 😊
