PHP Basics Seven: PHP Operation Database

<!-- 第十三章php操作数据库 -->
<?php
//     $sql = "insert into money(username,age) values(&#39;bao3&#39;, 25)";
//     $con = mysqli_connect("localhost","root","root");
//     if (!$con) {
//         die(&#39;Could not connect:&#39;.mysql_error());
//     }
//     echo &#39;连接成功！&#39;;
    
    
//     mysqli_select_db($con, &#39;mytest&#39;);
//     $result = mysqli_query($con, $sql);
//     var_dump($result);
//     mysqli_close($con)
?>

<form action="" method="post">
    用户名：<input type="text" name="username" /><br />
    密码：<input type="password" name="password" /><br />
  重复密码：<input type="password" name="repassword" /><br />
  <input type="submit" value="提交" />
</form>

<?php
    if (trim(@$_POST[&#39;password&#39;]) != trim(@$_POST[&#39;repassword&#39;])) {
        exit(&#39;两次密码不一致，请返回上一页&#39;);
    }
    
    @$username = trim($_POST[&#39;username&#39;]);
    @$password = md5(trim($_POST[&#39;password&#39;]));
    @$time = date(&#39;ymd&#39;,time());
    $ip = $_SERVER[&#39;REMOTE_ADDR&#39;];
    $conn = mysqli_connect(&#39;localhost&#39;, &#39;root&#39;, &#39;root&#39;);
    if (mysqli_error($conn)) {
        echo mysqli_error($conn);
        exit;
    }
    
    mysqli_select_db($conn, &#39;book&#39;);
    mysqli_set_charset($conn, &#39;utf-8&#39;);
    mysqli_set_charset($conn, &#39;gb2312&#39;);
    $sql = "insert into user(username,password,createtime,createip)
         values(&#39;".$username."&#39;,&#39;".$password."&#39;,&#39;".$time."&#39;,&#39;".$ip."&#39;)";
    $result = mysqli_query($conn, $sql);
    if ($result) {
        echo &#39;成功&#39;;
    }else {
        echo &#39;失败&#39;;
    }
    
    echo &#39;当前用户插入的ID为&#39;.mysqli_insert_id($conn);
    mysqli_close($conn);
?>

<!-- 做一个列表显示 -->
<?php
    $conn = mysqli_connect(&#39;localhost&#39;, &#39;root&#39;, &#39;root&#39;, &#39;book&#39;);
    if (mysqli_errno($conn)) {
        mysqli_error($conn);
        exit;
    }
    mysqli_set_charset($conn, &#39;utf8&#39;);
    mysqli_set_charset($conn, &#39;gb2312&#39;);
    $sql = "select id,username,createtime,createip from user order by id desc";
    $result = mysqli_query($conn, $sql);
    if ($result && mysqli_num_rows($result)) {
        echo &#39;<table width="800" border="1">&#39;;
        while ($row = mysqli_fetch_assoc($result)){
            echo &#39;<tr>&#39;;
            echo &#39;<td>&#39;.$row[&#39;username&#39;].&#39;</td>&#39;;
            echo &#39;<td>&#39;.@date(&#39;Y-m-d H:i:s&#39;, $row[&#39;createtime&#39;]).&#39;</td>&#39;;
            echo &#39;<td>&#39;.long2ip($row[&#39;createip&#39;]).&#39;</td>&#39;;
            echo &#39;<td><a href="edit.php?id=&#39;.$row[&#39;id&#39;].&#39;">编辑用户</a></td>&#39;;
            echo &#39;<td><a href="delete.php?id=&#39;.$row[&#39;id&#39;].&#39;">删除用户</a><td/>&#39;;
            echo &#39;</tr>&#39;;
        }
        echo &#39;</table>&#39;;
    }else {
        echo &#39;没有数据&#39;;
    }
    mysqli_close($conn);
?>

<!-- 把用户做个分页 -->
<?php
// 做一个配置文件config.php
//数据库服务器
define(&#39;DB_HOST&#39;, &#39;localhost&#39;);
//数据库用户名
define(&#39;DB_USER&#39;, &#39;root&#39;);
//数据库密码
define(&#39;DB_PWD&#39;, &#39;secret&#39;);
//库名
define(&#39;DB_NAME&#39;, &#39;book&#39;);
//字符集
define(&#39;DB_CHARSET&#39;, &#39;utf8&#39;);
?>
<?php
// connection.php
include &#39;config.php&#39;;
$conn = mysqli_connect(&#39;DB_HOST&#39;, &#39;DB_USER&#39;, &#39;DB_PWD&#39;, &#39;DB_NAME&#39;);
if (mysqli_error($conn)) {
    mysqli_error($conn);
    exit;
}
mysqli_set_charset($conn, DB_CHARSET);
?>
<?php
    include &#39;connection.php&#39;;
    $count_sql = &#39;select count(id) as c from user&#39;;
    $result = mysqli_query($conn, $count_sql);
    $data = mysqli_fetch_assoc($result);
    $count = $data[&#39;c&#39;];
    
    $page = isset($_GET[&#39;page&#39;]) ? (int)$_GET[&#39;page&#39;] : 1;
    
//     if (isset($_GET[&#39;page&#39;])) {
//         $page = (int) $_GET[&#39;page&#39;];
//     }else {
//         $page = 1;
//     }
    
    $num = 5;
    $total = ceil($count / $num);
    if ($page <= 1) {
        $page = 1;
    }
    if ($page >= $total) {
        $page = $total;
    }
    $offset = ($page - 1) * $num;
    
    $sql = "select id,username,createtime,createip from user order by id desc
       id desc limit $offset,$num";
    $result = mysqli_query($conn, $sql);
    if ($result && mysqli_num_fields($result)) {
       echo &#39;<table width="800" border="1">&#39; ;
       while ($row = mysqli_fetch_assoc($result)){
           echo &#39;<tr>&#39;;
           echo &#39;<td>&#39;.$row[&#39;username&#39;].&#39;</td>&#39;;
           echo &#39;<td>&#39;.date(&#39;Y-m-d H:i:s&#39;,$row[&#39;createtime&#39;]).&#39;</td>&#39;;
           echo &#39;<td>&#39;.long2ip($row[&#39;createip&#39;]).&#39;</td>&#39;;
           echo &#39;<td><a herf="edit.php?id=&#39;.$row[&#39;id&#39;].&#39;">编辑用户</a></td>&#39;;
           echo &#39;<td><a herf="delete.php?id=&#39;.$row[&#39;id&#39;].&#39;"></a></td>&#39;;
           echo &#39;</tr>&#39;;
       }
       echo &#39;<tr>
             <td colspan="5">
             <a herf="page.php?page=1">首页</a>
             <a href="page.php?page=&#39;.($page - 1).&#39;">上一页</a>
             <a herf="page.php?page=&#39;.($page + 1).&#39;">下一页</a>
             <a herf="page.php?page=&#39;.$total.&#39;">页尾</a>
                        当前是第&#39;.$page.&#39;页 共&#39;.$total.&#39;页
             </td>
             </tr>&#39;;
       echo &#39;</table>&#39;;
    }else {
        echo &#39;没有数据&#39;;
    }
    mysqli_close($conn);
?>
<!-- 批量和指定删除用户 -->
<?php
    include &#39;connection.php&#39;;
    if (is_array($_POST[&#39;id&#39;])) {
        $id = join(&#39;,&#39;, $_POST[&#39;id&#39;]);
    }elseif (is_numeric($_GET[&#39;id&#39;])){
        $id = (int) $_GET[&#39;id&#39;];
    }else {
        echo &#39;数据不合法&#39;;
        exit;
    }
    $sql = "delete from user where id in($id)";
    $result = mysqli_query($conn, $sql);
    if ($result) {
        echo &#39;删除成功&#39;;
    }else {
        echo &#39;删除失败&#39;;
    }
?>
<!-- 修改用户信息 -->
<?php
//edit.php
    include &#39;connection.php&#39;;
    if (is_numeric($_GET[&#39;id&#39;])) {
        $id = (int) $_GET[&#39;id&#39;];
    }
    $sql = "select id,username from user where id=".$id;
    $result = mysqli_query($conn, $sql);
    $data = mysqli_fetch_assoc($result);
?>
<?php
    //update.php
    include &#39;connection.php&#39;;
    $id = (int) $_POST[&#39;id&#39;];
    if (trim($_POST[&#39;password&#39;])) {
        $password = md5(trim($_POST[&#39;password&#39;]));
        $sql = "update user set password=&#39;".$password."&#39; where id = $id";
    }else {
        echo &#39;修改成功&#39;;
    }
    $result = mysqli_query($conn, $sql);
    if ($result) {
        echo &#39;修改成功&#39;;
    }
?>
<form action="update.php" method="post">
    用户名：<input type="text" name="username" value="<?php echo $data[&#39;username&#39;];?>" readonly/><br /> 
    密码：<input type="password" name="password" /><br />
  <input type="hidden" value="<?php echo $data[&#39;id&#39;];?>" name="id" />
  <input type="submit" value="提交" />    
</form>


<!-- 数据显示乱码终极解决办法 -->
<?php
//     解决乱码问题的核心思想 ，就是：一定要多个不同的文件系统中一定要统一编码。
//     9个要点分别是：
//     1.html编码与MySQL编码一致
//     2.PHP编码与MySQL编码一致
//     3.若有header头发送字符集，请与数据库一样
//     4.<meta http-equiv="Content-Type"content="text/html;charset=utf-8">
//       要和页面文字编码一致
//     5.数据库建库的字符集要统一
//     6.表的字符集要统一
//     7.列的字符集要统一(表设了列就默认写表的)
//     8.连接,校验的字符集要统一
//     9.结果集的字符集要统一



?>