Use the following SQL DDL statements to create the six tables required for this project. Note that you need to use the exact statements as shown below to ensure that the instructor can test your programs using the instructor’s data later. Please also note that the tables are created in certain order such that by the time when a foreign key needs to be created, the corresponding primary key has already been created.
1.创建相关表格：
（1）创建employees表格：
（2）创建customers表格：
（3）创建suppliers表格：
（4）创建products表格：
（5）创建purchases表格：
（6）创建logs表格：
（7）向表格中加入具体数据：
2.创建TableList.php实现一个页面显示所有表格：
（1）php代码
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>显示所有表格</title>
<style>
body {
font-family: 'Arial', sans-serif;
margin: 20px;
padding: 20px;
border: 1px solid #ccc;
border-radius: 5px;
width: 200px; /* 设置页面宽度 */
height: 320px; /* 设置页面高度 */
}
h2 {
color: #333;
}
a {
text-decoration: none;
color: #0066cc;
}
table {
border-collapse: collapse;
width: 100%;
margin-top: 20px;
}
th, td {
border: 1px solid #ddd;
padding: 8px;
text-align: center;
}
th {
background-color: #f2f2f2;
}
</style>
</head>
<body>
<?php
include("conn.php");
mysqli_query($conn, "set names utf8");
echo "<h2>显示所有的表格</h2>";
$tablesQuery = "SHOW TABLES";
$tablesResult = mysqli_query($conn, $tablesQuery) or die(mysqli_error($conn));
while ($tableRow = mysqli_fetch_row($tablesResult)) {
$tableName = $tableRow[0];
echo "<p><a href='show_tables.php?table=$tableName'>$tableName</a> |
<a href='INSERT.php?table=$tableName'>INSERT</a></p>";
}
?>
</body>
</html>
（2）页面效果
3.创建show_tables.php实现用户点击某个表名，显示该表的所有信息：
（1）php代码
<?php
include("conn.php");
mysqli_query($conn, "set names gb2312");
if (isset($_GET['table'])) {
$tableName = $_GET['table'];
echo "<h2>Contents of Table: $tableName</h2>";
$query = "SELECT * FROM $tableName";
$res = mysqli_query($conn, $query) or die(mysqli_error($conn));
if (mysqli_num_rows($res) > 0) {
echo "<table border='1'>";
echo "<tr>";
$columns = mysqli_fetch_fields($res);
foreach ($columns as $column) {
echo "<th>{$column->name}</th>";
}
echo "<th>操作</th>";
echo "</tr>";
while ($dbrow = mysqli_fetch_assoc($res)) {
echo "<tr>";
foreach ($dbrow as $value) {
echo "<td>$value</td>";
}
echo "<td>";
echo "<form id='updateForm_{$dbrow['id']}' style='display: none;'>";
foreach ($columns as $column) {
echo "<label>{$column->name}:</label>";
echo "<input type='text' name='{$column->name}' value='{$dbrow[$column->name]}' required><br>";
}
echo "<button type='button' onclick=\"saveUpdate('$tableName', {$dbrow['id']})\">Save</button>";
echo "<button type='button' onclick=\"cancelUpdate('updateForm_{$dbrow['id']}')\">Cancel</button>";
echo "</form>";
echo "<button onclick=\"editRecord('updateForm_{$dbrow['id']}')\">EDIT</button>";
echo "<button onclick=\"deleteRecord('$tableName', {$dbrow['id']})\">DELETE</button>";
echo "</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "No records found in the table.";
}
} else {
echo "Table not specified.";
}
mysqli_close($conn);
?>
<script>
function editRecord(formId) {
document.getElementById(formId).style.display = 'block';
}
function saveUpdate(tableName, recordId) {
var formData = new FormData(document.getElementById('updateForm_' + recordId));
fetch('update_record.php?table=' + tableName + '&id=' + recordId, {
method: 'POST',
body: formData,
})
.then(response => response.json())
.then(data => {
if (data.success) {
alert('Record updated successfully!');
location.reload();
} else {
alert('Error updating record: ' + data.message);
}
})
.catch(error => console.error('Error:', error));
}
function cancelUpdate(formId) {
document.getElementById(formId).style.display = 'none';
}
function deleteRecord(tableName, recordId) {
if (confirm('Are you sure you want to delete this record?')) {
fetch('delete_record.php?table=' + tableName + '&id=' + recordId)
.then(response => response.json())
.then(data => {
if (data.success) {
alert('Record deleted successfully!');
location.reload();
} else {
alert('Error deleting record: ' + data.message);
}
})
.catch(error => console.error('Error:', error));
}
}
</script>
（2）页面效果
4.创建DELETE.php完成删除记录：
（1）php代码：
<?php
include("conn.php");
mysqli_query($conn, "set names gb2312");
if ($_SERVER["REQUEST_METHOD"] === "GET") {
if (isset($_GET['table']) && isset($_GET['id'])) {
$tableName = $_GET['table'];
$id = $_GET['id'];
$query = "DELETE FROM $tableName WHERE id = $id";
$result = mysqli_query($conn, $query);
if ($result) {
echo json_encode(['success' => true]);
} else {
echo json_encode(['success' => false, 'message' => '数据库中删除记录时发生错误']);
}
mysqli_close($conn);
} else {
echo json_encode(['success' => false, 'message' => '参数无效']);
}
} else {
echo json_encode(['success' => false, 'message' => '无效的请求方法']);
}
?>
（2）页面效果：
删除后的表格：
5.创建CHANGE.php来完成修改记录：
（1）php代码：
<?php
include("conn.php");
mysqli_query($conn, "set names gb2312");
if ($_SERVER["REQUEST_METHOD"] === "POST") {
if (isset($_POST['table']) && isset($_POST['id'])) {
$tableName = $_POST['table'];
$id = $_POST['id'];
$updateValues = [];
foreach ($_POST as $key => $value) {
if ($key != 'table' && $key != 'id') {
$updateValues[] = "$key = '" . mysqli_real_escape_string($conn, $value) . "'";
}
}
$query = "UPDATE $tableName SET " . implode(", ", $updateValues) . " WHERE id = $id";
$result = mysqli_query($conn, $query);
if ($result) {
echo json_encode(['success' => true]);
} else {
echo json_encode(['success' => false, 'message' => '数据库中更新记录时发生错误']);
}
mysqli_close($conn);
} else {
echo json_encode(['success' => false, 'message' => '参数无效']);
}
} else {
echo json_encode(['success' => false, 'message' => '无效的请求方法']);
}
?>
（2）页面效果：
修改后的表格为：
6.创建INSERT.php文件实现插入效果：
（1）php代码：
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>添加EMPLOYEE信息</title>
<style>
form {
padding: 0px;
margin: 0px;
}
</style>
</head>
<body>
<table width="70%" height="30" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td align="center">请填写要添加EMPLOYEE的信息</td>
</tr>
</table>
<form action="save_edit.php" method="post">
<table width="70%" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="30%" align="right">cid</td>
<td width="70%" align="left"><input type="text" name="cid" size="30" /></td>
</tr>
<tr>
<td width="30%" align="right">cname</td>
<td width="70%" align="left"><input type="text" name="cname" size="30" /></td>
</tr>
<tr>
<td width="30%" align="right">city</td>
<td width="70%" align="left"><input type="text" name="city" size="30" /></td>
</tr>
<tr>
<td width="30%" align="right">visits_made</td>
<td width="70%" align="left"><input type="text" name="visits_made" size="30" /></td>
</tr>
<tr>
<td width="30%" align="right">last_visit_time</td>
<td width="70%" align="left"><input type="text" name="last_visit_time" size="30" /></td>
</tr>
</table>
<table width="70%" height="30" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td align="center"><input type="submit" name="submit1" value="确定添加" /></td>
</tr>
</table>
</form>
</body>
</html>
（2）页面效果：
