小明: 嗨，小华，我们最近在做一个校友信息管理系统，你能帮忙实现一些学生管理的功能吗？
小华: 当然可以。我们可以从数据库设计开始，首先创建一个students表。
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "alumni_system";
// 创建连接
$conn = new mysqli($servername, $username, $password, $dbname);
// 检测连接
if ($conn->connect_error) {
die("连接失败: " . $conn->connect_error);
}
// SQL语句
$sql = "CREATE TABLE students (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
phone VARCHAR(15),
graduation_year YEAR
)";
if ($conn->query($sql) === TRUE) {
echo "Table students created successfully";
} else {
echo "创建表时出错: " . $conn->error;
}
$conn->close();
?>
小明: 看起来不错！接下来是如何添加学生信息呢？
<?php
function addStudent($name, $email, $phone, $graduation_year) {
global $conn;
// 准备SQL语句
$stmt = $conn->prepare("INSERT INTO students (name, email, phone, graduation_year) VALUES (?, ?, ?, ?)");
$stmt->bind_param("sssi", $name, $email, $phone, $graduation_year);
if ($stmt->execute() === TRUE) {
return "新记录插入成功";
} else {
return "插入错误: " . $stmt->error;
}
$stmt->close();
}
echo addStudent('张三', 'zhangsan@example.com', '1234567890', 2020);
?>
小明: 这个函数看起来很棒！那么如何查询学生信息呢？
<?php
function getStudentById($id) {
global $conn;
$stmt = $conn->prepare("SELECT * FROM students WHERE id=?");
$stmt->bind_param("i", $id);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) {
return $row;
}
} else {
return "没有找到该学生的信息";
}
$stmt->close();
}
echo json_encode(getStudentById(1));
?>
小明: 太好了！最后，我们如何更新学生的信息呢？
<?php
function updateStudent($id, $name, $email, $phone, $graduation_year) {
global $conn;
$stmt = $conn->prepare("UPDATE students SET name=?, email=?, phone=?, graduation_year=? WHERE id=?");
$stmt->bind_param("ssssi", $name, $email, $phone, $graduation_year, $id);
if ($stmt->execute() === TRUE) {
return "记录更新成功";
} else {
return "更新错误: " . $stmt->error;
}
$stmt->close();
}
echo updateStudent(1, '李四', 'lisi@example.com', '0987654321', 2021);
?>