0x01 php查询功能编写
school数据库。
student表:id username mail字段
admin表:username password 字段
1.当url传参id为1查询数据表student id为1的所有值。当不传参查询student表全部内容
2.编写一个登录页面,当用户登录成功就跳转到admin目录下的success.php否则跳转到admin目录下的
failed.php
1.创建数据库
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
2.创建表
mysql> use school;
Database changed
mysql> create table student(id int(10),username varchar(20),mail varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> create table admin(username varchar(20),password varchar(20));
Query OK, 0 rows affected (0.00 sec)
3.插入数据
mysql> insert into student(id,username,mail) values(1,"zs","zs@yicunyiye.com");
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(id,username,mail) values(2,'ls','ls@yicunyiye.com');
Query OK, 1 row affected (0.00 sec)
mysql> insert into admin(username,password) values("admin","123456");
Query OK, 1 row affected (0.00 sec)
4.查询数据
mysql> select * from student where id =1;
+------+----------+------------------+
| id | username | mail |
+------+----------+------------------+
| 1 | zs | zs@yicunyiye.com |
+------+----------+------------------+
1 row in set (0.00 sec)
mysql> select * from student;
+------+----------+------------------+
| id | username | mail |
+------+----------+------------------+
| 1 | zs | zs@yicunyiye.com |
| 2 | ls | ls@yicunyiye.com |
+------+----------+------------------+
2 rows in set (0.00 sec)
5.php编写查询页面
<?php
mysql_connect("localhost","root","root");
mysql_select_db("school");
if(isset($_GET["id"])){
$id = $_GET["id"];
$sql = "select * from student where id =$id";
}else{
$sql = "select * from student";
}
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)){
echo "id:".$row["id"]." username:".$row["username"]." mail:".$row["mail"];
}
?>
isset
方法就是判断是否存在,这里就是如果存在 id 传参的话那么sql语句就为 $sql = "select * from student where id =$id";
否则就是查询全部 $sql = "select * from student";
。
然后$result = mysql_query($sql); 执
行了语句查询是一个整体,例如一个班级有很多人,每一排每
一列都是人。我们通过 mysql_fetch_array
方法进行切片,也就是把每一行每一列每一个人进行分
开。但是只会执行一次,所以我们通过 while 循环进行无限去切,直到把所有人切完就结束。把每个人
的值赋值给 $row
变量。然后 $row["id"]
就是获取内容中 id 这一列数据。
0x02 php登录功能编写
先构造一个表单
<form action = "./login.php" method="POST">
username:<input type="text" name="user">
password:<input type="text" name="pass">
<input type="submit" name="submit">
</form>
然后php来获取 POST 的内容带入数据库查询。
mysql_connect("localhost","root","root");
mysql_select_db("school");
$username = $_POST["user"];
$password = $_POST["pass"];
$sql = "select * from admin where username='$username' and password =
'$password'";
$result = mysql_query($sql);
当用户登录成功数据库结果为:
mysql> select * from admin where username='admin' and password = '123456';
+----------+----------+
| username | password |
+----------+----------+
| admin | 123456 |
+----------+----------+
1 row in set (0.00 sec)
当用户登录失败数据库结果为:
mysql> select * from admin where username='admin' and password = '1234561';
Empty set (0.00 sec)
在mysql当中,登录成功与否是呈现在一个有结果,一个没得结果。于是我们就只需要考虑在php中如何
呈现这个结果的。
我们就尝试编写。首先会不会是$result = mysql_query($sql);
这里的 $result
,那么我们就写一个
正确和错误的账号密码来输出下这个变量。
可以看到当我们输入一个正确或者错误的密码回显结果都一样,所以这里不能当作判断是否登录成功。
我们继续来分片。
可以通过结果得知,如果查询为空那么 $row
值就为 false
,否则就为真。
<form action = "./login.php" method="POST">
username:<input type="text" name="user">
password:<input type="text" name="pass">
<input type="submit" name="submit">
</form>
<?php
mysql_connect("localhost","root","root");
mysql_select_db("school");
$username = $_POST["user"];
$password = $_POST["pass"];
$sql = "select * from admin where username='$username' and password ='$password'";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
if($row){
header("Location: ./admin/success.php");
}else{
header("Location: ./admin/failed.php");
}
?>