MySQL数据库的增删改查

为了开发出一个家教信息服务网站,已经设计好家教注册登录等页面的表单,接下来本文解决的问题即是:将表单提交的内容上传到服务器端数据库中。系列文章汇总:一、整体设计:家教信息服务平台的开发二、前端设计:表单实现登录注册功能三、后台设计:MySQL数据库的增删改查连接数据库上篇文章涉及到表单的设计,都是靠近前端,也就是能够直接表现在浏览器上的。表单提交后的POST变量还需要存储在数据库中。这一节总结一下...

MySQL数据库的增删改查

为了开发出一个家教信息服务网站,已经设计好家教注册登录等页面的表单,接下来本文解决的问题即是:将表单提交的内容上传到服务器端数据库中。

系列文章汇总:

一、整体设计:家教信息服务平台的开发

二、前端设计:表单实现登录注册功能

三、后台设计:MySQL数据库的增删改查

连接数据库

上篇文章涉及到表单的设计, 都是靠近前端,也就是能够直接表现在浏览器上的。表单提交后的POST变量还需要存储在数据库中。这一节总结一下数据库的操作,包括增删改查之类的。

比如要将学员注册的信息存入数据库,可以在数据库中设计这样一个表格:


其中第11项push代表的是是否推送,如果教员不愿意公布自己的信息了就可以选择取消。

前端时间QQ空间相册出错,有人发现很多已删除的照片又重新出现。所以不少人又不得不面对那些最想销毁的照片,内心羞耻难以言表,只能呵呵,看来腾讯也是将“诚实地删除”替换成了一个开关。

数据库连接代码:

// connect to database$conn=new mysqli("localhost", "root", "","test");$conn->query("set names utf8");if($conn->connect_errno){die('Failed to connect to the database:'.mysqli_connect_error());}
因为php版本很多,安全技术也在不断发展,php并不是向前兼容的,与数据库相关的操作会发生改变,一些函数可能已经失效,需要注意一下。

向数据库增加记录:

 //判断是否是从注册页跳转过来的,防止恶意访问。if(isset($_POST["user"])){$user =$_POST["user"];//判断QQ是否被注册过,或者重复提交$query="select user from registrants where user='$user'";$result=$conn->query($query);$rows=$result->fetch_row();//用户名没有重复才往数据库插入数据if(empty($rows)){$pass =mysqli_real_escape_string($conn,$_POST["pass"]);$name =mysqli_real_escape_string($conn,$_POST["name"]);$gender = mysqli_real_escape_string($conn,$_POST["gender"]);$school = mysqli_real_escape_string($conn,$_POST["school"]);$grade = mysqli_real_escape_string($conn,$_POST["grade"]);$major = mysqli_real_escape_string($conn,$_POST["major"]);if(!empty($_POST["subject"])){$array=$_POST["subject"];$subject=implode(',',$array);}else$subject="";$aboutme = mysqli_real_escape_string($conn,$_POST["aboutme"]);$push = 1;//默认注册即发布// prepare query$query = "INSERT INTO registrants (user, pass, name, gender, school, grade, major, subject, aboutme, push) VALUES('$user', '$pass', '$name', '$gender', '$school', '$grade', '$major', '$subject', '$aboutme', '$push')";if ($conn->query($query) == FALSE) {die('INSERT attempt failed');}$query="SELECT LAST_INSERT_ID()";$result=$conn->query($query);$id=$result->fetch_row();}}else{$host = $_SERVER["HTTP_HOST"];$path = rtrim(dirname($_SERVER["PHP_SELF"]), "/\\");header("Location: http://$host$path/?page=register");}$result->free();$conn->close();

登录后可以查看自己的信息:


代码实现:

