php 连接数据库的增删改查

//建一个连接，造一个连接对象
$db = new MySQLi("localhost","root","123","mydb");

//判断是否出错
/*if(mysqli_connect_error())
{
    echo "连接失败！";
    exit;
}*/

mysqli_connect_error()?die("连接失败"):"";

//写SQL语句
$sql = "select * from Info";

//执行SQL语句，返回结果集对象
$reslut = $db->query($sql);

//从结果集中读取数据,返回数组
//$attr = $reslut->fetch_all(); //读取所有数据，返回索引二维数组

//$attr = $reslut->fetch_array(); //读取当前指针指向的数据，返回索引关联都存在的数组

//$attr = $reslut->fetch_assoc(); //返回关联数组

$attr = $reslut->fetch_row(); //返回索引数组

//$attr = $reslut->fetch_object(); //返回对象
/*$arr = array();
while($attr = $reslut->fetch_row())
{
    array_push($arr,$attr);
}

var_dump($arr);*/

登陆页面 text.php

1 <form action="chuli.php" method="post">
2 
3 <div>用户名：<input type="text" name="uid" /></div>
4 <div>密码：<input type="password" name="pwd" /></div>
5 <div><input type="submit" value="登录" /></div>
6 </form>

登陆处理 chili.php

 1 <?php 
 2 $uid = $_POST["uid"];
 3 $pwd = $_POST["pwd"];
 4 
 5 //造连接对象
 6 $db = new MySQLi("localhost","root","123","mydb");
 7 
 8 //写SQL语句
 9 //SQL注入攻击
10 
11 $sql = "select password from login where username='{$uid}'";
12 
13 
14 //执行SQL语句
15 $reslut = $db->query($sql);
16 
17 $n = $reslut->fetch_row();
18 
19 if($uid!="" && $pwd !="" )
20 {
21     if($n[0]==$pwd)
22     {
23         header("location:main.php");
24     }
25     else
26     {
27         echo "用户名或密码错误！";
28     }
29 }
30 else
31 {
32     echo "用户名密码不能为空";
33 }
34 ?>

主页面 main.php

 1 <form action="main.php" method="post">
 2 <div>姓名:
 3 <input type="text" name="xm" /> 
 4 <input type="submit" value="查询" />
 5 </div>
 6 </form>
 7 <br />
 8 <table width="100%" border="1" cellpadding="0" cellspacing="0">
 9     <tr>
10         <td>代号</td>
11         <td>姓名</td>
12         <td>性别</td>
13         <td>民族</td>
14         <td>生日</td>
15         <td>操作</td>
16     </tr>
17     
18     <?php
19     
20     //先判断有没有提交值
21     $xxm = "";
22     $tj = " 1=1 ";
23     if(!empty($_POST["xm"]) && $_POST["xm"]!="")
24     {
25         $xxm = $_POST["xm"];
26         $tj = " name like '%{$xxm}%' ";
27     }
28     
29     //造连接对象
30     $db = new  MySQLi("localhost","root","123","mydb");
31     //写SQL语句
32     $sql = "select * from info where ".$tj;
33     echo $sql;
34     //执行SQL语句
35     $result = $db->query($sql);
36     //读数据
37     $attr = $result->fetch_all();
38     
39     foreach($attr as $v)
40     {
41         echo "<tr>";
42         
43         $sex = $v[2]?"男":"女";
44         
45         //根据名族代号查询名族名称
46         $name = NationName($v[3]);
47         
48         //替换关键字
49         $newname = str_replace($xxm,"<mark>{$xxm}</mark>",$v[1]);
50         
51         echo "<td>{$v[0]}</td><td>{$newname}</td><td>{$sex}</td><td>{$name}</td><td>{$v[4]}</td><td><a href='shanchu.php?c={$v[0]}' onclick=\"return confirm('确定删除么？')\">删除</a><a href='xiugai.php?c={$v[0]}'>修改</a></td>";
52         
53         /*foreach($v as $v1)
54         {
55             echo "<td>{$v1}</td>";
56         }*/
57         
58         echo "</tr>";
59     }
60     
61     //给一个民族代号，返回民族名称
62     function NationName($code)
63     {
64         //造连接对象
65         $db = new  MySQLi("localhost","root","123","mydb");
66         //写SQL语句
67         $sql = "select name from nation where code='{$code}'";
68         //执行SQL语句
69         $result = $db->query($sql);
70         
71         $attr = $result->fetch_row();
72         
73         return $attr[0];
74     }
75     
76     ?>
77     
78     
79 </table>
80 <a href="add.php"><input type="button" value="添加数据" /></a>

