Author
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.
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:
get_user.php.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:
fetch() API.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
%>