<?// connect to database$conn=new mysqli("localhost", "root", "","test");$conn->query("set names utf8");if($conn->connect_errno){die('Failed to connect to the database:'.mysqli_connect_error());}if (isset($_SESSION["user"]))$user=$_SESSION["user"];else$user=0; // prepare query $query = "SELECT * FROM registrants WHERE user='$user'";$result=$conn->query($query); // iterate over results while ($row = mysqli_fetch_array($result)) {$id=$row["ID"];$user=$row["user"];$pass=$row["pass"];$name=$row["name"];$gender=$row["gender"];$school=$row["school"];$grade=$row["grade"];$major=$row["major"];$subject=$row["subject"];$aboutme=$row["aboutme"];$push=$row["push"]; }$result->free();$conn->close();?>


修改自己的信息:


代码实现(也需要一个表单,这里只给出数据库update操作):

<?php// connect to database$conn=new mysqli("localhost", "root", "","test");$conn->query("set names utf8");if($conn->connect_errno){die('Failed to connect to the database:'.mysqli_connect_error());}if (isset($_SESSION["user"]))$user=$_SESSION["user"];else$user=$_POST["user"];$pass =mysqli_real_escape_string($conn,$_POST["pass"]);$name =mysqli_real_escape_string($conn,$_POST["name"]);$gender = mysqli_real_escape_string($conn,$_POST["gender"]);$school = mysqli_real_escape_string($conn,$_POST["school"]);$grade = mysqli_real_escape_string($conn,$_POST["grade"]);$major = mysqli_real_escape_string($conn,$_POST["major"]);if(!empty($_POST["subject"])){$array=$_POST["subject"];$subject=implode(',',$array);}else$subject="";$aboutme = mysql_real_escape_string($_POST["aboutme"]);$push = $_POST["push"]; // prepare queryif(empty($pass)){//密码修改为空就不用修改$query = "UPDATE registrants SET name='$name',gender='$gender',school='$school',grade='$grade',major='$major',subject='$subject',aboutme='$aboutme',push='$push' WHERE user='$user'";}else{$query = "UPDATE registrants SET pass='$pass',name='$name',gender='$gender',school='$school',grade='$grade',major='$major',subject='$subject',aboutme='$aboutme',push='$push' WHERE user='$user'";}$result=$conn->query($query);$conn->close();$host = $_SERVER["HTTP_HOST"]; $path = rtrim(dirname($_SERVER["PHP_SELF"]), "/\\"); header("Location: http://$host$path/?page=myinfo");?>

除了查询自己的信息,还需要做两点有特别需求的查询操作:一、使需求信息按时间倒序显示在主页上;二、随机显示3条教员信息,点换一批,重置随机。

一、倒序查询:

<?php// connect to database$conn=new mysqli("localhost", "root", "","test");$conn->query("set names utf8");if($conn->connect_errno){die('Failed to connect to the database:'.mysqli_connect_error());} // prepare query $query = "SELECT * FROM orders order by ID desc limit 3";$result=$conn->query($query); // iterate over results while ($row = mysqli_fetch_array($result)) {$id=$row["ID"];$name=$row["name"];$grade=$row["grade"];$gender=$row["gender"];$name=$row["name"];$address=$row["address"];$detailed=$row["detailed"];$tel=$row["tel"];$subjects=$row["subject"];$timepay=$row["timepay"];$want=$row["want"];$time=$row["time"];$succeed=$row["succeed"];if($succeed==0)echo " <div class=\"col-sm-4\">  <div class=\"panel panel-info\"><div class=\"panel-heading\">  <h3 class=\"panel-title\">#$id</h3></div><div class=\"panel-body\"><table class=\"table\" style=\"table-layout:fixed\">  <tr><td>$name</td><td>$grade</td><td>$gender</td>  </tr>  <tr><td>南京市</td><td>$address</td>  </tr>  <tr>  <td colspan=\"3\"><strong>详细住址:</strong>$detailed</td>  </tr>  <tr><td colspan=\"3\"><strong>补习科目:</strong>$subjects</td>  </tr>  <tr><td colspan=\"3\"><strong>时间及薪酬:</strong>$timepay</td>  </tr>  <tr><td colspan=\"3\"><strong>要求:</strong>$want</td>  </tr>  <tr><td colspan=\"3\"><strong>发布时间:</strong>$time</td>  </tr>  <tr><td><a href=\"?page=connect&id=$id\"><span class=\"label label-success\" style=\" float:left\">点击预约</span></a></td>  </tr>  </table></div>  </div> </div>";elseecho "<div class=\"col-sm-4\">  <div class=\"panel panel-info\"><div class=\"panel-heading\">  <h3 class=\"panel-title\">#$id</h3></div><div class=\"panel-body\"><table class=\"table\" style=\"table-layout:fixed\">  <tr><td>$name</td><td>$grade</td><td>$gender</td>  </tr>  <tr><td>南京市</td><td>$address</td>  </tr>  <tr>  <td colspan=\"3\"><strong>详细住址:</strong>$detailed</td>  </tr>  <tr><td colspan=\"3\"><strong>补习科目:</strong>$subjects</td>  </tr>  <tr><td colspan=\"3\"><strong>时间及薪酬:</strong>$timepay</td>  </tr>  <tr><td colspan=\"3\"><strong>要求:</strong>$want</td>  </tr>  <tr><td colspan=\"3\"><strong>发布时间:</strong>$time</td>  </tr>  <tr>  <td><span class=\"label label-warning\" style=\" float:left\">已成功</span></td>  </tr>  </table></div>  </div> </div>"; }$result->free();$conn->close();?>
二、随机查询3条