删除处理 shanchu.php

<?php
$code = $_GET["c"];

//造连接对象
$db = new MySQLi("localhost","root","123","mydb");

$sql = "delete from info where code='{$code}'";

$r = $db->query($sql);

if($r)
{
    header("location:main.php");
}
else
{
    echo "删除失败";
}

添加页面 add.php

 1 <form action="addchuli.php" method="post">
 2     <div>代号：<input type="text" name="code" /></div>
 3     <div>姓名：<input type="text" name="name" /></div>
 4     <div>性别：
 5     <input type="radio" name="sex" value="1" checked="checked" />男
 6     &nbsp;
 7     <input type="radio" name="sex" value="0" />女
 8     </div>
 9     <div>民族：
10     <select name="nation">
11         <?php
12         //造连接对象
13         $db = new MySQLi("localhost","root","123","mydb");
14         $sql = "select * from nation";
15         $result = $db->query($sql);
16         $attr = $result->fetch_all();
17         
18         foreach($attr as $v)
19         {
20             echo "<option value='{$v[0]}'>{$v[1]}</option>";
21         }
22         ?>
23     </select>
24     </div>
25     <div>生日：<input type="text" name="birthday" /></div>
26     
27     <div><input type="submit" value="添加" /></div>
28 </form>

添加处理页面 addchuli.php

 1 <?php
 2 $code = $_POST["code"];
 3 $name = $_POST["name"];
 4 $sex = $_POST["sex"];
 5 $nation = $_POST["nation"];
 6 $birthday = $_POST["birthday"];
 7 
 8 //造连接对象
 9 $db = new MySQLi("localhost","root","123","mydb");
10 
11 $sql = "insert into info values('{$code}','{$name}',{$sex},'{$nation}','{$birthday}')";
12 
13 $db->query($sql);
14 
15 header("location:main.php");
16 ?>

修改页面 xiugai.php

 1 <?php
 2     $code = $_GET["c"];
 3     //造连接对象
 4     $db = new MySQLi("localhost","root","123","mydb");
 5     //写SQL语句
 6     $sinfo = "select * from info where code='{$code}'";    
 7 
 8     $rinfo = $db->query($sinfo);
 9     
10     $ainfo = $rinfo->fetch_row();
11 ?>
12 
13 <form action="xiugaichuli.php" method="post">
14     <div><input type="hidden" name="code" value="<?php echo $ainfo[0]; ?>" /></div>
15     <div>姓名：<input type="text" name="name" value="<?php echo $ainfo[1]; ?>" /></div>
16     
17     
18     
19     <div>性别：
20     <input type="radio" name="sex" value="1" <?php echo $ainfo[2]?"checked='checked'":"" ?> />男
21     &nbsp;
22     <input type="radio" name="sex" value="0" <?php echo $ainfo[2]?"":"checked='checked'" ?> />女
23     </div>
24     
25     
26     
27     <div>民族：
28     <select name="nation">
29         <?php
30         
31         $sql = "select * from nation";
32         $result = $db->query($sql);
33         $attr = $result->fetch_all();
34         
35         foreach($attr as $v)
36         {
37             if($v[0]==$ainfo[3])
38             {
39                 echo "<option selected='selected' value='{$v[0]}'>{$v[1]}</option>";
40             }
41             else
42             {
43                 echo "<option value='{$v[0]}'>{$v[1]}</option>";
44             }
45         }
46         ?>
47     </select>
48     </div>
49     
50     
51     
52     <div>生日：<input type="text" name="birthday" value="<?php echo $ainfo[4]; ?>" /></div>
53     
54     <div><input type="submit" value="修改" /></div>
55 </form>

