php+mysql’s operations | LSABLOG

首页 » Program » PHP&&Mysql » 正文

php+mysql’s operations

基于上次实现的php+mysql实现超原始的注册登录之后,这次的故事实现在登录后以超级管理员身份进行对注册用户的增删改查操作,而以普通用户却无法实现操作数据库(这里涉及权限控制,我只是简单表示一下效果而已,还没实现真正的权限控制)

先放上连接数据库的页面(conndb.php):

<?php

    $db = mysql_connect("127.0.0.1","root","root") or die("Fail to connect db!");  
    mysql_select_db("userdb",$db) or die ("Can't connect to userdb".mysql_error()); 

?>

以lsa超级管理员登录后页面(welcome.php)增强版:

<?php
$username = $_GET['uname'];
if($username=='lsa'){
    echo 'Welcome SuperAdmin '.$username.'!';
    echo '<br/><br/>';
    include("/var/www/html/conndb.php");
    //$sql = mysql_query("select * from user order by username");  
    //$info = mysql_fetch_array($sql); 
    
    if($_GET['page']==""){
        $_GET['page'] = 1;
    }
    if(is_numeric($_GET['page'])){
        $page_size = 5;
        $query = "select count(*) as total from user order by username";
        $result = mysql_query($query);
        $user_count = mysql_result($result,0,0);
        $page_count = ceil($user_count/$page_size);
        $offset = ($_GET['page'] - 1) * $page_size;
        $sql = mysql_query("select * from user order by username limit $offset,$page_size");
        $info = mysql_fetch_array($sql);   //get a group record(5).mysql_num_rows($sql) is 5
    }
?>
 
<form name="myform" method="post" action="search.php" target="_blank" >
          <input name="searchname" type="text" id="searchname" size="25"> 
          &nbsp;
          <input type="submit" name="Submit" value="search">
</form>

<a href="add_user.php">Add_user</a>

<table width="100%" height="200"  border="1" cellpadding="0" cellspacing="0" bgcolor="#9E7DB4" align="center"> 
<tr valign="top" bgcolor="#FFFFFF"> 
    <td height="100">
      <table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0">
        <tr>
          <td height="90" align="center" valign="top">
              <table width="100%"  border="0" align="center" cellpadding="0" cellspacing="1" bgcolor="#625D59">
                <tr align="center" bgcolor="#00cc00">
          <td width="70">username</td>
                  <td width="200">password</td>
          <td width="20">edit</td>
                </tr>

<?php           
    if($info==false){
        echo "<div align='center' style='color:#FF0000; font-size:18px'>Sorry,not found!</div>";
    }

    do{
    
?>
        <tr align="left" bgcolor="#FFFFFF">
                  
                  <td align="center" height="30"><?php echo $info[username]; ?></td>
                  <td align="center"><?php echo $info[password]; ?></td>
         <td align="center"><a href="check_del.php?id=<?php echo $info[id];?>&curuser=<?php echo $username;?>"><img src="images/delete.gif" width="20" border="0"></a>
        <a href="update_user.php?id=<?php echo $info[id];?>&curuser=<?php echo $username;?>"><img src="images/update.gif" width="20" border="0"></a>
        </td>
                </tr>


<?php
    }
    while($info=mysql_fetch_array($sql));
}
else{
    echo 'Welcome man '.$username;
    echo '<br/>';
}
?>

    <table width="50" border="0" cellspacing="0" cellpadding="0"> 
    <tr> 
    <td width="37%">page:<?php echo $_GET['page'];?>/<?php echo $page_count;?>&nbsp;&nbsp;&nbsp;</td>
        <td width="63%" align="right">
        <?php
            if($_GET['page']!=1){
                echo "<a href=welcome.php?uname=$username&page=1>1</a>&nbsp;";
                echo "<a href=welcome.php?uname=$username&page=".($_GET[page]-1)."><--</a>&nbsp;";
            }
            
            if($_GET['page']<$page_count){
                echo "<a href=welcome.php?uname=$username&page=".($_GET['page']+1).">--></a>&nbsp;";
            echo "<a href=welcome.php?uname=$username&page=".$page_count.">tail</a>&nbsp;";
            }
            
            
        ?>
    </tr>
    </table>
        
        
          </table>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;***found records&nbsp;<font color="red"><?php echo $user_count;?></font>***</td>
        </tr>
      </table>
    <br></td> 
  </tr> 
</table>

<?php
mysql_free_result($sql);
mysql_close($db);
?>

简单的一个页面(没那么超原始了……),实现了分页操作,从数据库提取注册用户的用户名和密码,详情见下效果图

来添加一个用户吧,添加用户页面(add_user.php):

<html>
<head>
<title>add user</title>
</head>
<body>


<form name="adduserform" method="post" action="check_adduser.php">
add username:<input name="add_name" type="text"><br/>
add password:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input name="add_pass" type="password"><br/>
add password again:<input name="add_pass_aga" type="password"><br/>
<input name="submit" type="submit" value="submit" onClick="return check(adduserform)">
<input name="reset" type="reset" value="reset">
</form>

