数据库：PHP实验报告

最新推荐文章于 2024-05-05 20:02:52 发布

起床悠悠

最新推荐文章于 2024-05-05 20:02:52 发布

阅读量708

点赞数 5

分类专栏：数据库 php 文章标签：数据库 sql mysql php

本文链接：https://blog.csdn.net/gyeolhada/article/details/135776412

版权

数据库同时被 2 个专栏收录

3 篇文章 0 订阅

订阅专栏

php

1 篇文章 0 订阅

订阅专栏

本文详细描述了如何使用SQLDDL语句创建六个相关表格，并通过PHP实现TableList.php、show_tables.php、INSERT.php、DELETE.php和CHANGE.php页面，展示数据库表的操作，包括查询、插入、更新和删除记录。

摘要由CSDN通过智能技术生成

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）页面效果：