php 使用mysql操作类_php 的mysql操作类

亲自测试，网上其他版本没法用，有很多错误，这是本人亲自测试用的，绝对增删改查都可以。

/**

* Created by Netbeans.

* User: Lugo

* Date: 16-7-14

* Version: 1.0.0

* Time: 上午10:50

*/

class MysqlHelper {

const HOST="localhost";

const DATABASE = "demo";

const ENCODING = "UTF8";

private $uname = 'root';

private $pwd = '';

private static $_instance;

private function __construct($db=NULL) {

$this->uname='root';

$this->pwd='';

$db == NULL ? $this->connect($this->uname, $this->pwd) : $this->connect($this->uname, $this->pwd,$db);

}

private function connect($uname,$pwd,$db=NULL) {

$link = mysqli_connect(self::HOST, $uname, $pwd) or die($this->error());

if($db == null) {

mysqli_select_db( $link,self::DATABASE) or die('Error:没有监测到'.self::DATABASE.'数据库!');

} else {

mysqli_select_db($link, $db) or die ('Error:没有监测到'.self::DATABASE.'数据库');

}

//$this->query("SET NAMES".self::ENCODING) or die ('字符集设置错误');

mysqli_set_charset($link, "utf8");

return $link;

}

//单例模式(让类只有一个静态对象)

public static function getInstance($db=null){

if(!(self::$_instance instanceof self)) {

self::$_instance = $db == null ? new self() : new self($db);

}

return self::$_instance;

}

//防止对象被克隆

function __clone() {

trigger_error('不允许克隆',E_USER_ERROR);

}

/**************MySQL基本操作方法**************/

function query($sql) {

$conn = $this->connect("root", "","demo");

return mysqli_query($conn, $sql) ;

}

function fetch_array($query,$result_type=MYSQL_ASSOC) {

return mysqli_fetch_array($query,$result_type) ;

}

//获取一条记录(MYSQL_ASSOC,MYSQL_NUM,MYSQL_BOTH)

function get_one($sql,$result_type = MYSQLI_ASSOC){

$query = $this->query($sql);

$rt = $this->fetch_array($query,$result_type);

return $rt;

}

//获取全部记录

public function get_all($sql,$result_type = MYSQL_ASSOC){

$query = $this->query($sql);

$rt = array();

while ($row = $this->fetch_array($query,$result_type)){

array_push($rt, $row);

}

return $rt;

}

//返回上一次 MySQL操作所影响的记录行数

function affected_rows(){

return mysqli_affected_rows();

}

function result($query,$row){

return mysqli_result($query, $row);

}

function error(){

return mysqli_error();

}

function num_rows($query){

return mysqli_num_rows($query);

}

function num_fields($query){

return mysqli_num_fields($query);

}

function free_result($query){

return mysqli_free_result($query);

}

function insert_id(){

return mysqli_insert_id();

}

//从结果集中取得一行作为数字数组

function fetct_row($query){

return mysqli_fetch_row($query);

}

function version(){

return mysql_get_server_info();

}

function close(){

return mysqli_close();

}

/**************自定义方法:增删改查 *************/

/**

* @param $table 数据表名

* @param null $name 数据表字段名

* @param $var 数据表字段名对应的值

*/

function fn_insert($table,$name,$val=NULL) {

if($val == NULL) {

$this->query("INSERT INTO $table VALUES ($name)");

} else {

$this->query("INSERT INTO $table ($name) VALUES ($val)");

}

}

/**

* @param $table 数据表名

* @param null $where 查找条件的参数和值

*/

function fn_delete($table,$where = NULL) {

if($where == NULL ){

//删除表中所有数据,别乱来啊

$this->query("DELETE FROM ",$table);

} else {

//删除自定义数据

$whereParam = SelectOr::builtWhere($where);

$this->query("DELETE FROM $table WHERE ". $whereParam) ;

}

}

/**

* @param $table 数据表名

* @param $setVal 更新的参数和值

* @param null $where 查找条件的参数和值

*UPDATE `tableName` SET `description` = '中国馆是短发' WHERE `user`.`id` = 6;

*/

function fn_update($table, $setVal , $where ) {

//此处必须用selector::来访问下,作为另一个类,必须要调用

$valParam = SelectOr::builtWhere($setVal,",");

if($where == NULL) {

$this->query("UPDATE $table SET ".$valParam);

}else {

$whereParam = SelectOr::builtWhere($where);

$this->query(("UPDATE $table SET ".$valParam."WHERE".$whereParam));

}

}

/**

* @param $name $selectOr 查找条件设置操作对象,where部分内容

* @param int $selectName 所要选的字段,比如 select id,name from …… id和name就是本部分内容

* @param int $result_type 排序的种类,倒序或正序

*/

function fn_select($table, SelectOr $selectOr = NULL,$selectName=NULL,$result_type=MYSQLI_ASSOC) {

if($selectOr == NULL && $selectName==NULL) {

return $this->get_all("SELECT * FROM ".$table,$result_type);

}elseif ($selectName==NULL) {

$sql = "SELECT * FROM $table ".$selectOr->getRes();

return $this->get_all($sql,$result_type);

}else{

$sql = "SELECT $selectName FROM $table".$selectOr->getRes();

return $this->get_all($sql,$result_type);

}

}

}

