对常用的mysql代码封装 主要包括： 1、执行INSERT,DELETE,UPDATE，返回受影响行 2、执行INSERT语句，返回自增标识 3、执行SELECT语句，返回结果集 4、执行SEELCT语句，返回第一行第一列值 注意
主要包括：
1、执行INSERT,DELETE,UPDATE，返回受影响行
2、执行INSERT语句，返回自增标识
3、执行SELECT语句，返回结果集
4、执行SEELCT语句，返回第一行第一列值
注意：！执行失败，都会throw错误信息。。所有方法都含有PDO版本，因此还有如下PDO操作
5、PDOBindParameter绑定参数
6、PDOPrepare
等等。。
对PHP分页功能封装(需要JQuery支持)
简单的对分页功能进行了封装，方便查询，可以根据条件实现分页
1. [文件] SqlHelper.class.php ~ 7KB 下载(15)
<?php
include_once("Config.class.php");//加载配置文件
class SqlHelper{
/* mysql连接信息 */
private $hostname = null;
private $port = null;
private $username = null;
private $userpwd = null;
private $dbName = null;
//PDO对象stmt
private $PDO = null;
//数据库连接对象
private $conn = null;
//构造函数
function SqlHelper(){
$config = new Config();
$SQLIni = $config->mysqlConfig;
$this->hostname = $SQLIni['hostname'];
$this->port = $SQLIni['port'];
$this->username=$SQLIni['username'];
$this->userpwd = $SQLIni['userpwd'];
$this->dbName = $SQLIni['dbName'];
}
/*************************************** 普通 开始 ********************************/
//打开连接
public function Open(){
try{
$this->conn = new mysqli($this->hostname, $this->username, $this->userpwd, $this->dbName,$this->port);
$this->conn->query('set names utf8');
} catch(Exception $ex) {
die("连接失败1：".iconv("gbk","utf-8",$this->conn->connect_error));
}
}
//关闭连接
public function Close(){
if($this->conn != null) {
$this->conn->close();
}
}
//执行SELECT语句,返回结果集
public function ExecuteReader($sql){
$this->Open();
$result = $this->conn->query($sql);
if($result === false){
//执行失败
$error = $this->conn->error;
$this->Dispose($result);
$this->Close();
throw new Exception($error,0);
} else {
//执行成功
$resArr = array();
while($row = $result->fetch_assoc()){
$resArr[] = $row;
}
$this->Dispose($result);
$this->Close();
return $resArr;
}
}
//执行SELECT语句,返回首行首列
public function ExecuteScalar($sql){
$this->Open();
$result = $this->conn->query($sql);
if($result === false){
//执行失败
$error = $this->conn->error;
$this->Close();
throw new Exception($error,0);
} else {
$row = $result->fetch_row();
$this->Dispose($result);
$this->Close();
//判断是否有结果集
if($row == null){
return null;
}
return $row[0];
}
}
//执行INSERT,UPDATE,DELETE语句。
public function ExecuteNonQuery($sql){
$this->Open();
$result = $this->conn->query($sql);
if($result === false) {//执行失败
//提取错误信息
$error = $this->conn->error;
$this->Close();
throw new Exception($error,0);
} else {//执行成功
//返回受影响行
$ret = $this->conn->affected_rows;
$this->Close();
return $ret;
}
}
//执行INSERT返回自增列。
public function ExecuteReaderIndex($sql){
$this->Open();
$result = $this->conn->query($sql);
if($result === false) {//执行失败
//提取错误信息
$error = $this->conn->error;
$this->Close();
throw new Exception($error,0);
} else {//执行成功
//返回自增编号
$ret = $this->conn->insert_id;
$this->Close();
return $ret;
}
}
//释放结果集
public function Dispose($result) {
if($result === false || $result == null){
return;
}
$result->free();
}
/*************************************** 普通 结束 ********************************/
/*************************************** PDO 开始 ********************************/
//PDO打开
public function PDOOpen(){
$dsn = "mysql:dbname=".$this->dbName.";host=".$this->hostname.";port=".$this->port;
try {
$this->conn = new PDO($dsn, $this->username,$this->userpwd);
} catch(PDOException $e) {
die('<br />不能连接到数据库:<br/>' . $e);
}
}
//PDO关闭
public function PDOClose(){
$this->PDO->closeCursor();
}
//PDO生成预编译
public function PDOPrepare($sql){
$this->PDOOpen();
$this->PDO = $this->conn->prepare($sql);
}
//PDO绑定预编译参数
public function PDOBindParameter($paramStrng,$paramValue){
$this->PDO->bindValue($paramStrng,$paramValue);
}
//PDO执行INSERT,DELETE,UPDASTE查询,返回影响行
public function PDOExecuteNonQuery(){
//获取查询结果集
$result = $this->PDO->execute();
//判断执行是否成功
if($result === false){//执行失败
//执行失败
$error = $this->PDO->errorInfo();//获取错误信息息
$this->PDOClose();//关闭PDO
throw new Exception($error[0] . "|" . $error[1] . "|" . $error[2], 0); //拼接错误信息
} else {//执行成
//记录受影响的行
$retValue = $this->PDO->rowCount();
$this->PDOClose();//关闭
return $retValue;//返回影响行
}
}
//PDO执行SELECT查询,返回结果集
public function PDOExecuteReader(){
$result = $this->PDO->execute();
if($result === false){
//执行失败
$error = $this->PDO->errorInfo();//获取错误信息
$this->PDOClose();//关闭PDO
throw new Exception($error[0] . "|" . $error[1] . "|" . $error[2], 0); //拼接错误信息
} else {
$arr = array();
while($row = $this->PDO->fetch(PDO::FETCH_ASSOC)){
$arr[] = $row;
}
$this->PDOClose();
return $arr;
}
}
//PDO执行SELECT查询，返回首行首列
public function PDOExecuteScalar(){
$result = $this->PDO->execute();
if($result === false){
//执行失败
$error = $this->PDO->errorInfo();//获取错误信息
$this->PDOClose();//关闭PDO
throw new Exception($error[0] . "|" . $error[1] . "|" . $error[2], 0); //拼接错误信息
} else {
$row = $this->PDO->fetch(PDO::FETCH_COLUMN);
$this->PDOClose();
if($row === false){
return null;
}
return $row;
}
}
//PDO执行INSERT,返回自增ID
public function PODExecuteReaderIndex(){
$result = $this->PDO->execute();
if($result === false){ //执行失败
//提取错误信息
$error = $this->PDO->errorInfo();//获取错误信息
$this->PDOClose();//关闭PDO
throw new Exception($error[0] . "|" . $error[1] . "|" . $error[2], 0); //拼接错误信息
} else {
//记录自增编号
$retValue = $this->conn->lastInsertId();
$this->PDOClose();
return $retValue;
}
}
/*************************************** PDO 结束 ********************************/
/*************************************** Transaction 开始 ********************************/
//开启事务
public function BeginTrans(){
$this->ExecuteNonQuery("BEGIN");
}
//提交事务
public function CommitTrans(){
$this->ExecuteNonQuery("COMMIT");
}
//回滚
public function RollbackTrans(){
$this->ExecuteNonQuery("ROLLBACK");
}
/*************************************** Transaction 结束 ********************************/
}
?>
2. [文件] Config.class.php ~ 424B 下载(9)
<?php
class Config {
/*MySQL 配置信息*/
public $mysqlConfig = array(
'hostname' => 'localhost',
'port' => '3306',
'username' => 'root',
'userpwd' => 'root',
'dbName' => 'test'
);
/*分页配置*/
public $pageConfig = array(
"pageSize" => 20,
"pageNow" => 1
);
/*Exception*/
public $ExceptionConfig = array(
);
}
?>
3. [文件] Fenye.class.php ~ 5KB 下载(11)
<?php
require_once("../SqlHelper.class.php");
class Fenye{
private $pageSize = null; //分页大小
private $pageNow = null; //当前页面
private $pageCount = null; //总页数
private $dataCount = null; //数据总数
private $condition = null; //限制条件
private $sqlHelper = null; //数据库操作对象
private $arrList = null; //数据集
private $tbName = null; //表名
private $customParameter = null; //自定义参数
//分页构造函数 当前页,页面大小,表名,限定条件
function Fenye($pageNow,$pageSize,$tbName,$condition) {
$this->pageNow = $pageNow; //记录当前页
$this->pageSize = $pageSize; //分页大小
$this->tbName = $tbName; //表名
$this->condition = $condition; //限制条件
$this->sqlHelper = new SqlHelper(); //数据库操作对象
$this->setPageCount(); //赋值总页数
$this->setArrList(); //赋值数据集
}
//赋值总页数
private function setPageCount(){
$sql = "SELECT COUNT(*) FROM ".$this->tbName." ".$this->condition;
$dataCount = $this->sqlHelper->ExecuteScalar($sql);
$this->dataCount = $dataCount;
$this->pageCount = ceil($dataCount / $this->pageSize);
}
//赋值自定义参数
public function setParameter($param){
$this->customParameter = $param;
}
//赋值数据集
private function setArrList(){
$sql = "SELECT * FROM ".$this->tbName." ".$this->condition." LIMIT ".($this->pageSize * ($this->pageNow - 1)).",".$this->pageSize;
$this->arrList = $this->sqlHelper->ExecuteReader($sql);
}
//读取数据信息
public function getArrList(){
return $this->arrList;
}
//读取总分页数
public function getPageCount(){
return $this->pageCount;
}
//读取当前页
public function getPageNow(){
return $this->pageNow;
}
//读取页面大小
public function getPageSize(){
return $this->getPageSize();
}
//读取总数据数量
public function getListCount(){
return $this->dataCount;
}
//获取自定义参数
public function getParameter(){
return $this->customParameter;
}
private $firstUrl = ""; /* 首页url */
private $backUrl = ""; /* 上一页url */
private $nextUrl = ""; /* 下一页url */
private $lastUrl = ""; /* 尾页url */
//当前页,最多显示页码,跳转的页面
public function getPageHTML($url,$numCount,$condent){
//首页,上一页
if($this->pageNow == 1){
$this->firstUrl = '<li class="text">首页</li>'."\r\n";
$this->backUrl = '<li class="text">上一页</li>'."\r\n";
}else{
$this->firstUrl = '<li class="text"><a href="'.$url.'?pageNow=1'.$condent.'">首页</a></li>'."\r\n";
$this->backUrl = '<li class="text"><a href="'.$url.'?pageNow='.($this->pageNow - 1). $condent . '">上一页</a></li>'."\r\n";
}
//数字开头
$pageNumStart = '<div class="pageList">'."\r\n".'<ul>'."\r\n";
//数字中间部分
$pageNumCont = "";
//数字尾部
$pageNumEnd = '</ul>'."\r\n".'</div>'."\r\n";
$mindPage = floor($numCount / 2);
for($i = $this->pageNow - $mindPage; $i <= $this->pageNow + $mindPage; $i++){
if($i < 1 || $i > $this->pageCount)
continue;
if($i == $this->pageNow){
$pageNumCont .= ('<li class="pageNow">'. $i . '</li>' . "\r\n");
} else {
$pageNumCont .= ('<li><a href="' . $url . '?pageNow=' . $i . $condent . '">' . $i . '</a></li>' . "\r\n");
}
}
//下一页,尾页
if($this->pageNow == $this->pageCount){
$this->lastUrl = '<li class="text">尾页</li>'."\r\n";
$this->nextUrl = '<li class="text">下一页</li>'."\r\n";
}else{
$this->lastUrl = '<li class="text"><a href="'.$url.'?pageNow='.$this->pageCount.$condent.'">尾页</a></li>'."\r\n";
$this->nextUrl = '<li class="text"><a href="'.$url.'?pageNow='.($this->pageNow +1 ).$condent.'">下一页</a></li>'."\r\n";
}
$pageInfo = "<li>第".$this->pageNow."页 / 共".$this->pageCount."页</li>";
$pageGo = '<li>跳至<input type="text" id="pageNow" name="pageNow" class="gopageText" />页<input type="submit" class="gopageBtn" value="GO" /></li>';
$html = $pageNumStart .$this->firstUrl.$this->backUrl. $pageNumCont .$this->nextUrl.$this->lastUrl. $pageGo. $pageInfo. $pageNumEnd;
return $html;
}
}
?>
4. [文件] Fenye.css ~ 1KB 下载(10)
/* CSS Document */
.pageList {
padding:0px;
width:1024px;
margin:0px auto;
overflow:hidden;
}
.pageList ul {
overflow:hidden;
}
.pageList ul,li {
list-style:none;
margin:0px;
padding:0px;
}
.pageList ul li {
text-align:center;
float:left;
border:solid 1px #CCC;
margin-right:6px; /* 间隔大小 */
height:22px; /* 整体高度 */
min-width:22px; /* 每个宽度 */
line-height:22px; /* 整体高度 */
font-size:12px; /* 数字大小 */
color:#000; /* 字体颜色 */
}
.pageList ul li:last-child {
margin-right:0px;
}
.pageList ul li.text {
padding:0px 2px;
}
.pageList ul li.pageNow {
background:#CCC; /* 当前页背景色 */
}
.pageList ul li a {
text-decoration:none;
color:#000;
}
.pageList ul li a:hover {
text-decoration:underline;
color:Blue;
font-weight:bold;
}
.pageList ul li .gopageText {
width:32px;
height:17px;
margin:0px 2px;
padding:0px;
}
.pageList ul li .gopageBtn {
width:25px;
height:23px;
font-size:10px;
margin:0px;
margin-left:6px;
padding:0px;
}
5. [文件] Fenye.js ~ 752B 下载(8)
// JavaScript Document
/* 所有方法 需要JQuery支持 */
$(this).ready(function(e) {
var borderWidth = 1; /* border的宽度(px) */
var marginRight = 6; /* margin-right像素(px) */
var padding = 2; /* 文本内边距 */
//读取所有的页码
var liList = $(".pageList ul li");
//宽度总和
var widthSum = 0;
//读取每一个宽度
for(var i = 0; i < liList.length; i++){
widthSum += liList.eq(i).width()+ (borderWidth * 2);
}
//添加间隔像素
widthSum += (marginRight * (liList.length - 1));
//内边距 最后一个+4用于字体变粗产生的宽度变化
widthSum += 2 * 2 * 4 + 3;
//宽度赋值
$(".pageList ul").width(widthSum);
$(".pageList ul").css("margin","0px auto");
});