PHP第三十篇——数据库相关操作与分页功能封装

实现数据库相关操作(MySQLi)与分页功能初步实战与封装，入门学习笔记

连接与配置

<?php
$config=[
    'host'=>'localhost',
    'user'=>'root',
    'password'=>'root',
    'charset'=>'utf8',
    'dbName'=>'mysqli'
];

define('DB_HOST','localhost');
define('DB_USER','root');
define('DB_PWD','root');
define('DB_CHARSET','utf8');
define('DB_DBNAME','mysqli');
define('DB_PORT','8889');

MySQL连接与d封装操作

<?php
/**
* 连接
* @param string $host
* @param string $user
* @param string $password
* @param string $charset
* @param string $database
* @return object 连接标识符
*/
function connect1($host,$user,$password,$charset,$database) {
    $link = mysqli_connect ( $host, $user, $password ) or die ( '数据库连接失败<br/>ERROR ' . mysqli_connect_errno () . ':' . mysqli_connect_error () );
    mysqli_set_charset ( $link, $charset );
    mysqli_select_db ( $link, $database ) or die ( '指定数据库打开失败<br/>ERROR ' . mysqli_errno ( $link ) . ':' . mysqli_error ( $link ) );
    return $link;
}
/**
* 连接 需要传递数组
* @param array $config
* @return object
*/
function connect2($config) {
    $link = mysqli_connect ( $config ['host'], $config ['user'], $config ['password'] ) or die ( '数据库连接失败<br/>ERROR ' . mysqli_connect_errno () . ':' . mysqli_connect_error () );
    mysqli_set_charset ( $link, $config ['charset'] );
    mysqli_select_db ( $link, $config ['dbName'] ) or die ( '指定数据库打开失败<br/>ERROR ' . mysqli_errno ( $link ) . ':' . mysqli_error ( $link ) );
    return $link;
}
/**
* 用常量的形式建立连接
* @return unknown
*/
function connect3(){
    // define('DB_HOST','localhost');
    // define('DB_USER','root');
    // define('DB_PWD','root');
    // define('DB_CHARSET','utf8');
    // define('DB_DBNAME','mysqli');
    // define('DB_POST','8889');
    //mysqli_connect('localhost', 'root', 'root', 'mysqli','8889') 
    $link = mysqli_connect ( DB_HOST, DB_USER, DB_PWD ,DB_DBNAME, DB_PORT) or die ( '数据库连接失败<br/>ERROR ' . mysqli_connect_errno () . ':' . mysqli_connect_error () );
    mysqli_set_charset ( $link, DB_CHARSET );
    mysqli_select_db ( $link, DB_DBNAME ) or die ( '指定数据库打开失败<br/>ERROR ' . mysqli_errno ( $link ) . ':' . mysqli_error ( $link ) );
    // echo "连接成功";
    return $link;
}

/*
array(
    'username'=>'king',
    'password'=>'king',
    'age'=>'12',
    'regTime'=>'123123123'
);
INSERT user(username,password,age,regTime) VALUES('king','king','12','123123123');
*/
/**
* 插入操作
* @param object $link
* @param array $data
* @param string $table
* @return boolean
*/
function insert($link,$data,$table){
    $keys = join ( ',', array_keys ( $data ) );
    $vals = "'" . join ( "','", array_values ( $data ) ) . "'";
    $query = "INSERT {$table}({$keys}) VALUES({$vals})";
    $res = mysqli_query ( $link, $query );
    if ($res) {
        return mysqli_insert_id ( $link );
    } else {
        return false;
    }
}

/*
array(
    'username'=>'king123',
    'password'=>'king123',
    'age'=>'32',
    'regTime'=>'123123123'
);
UPDATE user SET username='king123',password='king123',age='32',regTime='123123123' WHERE id=1
*/
/**
* 更新操作
* @param object $link
* @param array $data
* @param string $table
* @param string $where
* @return boolean
*/
function update($link, $data, $table, $where = null) {
    foreach ( $data as $key => $val ) {
        $set .= "{$key}='{$val}',";
    }
    $set = trim ( $set, ',' );
    $where = $where == null ? '' : ' WHERE ' . $where;
    $query = "UPDATE {$table} SET {$set} {$where}";
    $res = mysqli_query ( $link, $query );
    if ($res) {
        return mysqli_affected_rows ( $link );
    } else {
        return false;
    }
}

//DELETE FROM user WHERE id=
/**
* 删除操作
* @param object $link
* @param string $table
* @param string $where
* @return boolean
*/
function delete($link, $table, $where = null) {
    $where = $where ? ' WHERE ' . $where : '';
    $query = "DELETE FROM {$table} {$where}";
    $res = mysqli_query ( $link, $query );
    if ($res) {
        return mysqli_affected_rows ( $link );
    } else {
        return false;
    }
}

