封装的一个mysql_lib.php, 主要是一个分页模型

转发请声明出处...

提供的单元测试中没有给出相关数据库, 如要使用, 请自行设计...

有哪些不足和需要改进的地方, 请不吝赐教...

库文件:

<?php/** * author: selfimpr * mail: [e-mail] * blog: http://blog.csdn.net/lgg201 */#服务器配置信息keydefine('DB_CFG_K_HOST','host');define('DB_CFG_K_USERNAME','username');define('DB_CFG_K_PASSWORD','password');define('DB_CFG_K_PORT','port');#默认字符集define('MYSQL_DEFAULT_CHARSET','UTF8');#分页SQL拆分相关宏define('PAGINATE_COUNT_ALIAS','count');define('PAGINATE_COUNT_SQL_PATTERN','/(?<=(?<!`|\'|")SELECT).*(?=(?<!`|\'|")FROM)/i');define('PAGINATE_COUNT_SQL_REPLACEMENT',' count(*) AS ' . PAGINATE_COUNT_ALIAS . ' ');#分页默认信息define('PAGINATE_DEFAULT_PAGE',1);define('PAGINATE_DEFAULT_PAGESIZE',10);#分页数据模型keydefine('PAGEMODEL_KEY_PAGE_CURRENT','page_current');define('PAGEMODEL_KEY_PAGE_PREV','page_prev');define('PAGEMODEL_KEY_PAGE_NEXT','page_next');define('PAGEMODEL_KEY_PAGE_SIZE','page_size');define('PAGEMODEL_KEY_PAGE_TOTAL','page_total');define('PAGEMODEL_KEY_RECORD_OFFSET','record_offset');define('PAGEMODEL_KEY_RECORD_NUMBER','record_number');define('PAGEMODEL_KEY_RECORD_TOTAL','record_total');define('PAGEMODEL_KEY_DATA','data');/** * 获取数据库连接 * @param mixed $cfg 数据库服务器配置, 数组, key为DB_CFG_K_*系列常量 * @param mixed $dbname 数据库名 * @param mixed $charset 字符集 * @return void */function mysql_get_connection($cfg, $dbname = NULL, $charset = MYSQL_DEFAULT_CHARSET) {$host= $cfg[DB_CFG_K_HOST] . ':' . $cfg[DB_CFG_K_PORT];$username= $cfg[DB_CFG_K_USERNAME];$password= $cfg[DB_CFG_K_PASSWORD];$connection= mysql_connect($host, $username, $password);mysql_switch_db($connection, $dbname);mysql_switch_charset($connection, $charset);return $connection;}/** * 释放数据库连接 * @param mixed $conn 数据库连接对象 * @return void */function mysql_free_connection($conn) {if ( is_resource($conn) ) mysql_close($conn);}/** * 切换数据库 * @param mixed $conn 数据库连接对象 * @param mixed $dbname 数据库名 * @return void */function mysql_switch_db($connection, $dbname) {if ( $dbname ) mysql_select_db($dbname, $connection);}/** * 切换字符集 * @param mixed $conn 数据库连接对象 * @param mixed $charset 字符集名 * @return void */function mysql_switch_charset($connection, $charset) {if ( $charset ) mysql_set_charset($charset, $connection);}/** * 获取数据库中一行记录 * @param mixed $conn 数据库连接对象 * @param mixed $sql 查询语句 * @return void */function mysql_get_row($conn, $sql) {$rs= mysql_query($sql, $conn);$row= mysql_fetch_array($rs);mysql_free_result($rs);return $row;}/** * 获取数据库中多行记录 * @param mixed $conn 数据库连接对象 * @param mixed $sql 查询语句 * @return void */function mysql_get_rows($conn, $sql) {$rows= array();$rs= mysql_query($sql, $conn);while ( $row = mysql_fetch_array($rs) ) $rows[]= $row;mysql_free_result($rs);return $rows;}/** * 向数据库插入数据 * @param mixed $conn 数据库连接对象 * @param mixed $sql INSERT语句 * @param mixed $id 最后一次插入ID * @return void */function mysql_insert($conn, $sql, &$id = NULL) {mysql_query($sql, $conn);$id= mysql_insert_id($conn);return mysql_affected_rows($conn);}/** * 从数据库删除记录 * @param mixed $conn 数据库连接对象 * @param mixed $sql 删除语句 * @return void */function mysql_delete($conn, $sql) {mysql_query($sql, $conn);return mysql_affected_rows($conn);}/** * 更新数据库记录 * @param mixed $conn 数据库连接对象 * @param mixed $sql 更新语句 * @return void */function mysql_update($conn, $sql) {mysql_query($sql, $conn);return mysql_affected_rows($conn);}/** * 分页查询接口 * @param mixed $conn 数据库连接对象 * @param mixed $sql 查询语句 * @param mixed $page 要获取的当前页码 * @param mixed $page_size 每页记录数 * @return $pagemodel分页模型, 数据, key为PAGEMODEL_KEY_*系列常量 */function mysql_paginate($conn, $sql, $page = PAGINATE_DEFAULT_PAGE, $page_size = PAGINATE_DEFAULT_PAGESIZE) {$count_sql= preg_replace(PAGINATE_COUNT_SQL_PATTERN, PAGINATE_COUNT_SQL_REPLACEMENT, $sql);$count_ret= mysql_get_row($conn, $count_sql);$record_total= $count_ret[PAGINATE_COUNT_ALIAS];$page_model= pagemodel_create($page, $page_size, $record_total);$offset= $page_model[PAGEMODEL_KEY_RECORD_OFFSET];$limit= $page_model[PAGEMODEL_KEY_RECORD_NUMBER];$fetch_sql= $sql . " limit $offset, $limit";$fetch_result= mysql_get_rows($conn, $fetch_sql);pm_d($page_model, $fetch_result);return $page_model;}/** * 创建分页模型 * @param mixed $page_current 当前页码 * @param mixed $page_size 每页记录数 * @param mixed $record_total 总记录数 * @param mixed $data 可选. 数据 * @return $pagemodel分页模型, 数据, key为PAGEMODEL_KEY_*系列常量 */function pagemodel_create($page_current, $page_size, $record_total, $data = NULL) {$page_total= ceil($record_total / $page_size);$page_current= max(1, min($page_current, $page_total));$page_prev= max(1, $page_current - 1);$page_next= min($page_total, $page_current + 1);$record_offset= ($page_current - 1) * $page_size;$record_number= $page_current == $page_total ? $record_total - $page_size * ($page_total - 1) : $page_size;return array(PAGEMODEL_KEY_PAGE_CURRENT=> $page_current, PAGEMODEL_KEY_PAGE_PREV=> $page_prev, PAGEMODEL_KEY_PAGE_NEXT=> $page_next, PAGEMODEL_KEY_PAGE_SIZE=> $page_size, PAGEMODEL_KEY_PAGE_TOTAL=> $page_total, PAGEMODEL_KEY_RECORD_OFFSET=> $record_offset, PAGEMODEL_KEY_RECORD_NUMBER=> $record_number, PAGEMODEL_KEY_RECORD_TOTAL=> $record_total, PAGEMODEL_KEY_DATA=> $data, );}/** * 读取/设置分页模型中的当前页码 * @param mixed $pagemodel 分页模型 * @param mixed $page_current 可选. 如果传本参数, 则修改当前页码 * @return void当前页码 */function pm_pc(&$pagemodel, $page_current = NULL) {if ( isset($page_current) ) $pagemodel[PAGEMODEL_KEY_PAGE_CURRENT] = $page_current;return $pagemodel[PAGEMODEL_KEY_PAGE_CURRENT];}/** * 读取/设置分页模型中的前一页页码 * @param mixed $pagemodel 分页模型 * @param mixed $page_prev 可选. 如果传本参数, 则修改前一页页码 * @return void前一页页码 */function pm_pp(&$pagemodel, $page_prev = NULL) {if ( isset($page_prev) ) $pagemodel[PAGEMODEL_KEY_PAGE_PREV] = $page_prev;return $pagemodel[PAGEMODEL_KEY_PAGE_PREV];}/** * 读取/设置分页模型中后一页页码 * @param mixed $pagemodel 分页模型 * @param mixed $page_next 可选. 如果传本参数, 则修改后一页页码 * @return void后一页页码 */function pm_pn(&$pagemodel, $page_next = NULL) {if ( isset($page_next) ) $pagemodel[PAGEMODEL_KEY_PAGE_NEXT] = $page_next;return $pagemodel[PAGEMODEL_KEY_PAGE_NEXT];}/** * 获取/设置分页模型中每页记录数 * @param mixed $pagemodel 分页模型 * @param mixed $page_size 可选. 如果传本参数, 则修改每页记录数 * @return void每页记录数 */function pm_ps(&$pagemodel, $page_size = NULL) {if ( isset($page_size) ) $pagemodel[PAGEMODEL_KEY_PAGE_SIZE] = $page_size;return $pagemodel[PAGEMODEL_KEY_PAGE_SIZE];}/** * 获取/设置分页模型中总页数 * @param mixed $pagemodel 分页模型 * @param mixed $page_total 可选. 如果传本参数, 则修改总页数 * @return void总页数 */function pm_pt(&$pagemodel, $page_total = NULL) {if ( isset($page_total) ) $pagemodel[PAGEMODEL_KEY_PAGE_TOTAL] = $page_total;return $pagemodel[PAGEMODEL_KEY_PAGE_TOTAL];}/** * 获取/设置分页模型中的本页开始记录偏移量 * @param mixed $pagemodel 分页模型 * @param mixed $record_offset 可选. 如果传本参数, 则修改本页开始记录偏移量 * @return void本页开始记录偏移量 */function pm_ro(&$pagemodel, $record_offset = NULL) {if ( isset($record_offset) ) $pagemodel[PAGEMODEL_KEY_RECORD_OFFSET] = $record_offset;return $pagemodel[PAGEMODEL_KEY_RECORD_OFFSET];}/** * 获取/设置分页模型中本页记录数 * @param mixed $pagemodel 分页模型 * @param mixed $record_number 可选. 如果传本参数, 则修改本页记录数 * @return void本页记录数 */function pm_rn(&$pagemodel, $record_number = NULL) {if ( isset($record_number) ) $pagemodel[PAGEMODEL_KEY_RECORD_NUMBER] = $record_number;return $pagemodel[PAGEMODEL_KEY_RECORD_NUMBER];}/** * 获取/设置分页模型中总记录数 * @param mixed $pagemodel 分页模型 * @param mixed $record_total 可选. 如果传本参数, 则修改总记录数 * @return void总记录数 */function pm_rt(&$pagemodel, $record_total = NULL) {if ( isset($record_total) ) $pagemodel[PAGEMODEL_KEY_RECORD_TOTAL] = $record_total;return $pagemodel[PAGEMODEL_KEY_RECORD_TOTAL];}/** * 获取/设置分页模型中的数据 * @param mixed $pagemodel 分页模型 * @param mixed $data 可选. 如果传本参数, 则修改数据 * @return void数据 */function pm_d(&$pagemodel, $data = NULL) {if ( isset($data) ) $pagemodel[PAGEMODEL_KEY_DATA] = $data;return $pagemodel[PAGEMODEL_KEY_DATA];}

单元测试:

<?php/** * author: selfimpr * mail: [e-mail] * blog: http://blog.csdn.net/lgg201 */require_once dirname(__FILE__) . '/mysql_lib.php';$mysql_cfg= array(DB_CFG_K_HOST=> '', DB_CFG_K_USERNAME=> '', DB_CFG_K_PASSWORD=> '', DB_CFG_K_PORT=> '', );function pm_print($pagemodel) {printf("page_first: 1, page_prev: %d, page_current: %d, page_next: %d, page_last: %d, record: %d-%d(%d of %d)\n", pm_pp($pagemodel), pm_pc($pagemodel), pm_pn($pagemodel), pm_pt($pagemodel), pm_ro($pagemodel) + 1, pm_ro($pagemodel) + pm_rn($pagemodel), pm_rn($pagemodel), pm_rt($pagemodel));}define('DB_NAME_EXAMPLE','example');$conn= mysql_get_connection($mysql_cfg, DB_NAME_EXAMPLE);$ret= mysql_paginate($conn, 'SELECT * FROM example WHERE id > 100 order by id desc', $argv[1], $argv[2]);pm_print($ret);