<?php// connect to database$conn=new mysqli("localhost", "root", "","test");$conn->query("set names utf8");if($conn->connect_errno){die('Failed to connect to the database:'.mysqli_connect_error());} // prepare query $query = "SELECT * FROM registrants WHERE push='1' order by rand() limit 3";$result=$conn->query($query); // iterate over results while ($row = mysqli_fetch_array($result)) {$id=$row["ID"];$user=$row["user"];$name=$row["name"];$gender=$row["gender"];$school=$row["school"];$grade=$row["grade"];$major=$row["major"];$subjects=$row["subject"];$aboutme=$row["aboutme"];echo "<div class=\"col-sm-4\">  <div class=\"panel panel-info\"><div class=\"panel-heading\">  <h3 class=\"panel-title\">#$id</h3></div><div class=\"panel-body\"><table class=\"table\" style=\"table-layout:fixed\">  <tr><td>$name</td><td>$grade</td><td>$gender</td>  </tr>  <tr><td>$school</td><td><strong>专业:</strong>$major</td><td colspa=\"3\"><strong>QQ:</strong><a href=\"http://wpa.qq.com/msgrd?v=3&uin=2240140092&site=qq&menu=yes\"><span class=\"label label-success\" style=\" float:right\">联系我</span></a></td>  </tr>  <tr><td colspan=\"3\"><strong>可授课科目:</strong>$subjects</td>  </tr>  <tr>  <td colspan=\"3\"><strong>自我描述:</strong>$aboutme</td>  </tr>  </table></div>  </div> </div>"; }$result->free();$conn->close();?>
最后,完成预约功能。实质就是生成两个表(教员信息表,需求信息表)之间的关联。这两者不是一一对应的,因为一个教员可能预约了不止一个家教请求。所以不在原来的表中增加新的项,而是重新建立了一张表如下,用来连接订单编号和教员用户名:


到了这里,主要的功能都已经实现了。

最后一点无关题目小小的补充。网站的介绍页,精通html CSS可以自己设计更加美观的页面。“联系我们”页的实现,用一些可以百度的到的代码:

点击按钮跳转QQ聊天:

<p>�<a href="http://wpa.qq.com/msgrd?v=3&uin=qq号码&site=qq&menu=yes"><button type="button" class="btn btn-success btn-sm"><span class="glyphicon glyphicon-phone"></span> QQ</button></a>qq号码</p>

点击按钮拨打电话(前提是手机登录网站哈)

  <p>�<a href="tel:手机号码"><button type="button" class="btn btn-success btn-sm"><span class="glyphicon glyphicon-earphone"></span> Tel</button></a>手机号码</p>








源文地址:https://www.guoxiongfei.cn/csdn/1493.html