/**
* 查询指定记录
* @param object $link
* @param string $query
* @param string $result_type
* @return array|boolean
*/
function fetchOne($link, $query, $result_type = MYSQLI_ASSOC) {
    $result = mysqli_query ( $link, $query );
    if ($result && mysqli_num_rows ( $result ) > 0) {
        $row = mysqli_fetch_array ( $result, $result_type );
        return $row;
    } else {
        return false;
    }
}

/**
* 查询所有记录
* @param object $link
* @param string $query
* @param string $result_type
* @return array|boolean
*/
function fetchAll($link, $query, $result_type = MYSQLI_ASSOC) {
    $result = mysqli_query ( $link, $query );
    if ($result && mysqli_num_rows ( $result ) > 0) {
        while ( $row = mysqli_fetch_array ( $result, $result_type ) ) {
            $rows [] = $row;
        }
        return $rows;
    } else {
        return false;
    }
}

/**
* 得到表中的记录数
* @param object $link
* @param string $table
* @return number|boolean
*/
function getTotalRows($link, $table) {
    $query = "SELECT COUNT(*) AS totalRows FROM {$table}";
    $result = mysqli_query ( $link, $query );
    if ($result && mysqli_num_rows ( $result ) == 1) {
        $row = mysqli_fetch_assoc ( $result );
        return $row ['totalRows'];
    } else {
        return false;
    }
}

/**
* 得到结果集的记录条数
* @param object $link
* @param string $query
* @return boolean
*/
function getResultRows($link, $query) {
    $result = mysqli_query ( $link, $query );
    if ($result) {
        return mysqli_num_rows ( $result );
    } else {
        return false;
    }
}

/**
* @param object $link
*/
function getServerInfo($link) {
    return mysqli_get_server_info ( $link );
}
/**
* @param object $link
*/
function getClientInfo($link) {
    return mysqli_get_client_info ( $link );
}

/**
* @param object $link
*/
function getHostInfo($link){
    return mysqli_get_host_info($link);
}

/**
* @param object $link
*/
function getProtoInfo($link) {
    return mysqli_get_proto_info ( $link );
}

分页功能的封装与实战操作

<?php
    require_once 'mysql.func.php';
    require_once 'config.php';

    // 连接
    $link=connect3();
    // 表名
    $table = 'user';
    // 总条数
    $totalRows = getTotalRows($link,$table);
    echo $totalRows;
    // 每页10条
    $pageSize = 10;
    // 总页数
    $totalPages = ceil($totalRows/$pageSize);
    echo $totalPages;
    // 接受当前页
    $page = $_GET['page']?$_GET['page']:1;
    // 边界处理
    if ($page<1||$page==''||!is_numeric($page)) {
        $page=1;
    }
    if ($page>$totalPages) {
        $page=$totalPages;
    }
    // 计算偏移量
    $offset = ($page-1)*$pageSize;

    $query = "SELECT id,username,password,age,regtime FROM {$table} LIMIT {$offset}, {$pageSize}";
    $users = fetchAll($link, $query);
    //var_dump($users);
    if (!$users) {
        exit("没有用户数据");
    }
    // 形成用户列表
?>

<!DOCTYPE html>

<html>
<head>
    <title>用户列表</title>
</head>

<body>
    <h2>用户列表</h2>
    <table border="1" width="80%" cellpadding="0" cellspacing="0" bgcolor="#ABCDEF">
        <tr>
            <td>编码</td>
            <td>用户名</td>
            <td>密码</td>
            <td>年龄</td>
            <td>注册时间</td>
        </tr>
        <?php foreach($users as $user):?>
        <tr>
            <td><?php echo $user['id'];?></td>
            <td><?php echo $user['username'];?></td>
            <td><?php echo $user['password'];?></td>
            <td><?php echo $user['age'];?></td>
            <td><?php echo $user['regtime'];?></td>
        </tr>
        <?php endforeach;?>
    </table>

    <p>

    <?php
        echo "<a href=mainpage.php?page=1>[首页]</a>";
        $prev = $page == 1 ? 1 : ($page-1);
        echo "<a href=mainpage.php?page=".($prev).">[上一页]</a>";
        for ($i=1; $i<=$totalPages ; $i++) { 
            // 当前页无连接
            if ($page==$i) {
                echo "[{$i}]";
            } else {
                echo "<a href='mainpage.php?page={$i}'>[{$i}]</a>";
            }
        }
        $nextv = $page == $totalPages ? $totalPages : ($page+1);
        echo "<a href=mainpage.php?page=".($nextv).">[下一页]</a>";
        echo "<a href=mainpage.php?page=$totalPages>[尾页]</a>";
    ?>

    </p>

</body>
</html>