//MySQL 选择操作类

class SelectOr {

private $sql;

private static $obj;

function __construct() {

}

public static function getInstance() {

if(!(self::$obj instanceof self)) {

self::$obj = new self;

}

self::$obj->sql = "";

return self::$obj;

}

function where($where){

$this->sql.=" WHERE". $this->builtWhere($where);

return $this;

}

function order($orderBy,$desc = TRUE) {

if($desc) {

$this->sql.=" ORDER BY $orderBy DESC";

}else{

$this->sql.=" ORDER BY $orderBy ASC";

}

return $this;

}

/*

function update($where){

$this->sql.= $this->builtWhere($where);

return $this;

}

*

*/

function limit($num) {

$this->sql.=" LIMIT $num";

return $this;

}

function getRes() {

//$sql就是上面的一个定义的变量,你需要在调用的页面写SelectOr::getInstance()->where(array("uid"=>1))时

//就会在where,order,limit中有$this->sql . = xxxx

return $this->sql;

}

/**

* 封装SQL语句Where

* @param $where

* @param string $connector 链接符号[,|and]

* @param string

*/

function builtWhere($where,$connector="AND") {

if(is_string($where)) {

return $where;

} elseif (is_array($where)) {

$whereParam = " ";

$i = 1;

//下面这个之前用$this来调用的,后来在update时报未定义,所以改成self:: 未分析之间区别

switch (self::arrLevel($where)) {

//如果是一纬数组,即参数形势:array('id'=>'5','uname'=>'lugo')

case 1:

foreach($where as $k=>$v) {

//如果$v是字符串,则用单引号引起来;

if(is_string($v)) {

$v = "'".$v."'";

}

//如果where语句长度是1则不加 and 操作

if($i==count($where)) {

$whereParam .="$k=$v";

} else {

$whereParam .= "$k=$v $connector";

}

$i++;

}

break;

//参数形式:array(array('id','>','5'),array('uname','!=','lugo'))

case 2:

for($k=0; $k

if(count($where[$k]) == 3 ) {

$key = $where[$k][0];

$operate = $where[$k][1];

$param = $where[$k][2];

if(is_string($param)) {

$param = "'".$param."'";

}

//计算添加 "and" 次数

if($i == count($where)) {

$whereParam .= " $key $operate $param";

}else{

$whereParam.=" $key $operate $param $connector";

}

$i++;

}

}

break;

default :

break;

}

return $whereParam;

}

}

//判断数组纬度(只能判断二维)

function arrLevel($array) {

$n = 1;

if(is_array($array)) {

foreach ($array as $val) {

if(is_array($val)) {

$n++;

break;

}

}

}

return $n;

}

}

?>

操作指南

//error_reporting(E_ALL^E_NOTICE^E_WARNING);

require_once("databases.php");

/*操作说明:

//选择其他数据库连接

MysqlHelper::getInstance()->connect("root","","demo");

//选择语句

$res = MysqlHelper::getInstance()->fn_select("blog" , SelectOr::getInstance()->where(array('uid'=>2))->order("uid",TRUE),"uid,title,content,time");

$res = MysqlHelper::getInstance()->fn_select("user", SelectOr::getInstance()->where(array('username'=>'hello3')));

$res = SelectOr::getInstance()->fn_select("library", SelectOr::getInstance()->where(array('max'=>100))->order("id",true)->limit(5),MYSQLI_ASSOC);

//插入的语句格式

$res= MysqlHelper::getInstance()->fn_insert("user","username,description","'hello的55','领导slslslsllslsl'");

//UPDATE `user` SET `description` = '中国馆是短发' WHERE `user`.`id` = 6;

$res = MysqlHelper::getInstance()->fn_update('user',array('description'=>'zzz'),array(array('username','=','hello3'),array('id','=','7')));

$res = MysqlHelper::getInstance()->fn_update('user', SelectOr::getInstance()->update(array('description'=>'这个之前乱码了')), SelectOr::getInstance()->update(array(array('username','=','hello3') ,array('id','=','7'))));

//删除语句

$res = MysqlHelper::getInstance()->fn_delete("user",array(array('username','=','hello3'),array('id','=','8')));

$db->fn_delete("library",array(array('current','>','3'),array('max','!=','150')));

print_r($res);

*/

?>