连接数据库实现添加,删除,修改,AJAX异步,分页,查询等操作,

3/8/2017来源:ASP.NET技巧人气:2100

以下是boss.php页面

<a href="conn.php?&q=add">添加页面</a>

<script> function go(su){    if(confirm("您确定要删除吗?")){       window.location.href='conn.php?id='+su+'&q=delete';    }else{       return false;    } } function showsite(str,sd){ if (str=="")     {         document.getElementById(sd).innerHTML="";         return;     }      if (window.xmlHttPRequest)     {                  xmlhttp=new XMLHttpRequest();     }     else     {                  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");     }     xmlhttp.onreadystatechange=function()     {         if (xmlhttp.readyState==4 && xmlhttp.status==200)         {             document.getElementById(sd).innerHTML=xmlhttp.responseText;         }     }     xmlhttp.open("GET","conn.php?w="+str,true);     xmlhttp.send(); } </script> <?php  $k = isset($_GET['keyWords']) ? $_GET['keywords'] : ''; $f = isset($_GET['urlname']) ? $_GET['urlname'] : ''; $c = isset($_GET['emailname']) ? $_GET['emailname'] : ''; ?> <form action="" method="GET"> 网站名:<input type="text" name="keywords" size="8" value="<?php echo $k;?>" /> URL:<input type="text" name="urlname" size="8" value="<?php echo $f;?>"> EMAIL:<input type="text" name="emailname" size="8" value="<?php echo $c;?>"> <input type="submit" name="subname" value="搜索"/> <input type="button" value="全部信息" onclick="window.location='boss.php'"/> </form> <?php  $con = MySQLi_connect('127.0.0.1','root','','mydb'); if(!$con){ die("数据库连接失败原因是".mysqli_error($con)); } mysqli_set_charset($con, 'utf8'); $wherelist = array();    //封装的查询条件; if(!empty($k)) { $wherelist[] = "`name` like '%".$k."%'" ; } if(!empty($f)) { $wherelist[] = "`url` like '%".$f."%'";     } if(!empty($c)) { $wherelist[] = "`email` like '%".$c."%'"; } if(!empty($wherelist)) { $where = " where ".implode(" and ", $wherelist); }else{ $where = "where 1"; } $page = isset($_GET['page'])?$_GET['page']:1; $pagesize = 3; $maxrows; $maxpage; $sql = "select * from `websites`"; $res = mysqli_query($con, $sql); $maxrows = mysqli_num_rows($res); $maxpage = ceil($maxrows/$pagesize); if($page > $maxpage){ $page = $maxpage; } if($page<1){ $page = 1; } $limit = " limit ".($page-1)*$pagesize.",$pagesize"; $arr = "select * from `websites` {$where} order by id desc {$limit}"; //echo $arr; $result = mysqli_query($con, $arr);            echo "<table border='1'>           <tr>           <th>ID</th>           <th>网站名</th>           <th>url</th>       <th>email</th>           <th>Alexa</th>           <th>国家</th>   <th>点击量</th>   <th>操作 </th>           </tr>";            while ($row = mysqli_fetch_array($result)){     ?>   <tr>               <td> <?php echo $row['id']; ?></td>               <td> <?php echo $row['name'];?> </td>               <td> <?php echo $row['url'];?></td>               <td> <?php echo $row['email']; ?></td>               <td> <?php echo $row['alexa'];?> </td>               <td> <?php echo $row['country'];?> </td>               <td id='<?php echo $row['id'];?>'></td>               <td><a href='conn.php?id=<?php echo $row['id'];?>&q=update' >                                       修改</a>||<button type='button' onclick='go(<?php echo $row['id'];?>)'>                                       删除</button>||<button type='button' onclick= "showsite(<?php echo $row['id'];?>,<?php echo $row['id'];?>)">点击量</button></td>              </tr>               <?php    }   echo "</table>";   echo "<br><br>";   echo "当前第{$page}页/共计{$maxpage}页,共计{$maxrows}条";   echo "<a href='boss.php?page=1'>首页</a>";   echo "<a href='boss.php?page=".($page-1)."'>上一页</a>";   for($i=0;$i<$maxpage;$i++) {   echo "<a href='boss.php?page=".$i."'>$i&nbsp</a>"; }   echo "<a href='boss.php?page=".($page+1)."'>下一页</a>";   echo "<a href='boss.php?page=$maxpage'>尾页</a>";   

?>

以下是conn.pnp页面

<?php  $con = mysqli_connect('127.0.0.1','root','','mydb'); if(!$con){ die("数据库连接失败原因是".mysqli_error($con)); }        mysqli_set_charset($con, 'utf8');        $yibu = isset($_GET['w'])?intval($_GET['w']):FALSE;        //echo $yibu; if($yibu){ $frr = "update `websites` set `hits`=hits+1 where id='".$yibu."'"; $yi = mysqli_query($con,$frr); $grr = "select * from `websites` where id='".$yibu."'"; $ye = mysqli_query($con,$grr); $en = mysqli_fetch_array($ye); echo $en['hits']; exit(); } ?> <head> <meta charset="utf8"> </head> <body> <?php         $id = isset($_GET['id'])?intval($_GET['id']):FALSE;        $q = isset($_GET['q'])?$_GET['q']: FALSE;              if($id && $q=='update') { $arr = "select * from `websites` where id='".$id."'"; $result = mysqli_query($con,$arr); $row = mysqli_fetch_array($result); $sub = isset($_POST['sub'])?$_POST['sub']:FALSE;     if($sub)      {     $nn = $_POST['id'];     $nb = $_POST['url'];     $nc = $_POST['alexa'];     $nd = $_POST['country'];     if(!empty($_POST['name']))       {       $name = test_input($_POST['name']);       if(!preg_match("/^[a-zA-Z ]*$/", $name))         {         exit("只允许字母或者空格<br><a href='javascript:history.go(-1)'>返回上一页</a>");        }else {         $na = $name;        }       }else {       exit("名字是必须的<br><a href='Javascript:history.go(-1)'>返回上一页</a>");       }     if(!empty($_POST['url']))       {       $url = test_input($_POST['url']);       if(!preg_match("/\b(?:(?:https?|ftp):\/\/|www\.)[-a-z0-9+&@#\/%?=~_|!:,.;]*[-a-z0-9+&@#\/%=~_|]/i", $url))        {         exit("非法url格式<br><a href='javascript:history.go(-1)'>返回上一页</a>");        }else{         $nb = $url;          }       }else {       exit("URL是必须的<br><a href='javascript:history.go(-1)'>返回上一页</a>");       }       if(!empty($_POST['email']))       {       $email = test_input($_POST['email']);      if(!preg_match("/([\w\-]+\@[\w\-]+\.[\w\-]+)/", $email))        {       exit("非法邮箱格式<br><a href='javascript:history.go(-1)'>返回上一页</a>");                }else{             $emai = $email;              }       }else{               exit("邮箱是必须的<br><a href='javascript:history.go(-1)'>返回上一页</a>");       }     $nf = "update `websites` SET `id`='$nn',`name`='$na',`url`='$nb',`email`='$emai', `alexa`='$nc',`country`='$nd' WHERE `id` ='$nn'";     mysqli_query($con, $nf);     echo "<script>alert('修改成功');location.href='boss.php'</script>";          } }elseif( $q=='add')    {      $row['name'] =  $row['url'] = $row['email'] = $row['alexa'] =$row['country'] = $row['id'] = "";      $w = isset($_POST['sub'])?$_POST['sub']:FALSE;      if($w){      $nc = $_POST['alexa'];      $nd = $_POST['country'];      if(!empty($_POST['name']))      {       $name = test_input($_POST['name']);       if(!preg_match("/^[a-zA-Z ]*$/", $name))       {       exit("只允许字母或者空格<br><a href='javascript:history.go(-1)'>返回上一页</a>");       }else {       $na = $name;       }      }else {       exit("名字是必须的<br><a href='javascript:history.go(-1)'>返回上一页</a>");      }      if(!empty($_POST['email']))      {       $email = test_input($_POST['email']);       if(!preg_match("/([\w\-]+\@[\w\-]+\.[\w\-]+)/", $email))       {       exit("非法邮箱格式<br><a href='javascript:history.go(-1)'>返回上一页</a>");         }else{       $emai = $email;       }      }else {       exit("邮箱是必须的<br><a href='javascript:history.go(-1)'>返回上一页</a>");      }      if(!empty($_POST['url']))      {       $url = test_input($_POST['url']);       if(!preg_match("/\b(?:(?:https?|ftp):\/\/|www\.)[-a-z0-9+&@#\/%?=~_|!:,.;]*[-a-z0-9+&@#\/%=~_|]/i", $url))       {       exit("非法url格式<br><a href='javascript:history.go(-1)'>返回上一页</a>");       }else{       $nb = $url;       }      }else {       exit("URL是必须的<br><a href='javascript:history.go(-1)'>返回上一页</a>");      }        $nf = "INSERT INTO `websites`(`id`, `name`, `url`,`email`, `alexa`, `country`) VALUES ('','$na','$nb','$emai','$nc','$nd')";      mysqli_query($con, $nf);      echo "<script>alert('添加成功');location.href='boss.php'</script>";    } }elseif($id && $q=='delete')    {     $delete = "delete from `websites` where `id` = '".$id."'";   mysqli_query($con, $delete);   echo "<script>alert('删除成功');location.href='boss.php'</script>";    }    function test_input($data){        $data = trim($data);         $data = stripslashes($data);        $data = htmlspecialchars($data);        return $data;    }    ?>     <form action='' method='POST'>          <input type='hidden' name='id'  value="<?php echo $row['id'];?>">  网站名:<input type='text' name='name'  value="<?php echo $row['name'];?>"><br>  URL地址:<input type='text' name='url'  value="<?php echo $row['url'];?>"><br>  email:   <input type='text' name='email'  value="<?php echo $row['email'];?>"><br>  alexa排名:<input type='text' name='alexa'  value="<?php echo $row['alexa'];?>"><br>  国家:           <input type='text' name='country'  value="<?php echo $row['country'];?>">           <input type='submit' name='sub'  value='提交'> </form> </body>