效果图:
一.方法概要
1.字符串拼接 implode
2.多表查询
3.关联数组键值对使用
二.详解:
1.插入数据时由于涉及多次操作考虑自写insert()函数,提高效率
function insert($table,$arr,$link)
{
$keys=implode(',',array_keys($arr));//根据逗号分割数组的key值
$vals="'".implode("'".','."'",array_values($arr))."'";//字符串拼接数组的value值
$sql="insert into {$table}({$keys}) values ({$vals})";
if(mysqli_query($link,$sql))
{
return 1;
}
else{
return 0;
}
}
2.多表查询(引用php变量要加双引号)
步骤:
(1)select 先确定要筛选的变量
(2)from 相应的表
(3)where 筛选的条件
= 相等
and 且
or 或
(4)mysqli_fetch_assoc提取元素 $row['values'] //关联数组键值对使用
$sql = "
select a.s_id,c.s_name
from test.s_c a,test.course b,test.stu_info c
where b.c_Name='{$course}' and b.c_No=a.c_no and a.score<60 and a.s_id=c.s_id
";//多表查询
$result = mysqli_query($link, $sql);
if ($result)
{
$count = 0;
echo "<table><caption>该课程不及格的学生</caption>";
echo "<tr><th>学号</th><th>姓名</th>";
while ($row = mysqli_fetch_assoc($result))
{ //取数据
echo "<tr><td>{$row['s_id']}</td><td>{$row['s_name']}</td></tr>";
++$count;
}
}
完整源码:
connect.php
<?php
function connect()
{
$link = mysqli_connect(HOST, HOSTNAME, PWD)
or die(mysqli_connect_error());
//2、选择数据库
mysqli_select_db($link, DB_NAME) or die(mysqli_error($link));
//3、设置字符集
mysqli_set_charset($link, DB_CHAR);
return $link;
}
function insert($table,$arr,$link)
{
$keys=implode(',',array_keys($arr));
$vals="'".implode("'".','."'",array_values($arr))."'";
$sql="insert into {$table}({$keys}) values ({$vals})";
if(mysqli_query($link,$sql))
{
return 1;
}
else{
return 0;
}
}
php执行代码:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>$Title$</title>
</head>
<body>
</body>
<?php
require('./config.php');
require('./connect.php');
$link=connect();
$id=$_POST['id'];
$course=$_POST['course'];
if($id) {
$FLAG = 0;
$sql = "select s_id from test.stu_info where s_id='{$id}';";
$result = mysqli_query($link, $sql);
if (!mysqli_fetch_assoc($result)) {
echo "<script>alert('该学号不存在!请重新输入')</script>";
echo "<script>history.go(-1)</script>";
} else {
$sql = "select a.s_id,b.c_Name from test.s_c a,test.course b where a.c_no=b.c_No and a.s_id='$id'";
$result = mysqli_query($link, $sql);
echo "<table><caption><h2>该学生所选课程</h2></caption>";
echo "<tr><th>学号</th><th>课程</th></tr>";
while ($row = mysqli_fetch_assoc($result)) {
echo "<td>{$row['s_id']}</td>"; //关联数组键值对使用
echo "<td>{$row['c_Name']}</td>";
echo "</tr>";
}
}
}
if ($course) {
//根据课程名 筛选出成绩不合格的学生
//根据课程名 选出对应的课程id 再根据课程 id 选出对应的学生id->选出name
$sql = "select c_Name from test.course where c_Name='{$course}'";
$flag = 0;
$result = mysqli_query($link, $sql);
if (!mysqli_fetch_assoc($result)) {
echo "<script>alert('该课程不存在!请重新输入')</script>";
echo "<script>history.go(-1)</script>";
} else {
$sql = "
select a.s_id,c.s_name
from test.s_c a,test.course b,test.stu_info c
where b.c_Name='{$course}' and b.c_No=a.c_no and a.score<60 and a.s_id=c.s_id";//多表查询
$result = mysqli_query($link, $sql);
if ($result) {
$count = 0;
echo "<table><caption>该课程不及格的学生</caption>";
echo "<tr><th>学号</th><th>姓名</th>";
while ($row = mysqli_fetch_assoc($result)) {
echo "<tr><td>{$row['s_id']}</td><td>{$row['s_name']}</td></tr>";
++$count;
}
if ($count == 0) {
echo "<script>alert('该课程没有不及格人数!')</script>";
echo "<script>history.go(-1)</script>";
}
} else {
echo "<script>alert('查询失败!')</script>";
echo "<script>history.go(-1)</script>";
}
}
}
表单html代码:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
<style type="text/css">
</style>
<script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>
</head>
<body>
<form name="f1" action="CST20048-WWQ 2.php" method="post">
<table>
<tr><td>输入学号查询所选课程:<input type="text" name="id"><input type="submit" value="选课查询" ></td></tr>
<tr><td>请输入课程名称:<input type="text" name="course"><input type="submit" value="不及格查询" ></td></tr>
</table>
</form>
</body>
</html>
OK!