修改处理 xiugaichuli.php

 1 <?php
 2 $code = $_POST["code"];
 3 $name = $_POST["name"];
 4 $sex = $_POST["sex"];
 5 $nation = $_POST["nation"];
 6 $birthday = $_POST["birthday"];
 7 
 8 //造连接对象
 9 $db = new MySQLi("localhost","root","123","mydb");
10 
11 $sql = "update info set name='{$name}',sex={$sex},nation='{$nation}',birthday='{$birthday}' where code='{$code}'";
12 
13 $db->query($sql);
14 
15 header("location:main.php");

查询页面 duotiaojian.php

 1 <form action="duotiaojian.php" method="post">
 2 <div>姓名:
 3 <input type="text" name="xm" /> 
 4 民族代号：
 5 <input type="text" name="mz" />
 6 <input type="submit" value="查询" />
 7 </div>
 8 </form>
 9 <br />
10 <table width="100%" border="1" cellpadding="0" cellspacing="0">
11     <tr>
12         <td>代号</td>
13         <td>姓名</td>
14         <td>性别</td>
15         <td>民族</td>
16         <td>生日</td>
17         <td>操作</td>
18     </tr>
19     
20     <?php
21     
22     //先判断有没有提交值
23     $xxm = "";
24     $tj = " 1=1 ";
25     $tj2 = " 1=1 ";
26     if(!empty($_POST["xm"]) && $_POST["xm"]!="")
27     {
28         $xxm = $_POST["xm"];
29         $tj = " name like '%{$xxm}%' ";
30     }
31     
32     if(!empty($_POST["mz"]) && $_POST["mz"]!="")
33     {
34         $mz = $_POST["mz"];
35         $tj2 = " nation='{$mz}' ";
36     }
37     
38     
39     
40     //造连接对象
41     $db = new  MySQLi("localhost","root","123","mydb");
42     //写SQL语句
43     $sql = "select * from info where ".$tj." and ".$tj2;
44     echo $sql;
45     //执行SQL语句
46     $result = $db->query($sql);
47     //读数据
48     $attr = $result->fetch_all();
49     
50     foreach($attr as $v)
51     {
52         echo "<tr>";
53         
54         $sex = $v[2]?"男":"女";
55         
56         //根据名族代号查询名族名称
57         $name = NationName($v[3]);
58         
59         //替换关键字
60         $newname = str_replace($xxm,"<mark>{$xxm}</mark>",$v[1]);
61         
62         echo "<td>{$v[0]}</td><td>{$newname}</td><td>{$sex}</td><td>{$name}</td><td>{$v[4]}</td><td><a href='shanchu.php?c={$v[0]}' onclick=\"return confirm('确定删除么？')\">删除</a><a href='xiugai.php?c={$v[0]}'>修改</a></td>";
63         
64         /*foreach($v as $v1)
65         {
66             echo "<td>{$v1}</td>";
67         }*/
68         
69         echo "</tr>";
70     }
71     
72     //给一个民族代号，返回民族名称
73     function NationName($code)
74     {
75         //造连接对象
76         $db = new  MySQLi("localhost","root","123","mydb");
77         //写SQL语句
78         $sql = "select name from nation where code='{$code}'";
79         //执行SQL语句
80         $result = $db->query($sql);
81         
82         $attr = $result->fetch_row();
83         
84         return $attr[0];
85     }
86     
87     ?>
88     
89     
90 </table>
91 <a href="add.php"><input type="button" value="添加数据" /></a>

posted on 2016-10-31 14:36 小学员G 阅读(7388) 评论(0) 编辑收藏举报