Below is a complete example including the creation of a MySQL table, insertion of sample data, and the code to fetch and display records in a DataTable:
1. Create a MySQL table
2. HTML file (index.html)
3. JavaScript file (fetch_data.js)
4. PHP file (fetch_data.php)
5. Run your code on localhost
1. Create a MySQL table:
Create a MySQL table with some sample data
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL
);
INSERT INTO users (username, email) VALUES
('john_doe', 'john.doe@example.com'),
('jane_smith', 'jane.smith@example.com'),
('bob_jones', 'bob.jones@example.com');2. HTML file (index.html)
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>DataTables Example</title>
<!-- Include jQuery -->
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<!-- Include DataTables CSS and JS -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.13.7/css/jquery.dataTables.min.css">
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
</head>
<body>
<!-- Your DataTable container -->
<table id="userTable" class="display">
<thead>
<tr>
<th>ID</th>
<th>Username</th>
<th>Email</th>
</tr>
</thead>
<tbody></tbody>
</table>
<!-- Include your custom script -->
<script src="fetch_data.js"></script>
</body>
</html>3. JavaScript file (fetch_data.js)
$(document).ready(function () {
// DataTable initialization
$('#userTable').DataTable({
"ajax": {
"url": "fetch_data.php", // PHP script to fetch data
"dataSrc": ""
},
"columns": [
{ "data": "id" },
{ "data": "username" },
{ "data": "email" }
]
});
});4. PHP file (fetch_data.php)
<?php
// Database connection parameters
$servername = "your_server_name";
$username = "your_username";
$password = "your_password";
$dbname = "your_database_name";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to fetch records
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);
// Fetch data and encode as JSON
$data = [];
while ($row = $result->fetch_assoc()) {
$data[] = $row;
}
echo json_encode($data);
// Close connection
$conn->close();
?>5. Run your code:
Now You can run your code on your localhost machine
http://localhost/project_folder_name
