AJAX Database Integration

AJAX can be used to interact with databases (like MySQL or SQL Server) without reloading the webpage.

The process involves JavaScript sending a request to a server-side script (PHP, ASP, etc.), which then interacts with the database and sends back the result.

How It Works (Step-by-Step)

Client (Browser) — Uses JavaScript to send an AJAX request.
Server Script (PHP / ASP) — Receives the request and connects to a database (like MySQL).
Database — Executes the SQL query and returns data to the server script.
Server Script — Sends the data back to the client in text or JSON format.
Client (Browser) — Updates the webpage dynamically (without refreshing).

Example: AJAX + PHP + MySQL

1. Database Setup (MySQL)

Create a database and a table:

CREATE DATABASE ajax_demo;

USE ajax_demo;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]'),
       ('Jane Smith', '[email protected]');

2. HTML + JavaScript (Client Side)

<!DOCTYPE html>
<html>
<head>
  <title>AJAX Database Example</title>
</head>
<body>

<h3>Search User by Name:</h3>
<input type="text" id="name" placeholder="Enter name">
<button onclick="searchUser()">Search</button>

<p id="result"></p>

<script>
function searchUser() {
  const name = document.getElementById("name").value;

  const xhttp = new XMLHttpRequest();
  xhttp.onreadystatechange = function() {
    if (this.readyState === 4 && this.status === 200) {
      document.getElementById("result").innerHTML = this.responseText;
    }
  };

  xhttp.open("GET", "get_user.php?name=" + encodeURIComponent(name), true);
  xhttp.send();
}
</script>

</body>
</html>

3. PHP Script (Server Side – get_user.php)

<?php
// Database connection
$conn = new mysqli("localhost", "root", "", "ajax_demo");

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

// Get name from request
$name = $_GET['name'] ?? '';

// Query database
$sql = "SELECT * FROM users WHERE name LIKE '%$name%'";
$result = $conn->query($sql);

// Display results
if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
    echo "Name: " . $row["name"] . " - Email: " . $row["email"] . "<br>";
  }
} else {
  echo "No results found";
}

$conn->close();
?>

How It Works:

The user types a name and clicks Search.
JavaScript sends the request to get_user.php.
PHP queries the MySQL database for matching names.
The server returns the results as plain text.
JavaScript displays the results instantly — without page reload.

Example with JSON Response

For more advanced integration, return data in JSON format and handle it in JavaScript.

PHP (get_user_json.php)

<?php
$conn = new mysqli("localhost", "root", "", "ajax_demo");

$name = $_GET['name'] ?? '';

$sql = "SELECT * FROM users WHERE name LIKE '%$name%'";
$result = $conn->query($sql);

$users = [];

while($row = $result->fetch_assoc()) {
  $users[] = $row;
}

echo json_encode($users);

$conn->close();
?>

JavaScript (Client Side)

<script>
function searchUser() {
  const name = document.getElementById("name").value;

  fetch("get_user_json.php?name=" + encodeURIComponent(name))
    .then(response => response.json())
    .then(data => {
      let output = "";
      if (data.length > 0) {
        data.forEach(user => {
          output += `Name: ${user.name} - Email: ${user.email}<br>`;
        });
      } else {
        output = "No results found";
      }
      document.getElementById("result").innerHTML = output;
    })
    .catch(err => console.error("Error:", err));
}
</script>

Advantages of using JSON:

Easier to parse and format data.
Ideal for larger or structured responses.
Works smoothly with the modern fetch() API.

Example: AJAX with ASP and SQL Server

ASP file (get_user.asp):

<%
Dim conn, rs, name, sql
name = Request.QueryString("name")

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=ajax_demo;User ID=sa;Password=yourpassword;"

sql = "SELECT * FROM users WHERE name LIKE '%" & name & "%'"
Set rs = conn.Execute(sql)

If Not rs.EOF Then
  Do Until rs.EOF
    Response.Write "Name: " & rs("name") & " - Email: " & rs("email") & "<br>"
    rs.MoveNext
  Loop
Else
  Response.Write "No results found"
End If

rs.Close
conn.Close
%>