【PHP网页应用】MySQL数据库增删改查基础版

使用PHP编写一个简单的网页，实现对MySQL数据库的增删改和展示操作

页面实现在index.php，其中basic.php为没有css美化的原始人版本

函数实现在database.php

功能基本实现版

CSS美化版

basicindex.php

index.php

database.php

代码讲解

功能基本实现版

CSS美化版

我们来展示一下页面和操作示例。

运行网页，可以看到页面展示出了数据库的名字，还有数据库下的表。

我们点击其中一个表，可以看到表名和表的数据以及可以点击操作的按钮。

然后我们输入新的数据准备添加。

点击添加，可以看到添加成功的提示。

可以看到新添加的数据。

然后我们尝试修改数据，将visits_made改成2，点击更新。

可以看到visits_made已经变成2了。

然后我们删除这一行数据，点击删除。

可以看到我们刚刚添加的一行数据没了。

basicindex.php

<?php
include 'database.php';
global $primaryKey;
$tableNames=getTableNames();
echo '<h1>数据库表名</h1>';
foreach ($tableNames as $name){echo '<a href="?table=' . $name . '">' . $name . '</a><br>';
}
if (isset($_GET['table'])) {$tableName = $_GET['table'];setPrimaryKey($tableName);if (isset($_POST['add'])) {// 添加数据$data = $_POST;unset($data['add']);insertRecord($tableName, $data);} elseif (isset($_POST['delete'])) {// 删除数据$id = $_POST['id'];deleteRecord($tableName, $id);} elseif (isset($_POST['update'])) {// 修改数据$id = $_POST['id'];$data = $_POST;unset($data['update'], $data['id']);updateRecord($tableName, $id, $data);}$tableData = getTableData($tableName);
} else {$tableNames = getTableNames();
}
?><!DOCTYPE html>
<html>
<head><title>MySQL 数据库操作</title>
</head>
<body>
<?php if (isset($tableData)) { ?><h1><?php echo $tableName; ?></h1><table><tr><?php foreach (array_keys($tableData[0]) as $key) { ?><th><?php echo $key; ?></th><?php } ?><th>操作</th></tr><?php foreach ($tableData as $row) { ?><form method="post"><tr><?php foreach ($row as $key => $value) { ?><td><input type="text" name="<?php echo $key; ?>" value="<?php echo $value; ?>"></td><?php } ?><td><input type="hidden" name="id" value="<?php echo $row[$primaryKey]; ?>"><input type="submit" name="update" value="更新"><input type="submit" name="delete" value="删除"></td></tr></form><?php } ?><form method="post"><tr><?php foreach (array_keys($tableData[0]) as $key) { ?><td><input type="text" name="<?php echo $key; ?>" placeholder="<?php echo $key; ?>"></td><?php } ?><td><input type="submit" name="add" value="添加"></td></tr></form></table>
<?php }?>
</body>
</html>

index.php

<?php
include 'database.php';
global $primaryKey;
global $database;
$tableNames = getTableNames();
if (isset($_GET['table'])) {$tableName = $_GET['table'];setPrimaryKey($tableName);if (isset($_POST['add'])) {// 添加数据$data = $_POST;unset($data['add']);echo '<script>alert("'.insertRecord($tableName, $data).'")</script>';} elseif (isset($_POST['delete'])) {// 删除数据$key = $_POST['key'];echo '<script>alert("'.deleteRecord($tableName, $key).'")</script>';} elseif (isset($_POST['update'])) {// 修改数据$key = $_POST['key'];$data = $_POST;unset($data['update'], $data['key']);echo '<script>alert("'.updateRecord($tableName, $key, $data).'")</script>';}$tableData = getTableData($tableName);
}
?><!DOCTYPE html>
<html>
<head><title>数据库管理系统</title><style>body {font-family: Arial, sans-serif;margin: 0;padding: 0;background-color: #f5f5f5;}h2 {background-color: #333;color: #fff;text-align: center;padding: 10px;}table {border-collapse: collapse;width: 80%;margin: auto;background-color: #fff;}th, td {padding: 10px;text-align: center;}th {background-color: #333;color: #fff;}tr:nth-child(even) {background-color: #f2f2f2;}tr:hover {background-color: #ddd;}form {display: inline;}input {text-align: center;}input[type="text"] {width: 100%;padding: 5px;}input[type="submit"] {background-color: #333;color: #fff;border: none;padding: 5px 10px;cursor: pointer;}input[type="submit"]:hover {background-color: #555;}</style>
</head>
<body>
<h2>Database <?php echo $database ?></h2>
<table><?php foreach ($tableNames as $name) { ?><tr><td><?php echo '<a href="?table=' . $name . '">' . $name . '</a>'; ?></td></tr><?php } ?>
</table>
<?php if (isset($tableData)) { ?><h2 colspan=100%><?php echo 'Table ' . $tableName; ?></h2><table><tr><?php foreach (getTableHeader($tableName)as $key) { ?><th><?php echo $key; ?></th><?php } ?><th>操作</th></tr><?php if (!empty($tableData)) { ?><?php foreach ($tableData as $row) { ?><form method="post"><tr><?php foreach ($row as $key => $value) { ?><td><input type="text" name="<?php echo $key; ?>" value="<?php echo $value; ?>"></td><?php } ?><td><input type="hidden" name="key" value="<?php echo $row[$primaryKey]; ?>"><input type="submit" name="update" value="更新"><input type="submit" name="delete" value="删除"></td></tr></form><?php } ?><?php } ?><form method="post"><tr><?php foreach (getTableHeader($tableName) as $key) { ?><td><input type="text" name="<?php echo $key; ?>" placeholder="<?php echo $key; ?>"></td><?php } ?><td><input type="submit" name="add" value="添加"></td></tr></form></table>
<?php } ?>
</body>
</html>

database.php

<?php
$host = "localhost"; // 数据库主机
$username = "root"; // 数据库用户名
$password = ""; // 数据库密码
$database = "taobao"; // 数据库名称
$conn = new mysqli($host, $username, $password, $database);
$primaryKey = "";
if ($conn->connect_error) {  //数据库连接失败弹出提示echo '<script>alert("数据库连接失败")</script>';die(); //终止代码
}function setPrimaryKey($tableName)
{global $conn;global $primaryKey;$sql = "show index from $tableName";$result = mysqli_query($conn, $sql);$row = mysqli_fetch_assoc($result);$primaryKey = $row['Column_name'];
}function getTableNames()
{global $conn;$sql = "show tables";$result = $conn->query($sql);$tableNames = array();if ($result->num_rows > 0) {while ($row = $result->fetch_assoc()) {$tableNames[] = $row['Tables_in_' . $GLOBALS['database']];}}return $tableNames;
}function getTableData($tableName)
{global $conn;$sql = "select*from $tableName";$result = $conn->query($sql);$tableData = array();if ($result->num_rows > 0) {while ($row = $result->fetch_assoc()) {$tableData[] = $row;}}return $tableData;
}function insertRecord($tableName, $data)
{global $conn;$keys = implode(', ', array_keys($data)); // 连成字符串$values = "'" . implode("', '", array_values($data)) . "'";$sql = "insert into $tableName ($keys) values ($values)";$result = $conn->query($sql);if ($result == 1)return '添加成功';elsereturn '添加失败';
}function deleteRecord($tableName, $key)
{global $conn;global $primaryKey;$sql = "delete from $tableName where $primaryKey = '$key'";$result = $conn->query($sql);if ($result == 1)return '删除成功';elsereturn '删除失败';
}function updateRecord($tableName, $pkey, $data)
{global $conn;global $primaryKey;$set = "";foreach ($data as $key => $value) {$set .= "$key = '$value', ";}$set = rtrim($set, ', '); // 移除最后一个字符','$sql = "update $tableName set $set where $primaryKey = '$pkey'";$result = $conn->query($sql);if ($result == 1)return '修改成功';elsereturn '修改失败';
}function getTableHeader($tableName)
{global $conn;$sql = "describe $tableName";$result = $conn->query($sql);$tableHeader = array();if ($result) {while ($row = $result->fetch_assoc()) {$tableHeader[] = $row['Field'];}}return $tableHeader;
}?>