一、 整体要求
我们要构建一套大学学生管理系统，用来管理学生的基本信息、学生的课业信息以及学生在学校除学习外的一些相关信息。本次PHP作业基于上次数据库作业构建的表结构来进行系统开发。
二、 详细要求
1. 菜单
每个页面顶部放置两个菜单：
学生列表：对应学生管理>学生列表页面
课程列表：对应课程管理>课程列表页面
2. 学生管理
1） 学生列表
开发一个页面，提供学生列表展示功能，展示系统里面的学生信息。主要展示：学生学号、学生姓名、学生性别、学生年龄、学生籍贯、学生所在学院、学生班级。
页面最上部放置一个“添加”按钮，点击之后跳转到学生添加页面。
页面提供查询功能，可以根据学院、班级来进行精确查询，同时可以根据姓名进行模糊查询。查询表单放置在列表上部。
每条学生信息后面增加一列，该列里面包含三个按钮：
我的课程按钮：点击之后进入课程管理>我的课程页面
修改按钮：点击之后执行学生修改操作
删除按钮：点击之后执行学生删除操作
2） 学生添加
开发一个页面，提供学生添加功能。页面主体为一个表单，要求可以填写学生学号、学生姓名、学生籍贯，同时选择学生性别、学生生日、学生班级信息。
提交之后，执行添加学生功能，添加成功之后，跳转到学生列表页面。
3） 学生修改
开发一个页面，提供学生修改功能。进入页面之后，根据学生id获取到学生信息，然后把学生信息填充到表单里面，要求可以修改学生籍贯、学生生日，其余信息不可修改。
提交之后，执行修改学生信息功能，修改成功之后，跳转到学生列表页面。
4） 学生删除
开发一个页面功能，提供学生删除功能，删除操作使用逻辑删除（将学生状态改为0，而不是删除整条数据）。删除成功后，跳转到学生列表页面。
3. 课程管理
1） 课程列表
开发一个页面，提供课程列表展示功能，展示系统里面的课程信息。主要展示：学院、学期、课程、学分。
页面提供查询功能，可以根据学院、学期来进行精确查询，同时可以根据课程名进行模糊查询。查询表单放置在列表上部。
2） 我的课程
开发一个页面，提供某个学生所修全部课程展示功能，主要展示：学生姓名、课程、学期、成绩或学分、课程状态（是否挂科）。
4.实现代码
1)student_info.php
<?php $dsn = ''; $user = ''; $password = ''; try { $dbh = new PDO($dsn,$user,$password); } catch(PDOException $e){ echo 'Connection failed:'.$e->getMessage(); } $sql = "SELECT student_id,student_name,sex,birthday,birth_place,class_name,college_name FROM students as a join classes as b on a.class = b.class_id join colleges as c on b.college_id = c.college_id where a.state = 1"; if($_SERVER['REQUEST_METHOD'] == 'GET') { $statement = $dbh->prepare($sql); $statement->execute(); } $query1="select college_id,college_name from colleges"; $colleges =$dbh->prepare($query1); $colleges->execute(); $query2="select class_id,class_name from classes"; $classes =$dbh->prepare($query2); $classes->execute(); if($_SERVER['REQUEST_METHOD'] == 'POST' ) { $data1=array(); if (isset($_POST["sname"])) { $student_name = $_POST["sname"]; if($student_name !== '') { $sql .= " AND student_name like ?"; $data1[] = "%$student_name%"; } } if (isset($_POST["co_name"])) { $college_id = $_POST["co_name"]; if($college_id!=0) { $sql .= " AND c.college_id= ?"; $data1[]=$college_id; } } if (isset($_POST["cname"])) { $class_id = $_POST["cname"]; if($class_id!=0) { $sql .= " AND b.class_id = ?"; $data1[]=$class_id; } } $statement = $dbh->prepare($sql); $statement->execute($data1); echo $sql; } ?> <html> <style type="text/css"> <!-- body { margin: 0; padding: 0; } #menu { background:silver; margin:5px; height:30px; padding-left:30px; } #box1 { background:deepskyblue; margin:5px; padding-left:500px; } #box2 { background:cornflowerblue; margin:5px; padding-left:30px; } #tip { background:blue; margin:5px; padding-left:700px; } --> </style> <body> <div id="menu"> <a href="student_info1.php"><button style="background:red">学生管理</button></a><a href="course_info1.php"><button style="background:blue">课程管理</button></a><br><br> </div> <div id="tip"> <a href="student_add1.php"><button>添加学生</button></a><br><br> </div> <div id="box1"> <form method="post" action="student_info1.php"> 精确查询 学院: <select name="co_name"> <option value="0">请选择学院</option> <?php while ($co = $colleges->fetch()) { $attr=''; if($co['college_id']==$_POST['co_name']){ $attr='selected="selected"'; } echo '<option value="'.$co['college_id'].'"'.$attr.'>'.$co['college_name'].'</option>'; } ?> </select> 班级名称: <select name="cname"> <option value="0">请选择班级</option> <?php while ($c = $classes->fetch()) { $attr=''; if($c['class_id']==$_POST['cname']){ $attr='selected="selected"'; } echo '<option value="'.$c['class_id'].'"'.$attr.'>'.$c['class_name'].'</option>'; } ?> </select> <br><br> 模糊查询 学生姓名: <input type="text" name="sname" value="<?php if(isset($_POST["sname"])){echo $student_name;}?>" placeholder="点击输入"> <br><br> <input type="submit" value="查询"> </form> </div> <div id="box2"> <h1>学生总表</h1> <table border="1"> <tr> <th>学号</th> <th>姓名</th> <th>性别</th> <th>生日</th> <th>籍贯</th> <th>班级</th> <th>学院</th> <th>我的课程</th> <th>修改</th> <th>删除</th> </tr> <?php while ($row = $statement->fetch()) { ?> <tr> <td><?php echo $row['student_id']?></td> <td><?php echo $row['student_name']?></td> <td><?php echo $row['sex']?></td> <td><?php echo $row['birthday']?></td> <td><?php echo $row['birth_place']?></td> <td><?php echo $row['class_name']?></td> <td><?php echo $row['college_name']?></td> <td> <a href="student_courses1.php?student_id=<?php echo $row['student_id']?>"> <button>点击</button></a> </td> <td> <a href="student_update1.php?student_id=<?php echo $row['student_id'] ?>"> <button>修改</button></a> </td> <td> <a href="student_delete1.php?student_id=<?php echo $row['student_id'] ?>"> <button>删除</button></a> </td> </tr> <?php } ?> </table> </div> </body> </html>
2)student_add.php
<?php $dsn = ''; $user = ''; $password = ''; try { $dbh = new PDO($dsn,$user,$password); } catch(PDOException $e){ echo 'Connection failed:'.$e->getMessage(); } $query2="select class_id,class_name from classes"; $classes =$dbh->prepare($query2); $classes->execute(); if($_SERVER['REQUEST_METHOD'] == 'POST' ){ if (isset($_POST["id"])) { $student_id = $_POST["id"]; } if (isset($_POST["name"])) { $student_name = $_POST["name"]; } if (isset($_POST["sex"])) { $sex = $_POST["sex"]; } if (isset($_POST["birth_place"])) { $birth_place = $_POST["birth_place"]; } if (isset($_POST["birthday"])) { $birthday = $_POST["birthday"]; } if (isset($_POST["class"])) { $class = $_POST["class"]; } $sql = "insert into students(student_id,student_name,sex,birthday,birth_place,class) values (:v1,:v2,:v3,:v4,:v5,:v6)"; $statement = $dbh->prepare($sql); $sql_data1 = Array( ":v1" => $student_id, ":v2" => $student_name, ":v3" => $sex, ":v4" => $birthday, ":v5" => $birth_place, ":v6" => $class ); //echo $result->queryString; $statement->execute($sql_data1); echo '添加成功，3秒后跳转'; header("Refresh:3;url=student_info1.php"); } ?> <html> <style type="text/css"> <!-- body { margin: 0; padding: 0; } #menu { background:silver; margin:5px; height:30px; padding-left:30px; } #box1 { background:deepskyblue; margin:5px; padding-left:30px; } #box2 { background:cornflowerblue; margin:5px; padding-left:30px; } --> </style> <body> <div id="menu"> <a href="student_info1.php"><button style="background:red">学生管理</button></a><a href="course_info1.php"><button style="background:blue">课程管理</button></a><br><br> </div> <div id="box1"> <h1>学生添加</h1> <form method="post" action="student_add1.php"> <table border="1"> <tr> <td>学号</td> <td> <input type="text" name="id" value=""/> </td> </tr> <tr> <td>姓名</td> <td><input type="text" name="name" value=""></td> </tr> <tr> <td>籍贯</td> <td><input type="text" name="birth_place" value=""></td> </tr> <tr> <td>性别</td> <td> <input type="radio" value="男" name="sex">男 <input type="radio" vaule="女" name="sex">女 </td> </tr> <tr> <td>生日</td> <td><input type="date" name="birthday" value=""></td> </tr> <tr> <td>班级id</td> <td> <select name="class"> <option>--请选择--</option> <?php while ($c = $classes->fetch()) { $attr=''; if($c['class_id']==$_POST['cname']){ $attr='selected="selected"'; } echo '<option value="'.$c['class_id'].'"'.$attr.'>'.$c['class_name'].'</option>'; } ?> </select> </td> </tr> <tr> <td>学院</td> <td> <input type="text" name="college" value="根据班级名称自动填写" readonly> </td> </tr> <tr> <td colspan="2" align="center"><input type="submit" value="添加"></td> </tr> </table> </form> </div> </body> </html>
3)student_update.php
<?php $dsn = ''; $user = ''; $password = ''; try { $dbh = new PDO($dsn, $user, $password); } catch (PDOException $e) { echo 'Connection failed:' . $e->getMessage(); } @$id = $_GET['student_id']; $sql = "SELECT student_id,student_name,sex,birthday,birth_place,class_name,college_name FROM students as a join classes as b on a.class = b.class_id join colleges as c on b.college_id = c.college_id where a.state = :state and a.student_id = :id"; $statement = $dbh->prepare($sql); $sql_data1 = Array( ":state" => 1, ":id" => $id ); $statement->execute($sql_data1); while($row=$statement->fetch()){ $var1=$row['student_id']; $var2=$row['student_name']; $var3=$row['sex']; $var4=$row['birthday']; $var5=$row['birth_place']; $var6=$row['class_name']; $var7=$row['college_name']; } if($_SERVER['REQUEST_METHOD'] == 'POST' ) { if (isset($_POST["id"])) { $id=$_POST['id']; echo $id; } if (isset($_POST["name"])) { $name=$_POST['name']; } if (isset($_POST["birth_place"])) { $birth_place=$_POST["birth_place"]; echo $birth_place; } if (isset($_POST["birthday"])) { $birthday=$_POST["birthday"]; echo $birthday; } $sql1 = "update students set birth_place=:v1,birthday=:v2 where student_id=:v3"; $statement1=$dbh->prepare($sql1); $sql_data2 = Array( ":v1" => $birth_place, ":v2" => $birthday, ":v3" => $id, ); $statement1->execute($sql_data2); header("Location:student_info1.php"); } ?> <html> <style type="text/css"> <!-- body { margin: 0; padding: 0; } #menu { background:silver; margin:5px; height:30px; padding-left:30px; } #box1 { background:deepskyblue; margin:5px; padding-left:30px; } --> </style> <body> <div id="menu"> <a href="student_info1.php"><button style="background:red">学生管理</button></a><a href="course_info1.php"><button style="background:blue">课程管理</button></a><br><br> </div> <h1>学生信息修改</h1> <form method="post" action="student_update1.php"> <table border="1"> <tr> <td>学号</td> <td> <input type="text" name="id" value="<?php echo $var1 ?>" readonly> </td> </tr> <tr> <td>姓名</td> <td> <input type="text" name="name" value="<?php echo $var2 ?>" readonly> </td> </tr> <tr> <td>性别</td> <td> <input type="text" name="sex" value="<?php echo $var3 ?>" readonly> </td> </tr> <tr> <td>籍贯</td> <td><input type="text" name="birth_place" value="<?php echo $var5 ?>"></td> </tr> <tr> <td>生日</td> <td><input type="datetime-local" name="birthday" value="<?php echo $var4 ?>"></td> </tr> <td>班级</td> <td> <input type="text" name="class" value="<?php echo $var6 ?>" readonly> </td> </tr> <td>学院</td> <td> <input type="text" name="college" value="<?php echo $var7?>" readonly> </td> </tr> <tr> <td colspan="2" align="center"><input type="submit" value="修改"></td> </tr> </table> </form> </body> </html>
4)student_delete.php
<html> <?php if($_SERVER['REQUEST_METHOD'] == 'GET') { $dsn = ''; $user = ''; $password = ''; try { $dbh = new PDO($dsn, $user, $password); } catch (PDOException $e) { echo 'Connection failed:' . $e->getMessage(); } @$id = $_GET['student_id']; $sql = "update students set state=0 where student_id=:v1"; $statement=$dbh->prepare($sql); $sql_data1 = Array( ":v1" => $id, ); $statement->execute($sql_data1); if($row=$statement->rowCount()) { echo '删除成功，3秒后跳转'; }else{ echo "删除失败!请检查删除id是否正确!"; echo "<br>"; echo "3秒后跳转回学生信息页面！"; } header("Refresh:3;url=student_info1.php"); } ?> </html>
5)course_info.php
<?php $dsn = ''; $user = ''; $password = ''; try { $dbh = new PDO($dsn,$user,$password); } catch(PDOException $e){ echo 'Connection failed:'.$e->getMessage(); } $sql = "SELECT college_name,term_name,course_name,credit FROM college_term_courses as a join colleges as b on a.college_id=b.college_id join terms as c on a.term_id=c.term_id join courses as d on a.course_id=d.course_id where 1=1"; if($_SERVER['REQUEST_METHOD'] == 'GET') { $statement = $dbh->prepare($sql); $statement->execute(); } $query1="select college_id,college_name from colleges"; $colleges =$dbh->prepare($query1); $colleges->execute(); $query2="select term_id,term_name from terms"; $terms =$dbh->prepare($query2); $terms->execute(); if($_SERVER['REQUEST_METHOD'] == 'POST' ) { $data=array(); if (isset($_POST["term"])) { $term_id = $_POST["term"]; if($term_id!=0) { $sql .= " AND c.term_id=?"; $data[]=$term_id; } } if (isset($_POST["co_name"])) { $college_id = $_POST["co_name"]; if($college_id!=0) { $sql .= " AND b.college_id=?"; $data[]=$college_id; } } if (isset($_POST["cname"])) { $course_name = $_POST["cname"]; if($course_name!=null) { $sql .= " AND course_name like ?"; $data[] = "%$course_name%"; } } $statement = $dbh->prepare($sql); $statement->execute($data); } ?> <html> <style type="text/css"> <!-- body { margin: 0; padding: 0; } #menu { background:silver; margin:5px; height:30px; padding-left:30px; } #box1 { background:deepskyblue; margin:5px; padding-left:500px; } #box2 { background:cornflowerblue; margin:5px; padding-left:30px; } --> </style> <body> <div id="menu"> <a href="student_info1.php"><button style="background:red">学生管理</button></a><a href="course_info1.php"><button style="background:blue">课程管理</button></a><br><br> </div> <div id="box1"> <form method="post" action="course_info1.php"> 精确查询 学院: <select name="co_name"> <option value="0">请选择学院</option> <?php while ($co = $colleges->fetch()) { $attr=''; if($co['college_id']==$_POST['co_name']){ $attr='selected="selected"'; } echo '<option value="'.$co['college_id'].'"'.$attr.'>'.$co['college_name'].'</option>'; } ?> </select> 学期名称: <select name="term"> <option value="0">请选择学期</option> <?php while ($t = $terms->fetch()) { $attr=''; if($t['term_id']==$_POST['term']){ $attr='selected="selected"'; } echo '<option value="'.$t['term_id'].'"'.$attr.'>'.$t['term_name'].'</option>'; } ?> </select> <br><br> 模糊查询 课程名: <input type="text" name="cname" value="<?php if(isset($_POST["cname"])){echo $course_name;}?>" placeholder="点击输入"> <br><br> <input type="submit" value="查询"> </form> </div> <div id="box2"> <h1>课程总表</h1> <table border="1"> <tr> <th>学院</th> <th>学期</th> <th>课程</th> <th>学分</th> </tr> <?php while ($row = $statement->fetch()) { ?> <tr> <td><?php echo $row['college_name']?></td> <td><?php echo $row['term_name']?></td> <td><?php echo $row['course_name']?></td> <td><?php echo $row['credit']?></td> </tr> <?php } ?> </table> </div> </body> </html>
6)student_course.php
<?php if($_SERVER['REQUEST_METHOD'] == 'GET') { $dsn = ''; $user = ''; $password = ''; try { $dbh = new PDO($dsn, $user, $password); } catch (PDOException $e) { echo 'Connection failed:' . $e->getMessage(); } @$id = $_GET['student_id']; $sql = "SELECT student_name,term_name,course_name,grade FROM student_courses AS a JOIN college_term_courses AS b ON a.id_c=b.id JOIN courses AS c ON b.course_id=c.course_id JOIN students AS d ON a.student_id=d.student_id JOIN terms AS e ON b.term_id=e.term_id where a.student_id=:1"; $statement=$dbh->prepare($sql); $sql_data1 = Array( ":1" => $id, ); $statement->execute($sql_data1); } ?> <html> <style type="text/css"> <!-- body { margin: 0; padding: 0; } #menu { background:silver; margin:5px; height:30px; padding-left:30px; } #box1 { background:deepskyblue; margin:5px; padding-left:30px; } --> </style> <body> <div id="menu"> <a href="student_info1.php"><button style="background:red">学生管理</button></a><a href="course_info1.php"><button style="background:blue">课程管理</button></a><br><br> </div> <div id="box1"> <h1>已修课程</h1> <table border="1"> <tr> <th>姓名</th> <th>学期</th> <th>课程</th> <th>成绩</th> <th>课程状态</th> </tr> <?php { while ($row = $statement->fetch()) { ?> <tr> <td><?php echo $row['student_name']?></td> <td><?php echo $row['term_name']?></td> <td><?php echo $row['course_name']?></td> <td><?php echo $row['grade']?></td> <td> <?php if($row[3]>=60){ echo '合格'; }else{ echo '挂科'; } ?> </td> <?php } } ?> </table> </div> </body> </html>
PHP总结
1.PHP代码放到最上面，最好不要PHP与html多次穿插着写
2.Phpstorm中提示的标横杠的标签为淘汰标签，尽量不要使用
3.命名时名称加上前缀、后缀，意思要清晰、明了
4.使用PDO连接数据库
$dbh = new PDO($dsn,$user,$password);
5.SQL使用PDO安全规范，防止SQL注入，应使用预处理语句，通过$_SERVER['REQUEST_METHOD']来判断传值方法
if($_SERVER['REQUEST_METHOD'] == 'POST' )
通过判断是否传值过来，来决定是否添加sql语句，SQL语句中使用占位符来替换数据值
if (isset($_POST["sname"])) {
$student_name = $_POST["sname"];
if($student_name!=null){
$sql .=" AND student_name like ?";
$data1[]="%$student_name%";
}
6.修改页面：应在数据库中传过来的id是否存在，若存在则查询该id号的学生信息并输出，若不存在则返回错误
7.student_info.php页面中，查询和学生总表的属性内容相同，可使用同一个列表来显示
8.查询时注意条目的可用性，即state是否等于1
9.查询框查询时，在输出查询结果后，查询框中仍应保留查询内容
10.value值最好使用id号，查询时会比较迅速
11.输入框的提示使用placeholder=""
12.页面应使用div划分块
13.从前端取数据$_SERVER[''] 、$_GET['']、$_POST['']
14.URL结构
http://www.abc.com/article/1.html/?ie=utf8×tamp=1111/#content
协议 域名 URI query 毛文本
15.PHP代码的大致内容
<?php
取参
执行查询
$articles=array
?>
16.不推荐使用@，a.可能会隐藏重要错误提醒，b.会降低代码的执行性能
17.select若条目很多，可在数据库中查询，循环输出
<select name="co_name">
<option value="0">请选择学院</option>
<?php
while ($co = $colleges->fetch()) {
$attr='';
if($co['college_id']==$_POST['co_name']){
$attr='selected="selected"';
}
echo '<option value="'.$co['college_id'].'"'.$attr.'>'.$co['college_name'].'</option>';
}
?>
</select>
18.查询框条件回显
<input type="text" name="sname" value="<?php if(isset($_POST["sname"])){echo $student_name;}?>" placeholder="点击输入">
19.删除界面对sql执行情况进行判断
20.isset();判断变量是否定义，若定义为null或未赋任何值则为false，若定义为0、''、""则为true
21.== 会进行类型转换，''等于null；=== 不进行类型转换， ''不等于null
22.!=与！==不一样