PHP第三十篇——数据库相关操作与分页功能封装 发表于 2018-12-28 | 评论数: | 阅读次数: | 阅读次数: 本文字数: 9.5k | 阅读时长 ≈ 9 分钟 实现数据库相关操作(MySQLi)与分页功能初步实战与封装,入门学习笔记 连接与配置 123456789101112131415<?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封装操作123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219<?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 );} 分页功能的封装与实战操作12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788<?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> 坚持原创技术分享,您的支持将鼓励我继续创作! 打赏 微信支付 支付宝 本文作者: 曹理鹏@iCocos 本文链接: https://icocos.github.io/2018/12/28/PHP第三十篇——数据库相关操作与分页功能封装/ 版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明出处!