In this tutorial, we’ll create a dynamic HTML table that allows users to edit existing entries and insert new entries into a MySQL database. This feature is particularly useful for applications requiring user interaction with tabular data, such as employee management systems, inventory systems, and more.
Overview
Dynamic tables are a powerful feature in web applications, allowing users to interact directly with the data displayed on the front end. By leveraging PHP for backend processing and JavaScript for client-side interactions, we can create a responsive and user-friendly interface for managing data.
In this post, we’ll walk through the following:
- Setting up a sample employee table in MySQL.
- Displaying the employee data in an HTML table using PHP.
- Implementing JavaScript to enable table editing and adding new entries.
- Writing PHP scripts to update and insert data into the database.
- Conclusion and further improvements.
Table Structure and DDL in MySQL
Let’s start by creating a sample employee table in MySQL. This table will store basic information about employees, including their ID, name, email, position, department, and salary.
CREATE TABLE `employees` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`position` VARCHAR(50) NOT NULL,
`department` VARCHAR(50) NOT NULL,
`salary` DECIMAL(10, 2) NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
Page to Display the HTML Table
We will create a PHP page to query data from the MySQL table and display it in an HTML table. We’ll also include buttons for adding new rows.
PHP Code to Query and Display Data
<?php
require_once('db_connection.php'); // Include your database connection script
$sql_get_all_employees = "SELECT * FROM employees";
$result = $conn->query($sql_get_all_employees);
if (!$result) {
echo 'Error: ' . $conn->error;
exit();
}
?>
<!DOCTYPE html>
<html>
<head>
<title>Manage Employees</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" type="text/css" href="https://www.w3schools.com/w3css/4/w3.css">
<link rel="stylesheet" type="text/css" href="https://www.w3schools.com/lib/w3-theme-indigo.css">
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<style>
[contenteditable="true"] {
background-color: #f9f9f9;
border: 1px solid #ddd;
padding: 5px;
min-width: 100px;
}
[contenteditable="true"]:focus {
outline: 2px solid #4caf50;
}
.new-row td {
background-color: #e7f4e4;
}
</style>
</head>
<body class="w3-theme-d5">
<div class="w3-container w3-center w3-padding">
<h3>Employee Management</h3>
<button class="w3-button w3-theme-l2 w3-margin-bottom" id="addRowButton">Add New Employee</button>
<table class="w3-table w3-bordered" id="employeesTable">
<tr>
<th>Name</th>
<th>Email</th>
<th>Position</th>
<th>Department</th>
<th>Salary</th>
</tr>
<?php while ($employee = $result->fetch_assoc()) { ?>
<tr data-id="<?= $employee['id'] ?>">
<td contenteditable="true"><?= htmlspecialchars($employee['name']) ?></td>
<td contenteditable="true"><?= htmlspecialchars($employee['email']) ?></td>
<td contenteditable="true"><?= htmlspecialchars($employee['position']) ?></td>
<td contenteditable="true"><?= htmlspecialchars($employee['department']) ?></td>
<td contenteditable="true"><?= htmlspecialchars($employee['salary']) ?></td>
</tr>
<?php } ?>
</table>
</div>
</body>
</html>
JavaScript for Editing and Adding Rows
We will use JavaScript to detect changes in table cells and send updates to the server. Additionally, we will add functionality to insert new rows.
JavaScript Code
<script>
$(document).ready(function() {
let originalValue;
$('#employeesTable').on('focus', '[contenteditable="true"]', function() {
// Store the original value when the cell gains focus
originalValue = $(this).text();
});
$('#employeesTable').on('blur', '[contenteditable="true"]', function() {
var $cell = $(this);
var newValue = $cell.text();
// Only proceed if the new value is different from the original value
if (newValue !== originalValue) {
var $row = $cell.closest('tr');
var id = $row.data('id');
var column = $cell.index();
$.ajax({
url: 'update_employee.php',
type: 'POST',
data: {
id: id,
column: column,
value: newValue
},
success: function(response) {
console.log(response);
},
error: function(xhr, status, error) {
console.error(xhr.responseText);
}
});
}
});
$('#addRowButton').click(function() {
var $newRow = $('<tr class="new-row">' +
'<td contenteditable="true"></td>' +
'<td contenteditable="true"></td>' +
'<td contenteditable="true"></td>' +
'<td contenteditable="true"></td>' +
'<td contenteditable="true"></td>' +
'</tr>');
$('#employeesTable').append($newRow);
// Automatically focus the first cell of the new row
$newRow.find('td:first').focus();
// Listen for blur on new row to add it to the database
$newRow.on('blur', 'td', function() {
var $cell = $(this);
var newValue = $cell.text();
// If it's the first time we're saving this new row
if (!$newRow.data('isSaved')) {
var newRowData = [];
$newRow.find('td').each(function() {
newRowData.push($(this).text());
});
$.ajax({
url: 'insert_employee.php',
type: 'POST',
data: {
name: newRowData[0],
email: newRowData[1],
position: newRowData[2],
department: newRowData[3],
salary: newRowData[4]
},
success: function(response) {
console.log(response);
response = JSON.parse(response);
// Set the new row id based on the response
if (response.success) {
$newRow.data('id', response.id);
$newRow.removeClass('new-row'); // Reset class
$newRow.data('isSaved', true); // Mark row as saved
} else {
alert('Error adding row: ' + response.message);
}
},
error: function(xhr, status, error) {
console.error(xhr.responseText);
}
});
}
});
});
});
</script>
PHP Scripts for Editing and Inserting Records
We’ll create two separate PHP scripts to handle AJAX requests for updating existing records and inserting new ones.
PHP Script for Updating Records (update_employee.php)
<?php
require_once('db_connection.php'); // Include your database connection script
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
$id = $_POST['id'];
$column = $_POST['column'];
$value = $_POST['value'];
$columns = ['name', 'email', 'position', 'department', 'salary'];
if (!isset($columns[$column])) {
echo 'Invalid column';
exit();
}
$columnName = $columns[$column];
$sql = "UPDATE employees SET $columnName = ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param('si', $value, $id);
if ($stmt->execute()) {
echo 'Success';
} else {
echo 'Error: ' . $stmt->error;
}
}
?>
PHP Script for Inserting New Records (insert_employee.php)
<?php
require_once('db_connection.php'); // Include your database connection script
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
// Get the new row data from POST request
$name = $_POST['name'];
$email = $_POST['email'];
$position = $_POST['position'];
$department = $_POST['department'];
$salary = $_POST['salary'];
// Validate required fields
if (empty($name
) || empty($email) || empty($position) || empty($department) || empty($salary)) {
echo json_encode(['success' => false, 'message' => 'All fields are required.']);
exit();
}
// Prepare and execute the insert statement
$sql = "INSERT INTO employees (name, email, position, department, salary) VALUES (?, ?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$stmt->bind_param('ssssd', $name, $email, $position, $department, $salary);
if ($stmt->execute()) {
echo json_encode(['success' => true, 'id' => $stmt->insert_id]);
} else {
echo json_encode(['success' => false, 'message' => 'Error: ' . $stmt->error]);
}
}
?>
Here is the whole codes together:
https://github.com/subhadipm/dynamic-html-table
Conclusion
By following this tutorial, you can create a dynamic HTML table that enables users to edit existing records and add new entries, with changes being reflected in the backend MySQL database. This approach can be adapted to a variety of applications, such as inventory management systems, user management interfaces, and more.
Further improvements can include implementing more advanced validation, error handling, and security measures like input sanitization and CSRF protection.
This blog post provides a solid foundation for creating dynamic tables, enhancing the interactivity and functionality of your web applications. Feel free to experiment and expand upon the concepts presented here to suit your specific project needs.