<script language="javascript">
function check(form){
    if(form.add_name.value==""){
        alert("please input username");form.add_name.focus();return false;
    }
    if(form.add_pass.value==""|form.add_pass_aga.value==""){
        alert("please input password or input password again");return false;
    }
    if(form.add_pass.value!=form.add_pass_aga.value){
        alert("two password are different!");return false;
    }
    form.submit();
}
</script>

</body>
</html>

向数据库增加用户页面(check_adduser.php):

<?php
include("/var/www/html/conndb.php");

$addname = $_POST['add_name'];
$addpass = md5($_POST['add_pass']);

$sql = mysql_query("insert into user(username,password) values('$addname','$addpass')");
echo "<script>alert('add user success!');window.location.href='add_user.php';</script>";

mysql_free_result($sql);
mysql_close($db);
?>


来看看数据库和web页面有没加进这个用户

成功添加用户(insert)

好,现在修改一下lsablog这个刚刚加进的用户的密码吧

修改密码页面(update_user.php):

<?php
include("/var/www/html/conndb.php");

$id = $_GET['id'];
$curuser = $_GET['curuser'];

$sql = mysql_query("select * from user where id=$id");
$row = mysql_fetch_object($sql);
?>

<form name="updateuserform" method="post" action="check_updateuser.php">
<input name="id" type="hidden" value="<?php echo $id;?>">
<input name="curuser" type="hidden" value="<?php echo $curuser;?>">
username:<input name="user" type="text" value="<?php echo $row->username;?>" readonly="true"><br/>
new password:<input name="change_pass" type="password"><br/>
new password again<input name="change_pass_aga" type="password"><br/>
<input name="submit" type="submit" value="change" onClick="return checkpass(updateuserform)">
<input name="reset" type="reset">
</form>

<script>
function checkpass(form){
    if(form.change_pass.value==""|form.change_pass_aga.value==""){
        alert("please input password or input password again");return false;
    }
    if(form.change_pass.value!=form.change_pass_aga.value){
        alert("two password are different!");return false;
    }
    form.submit();
}
</script>

<?php
mysql_free_result($sql);
mysql_close($db);
?>

从数据库更新用户密码页面(check_updateuser.php):

<?php 
include("/var/www/html/conndb.php");

$id = $_POST['id'];
$md5_change_pass = md5($_POST['change_pass']);
$curuser = $_POST['curuser'];

$sql = mysql_query("update user set password='$md5_change_pass' where id=$id");

if($sql){
    echo "<script>alert('change password success!');history.back();window.location.href='welcome.php?uname=$curuser';</script>";
}
else{
    echo "<script>alert('change password failed!');history.back();window.location.href='welcome.php?uname=$curuser';</script>";
}

mysql_free_result($sql);
mysql_close($db);
?>

直接去数据库看看密码有没改成功

密码修改成功!(update)

现在我想查询一下有多少用户是ls开头的

直接在welcome.php的查询框查询ls,点击search

查询页面(search.php):

<form name="myform" method="post" action="search.php">
          <input name="searchname" type="text" id="searchname" size="25"> 
          &nbsp;
          <input type="submit" name="Submit" value="search">
</form>


<?php

    $sname = $_POST['searchname'];

    include("/var/www/html/conndb.php");

    $sql = mysql_query("select * from user where username like '%".trim($sname)."%' order by username");
    $info = mysql_fetch_array($sql);
    

    if($info==false){
        echo "<div align='center' style='color:#FF0000; font-size:18px'>Sorry,not found!</div>";
    }

    do{ 

        echo $info[username]."---".$info[password];
        echo '<br/>';

    }while($info=mysql_fetch_array($sql));
    
    echo '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;***Found records:<font color="red">'.$nums = mysql_num_rows($sql).'</font>***';

mysql_free_result($sql);
mysql_close($db);

?>

查询成功!(select)

最后删除掉lsablog用户吧,直接点编辑下面的垃圾桶图标就ok了

删除用户页面(check_del.php):

<?php
include("/var/www/html/conndb.php");

$id = $_GET['id'];
$curuser = $_GET['curuser'];

$sql = mysql_query("delete from user where id=$id");
if($sql){
    echo "<script>alert('delete user success!');history.back();window.location.href='welcome.php?uname=$curuser';</script>";
}else{
    echo "<script>alert('delete user failed!');history.back();window.location.href='welcome.php?uname=$curuser';</script>";
}

mysql_free_result($sql);
mysql_close($db);
?>

看看数据库和web页面还有没lsablog用户

成功删除!(delete)

增删改查操作都成功完成!下来在瞄一下最没用的权限控制吧(前面说过只是表达一下效果而已),上面是以超级管理员lsa登录的,下面以普通注册用户登录一下看看是什么情况。

……

还有很多地方可以完善,比如用mysqli代替mysql,权限控制,防注入等等,成为世界php大师之路,依旧继续!

 

 

 

 

Comment