一个好用的mysql pdo php操作类

由于将服务器升级到 centos 7.2、 php 7.0 、 mysql 5.6.28，导致之前一直使用的 mysql php 操作类不可使用，只能直接过渡到 pdo 。

在国外的网站找到一个好用的 mysql pdo php操作类，根据自己的需求，修改如下：

<?php


    //001 用于  连接 memcache/memcached 
<pre name="code" class="php">    <span style="font-family: Arial, Helvetica, sans-serif;">//【注：Linux服务器是 memcached ，Windows 服务器或pc 是memcache】</span>

// 需根据操作系统的不同，注释或启用不同的代码 function linkMem(){//以下只能用于 windows ，不能用于 linux $mem = new Memcache;$mem->connect("localhost",11211);//以下只能用于 linux ，不能用于 windows //$mem = new Memcached; //$mem->addServer('127.0.0.1', 11211);return $mem;} function setMem($key,$val,$qixian){//以下只能用于 windows ，不能用于 linux $mem = new Memcache;$mem->connect("localhost",11211);$mem->set($key, $val,false,$qixian);//以下只能用于 linux ，不能用于 windows //$mem = new Memcached; //$mem->addServer('127.0.0.1', 11211);//$mem->set($key, $val,$qixian);}

    //002  mysql 操作类  
    function selectDb($sql){// 返回结果集
		$db = new MySql();
		return $db->select($sql); 
	}
	// selectRows($sql,1) 返回结果集和结果集的数量组成的数组 
	// 返回结果集数量 
    function selectRows($sql,$sig=null){		
		$db     = new MySql();
		$result = $db->select($sql);
		$count  = $db->getNumRows($result);
		if($sig == 1){
			return array('row'=>$result,'count'=>$count);
		}else{
			return $count;
		}		
	}
    function insertDb($sql,$sig=null){
		$db = new MySql();
		$result = $db->insert($sql);
		if($result && $sig==1){
			return $db->getId();
		}else{
			return $result;
		}
	}

    // delete、update 均可使用
    function updateDb($sql,$sig=null){
<span style="white-space:pre">		</span>$db        = new MySql();
<span style="white-space:pre">		</span>$result    = $db->insert($sql);
<span style="white-space:pre">		</span>if($result && $sig==1){
<span style="white-space:pre">			</span>return $result;
<span style="white-space:pre">		</span>}
<span style="white-space:pre">	</span>}



    //000  mysql pdo 操作类 
	class MySql {

		//protected $host     = "[HOST]";
		//protected $user     = "[USER]";
		//protected $pass     = "[PASSWORD]";
		//protected $dbName   = "[NAMEDB]";

		protected $host     = "127.0.0.1:3306";
		protected $user     = "root";
		protected $pass     = "xxx";
		protected $dbName   = "xxdb";
		
		protected $error; 
		protected $inforResult;
		protected $numRows;
		protected $numCols;
		protected $id;
		protected $dataJson;
		protected $transacao;
		protected $sql;
		protected $converterUtf8=false;
		protected $uppercase=false;

		protected $connection;

		function __construct(){

		}
	   
		public function setDbName($dbName){
			if (strlen(trim($dbName)) > 0 ){
				$this->dbName = $dbName;
				return true;
			}
			else{
				return false;
			}

		}

		public function getDbName(){
			return $this->dbName;
		}

		public function setHost($host){
			if (strlen(trim($host)) > 0 ){
				$this->host = $host;
			}
		}

		public function getHost(){
			return $this->host;
		}

		public function setUser($user){
			if (strlen(trim($user)) > 0 ){
				$this->user = $user;
			}
		}

		public function getUser(){
			return $this->user;
		}


		public function setPass($senha){
			if (strlen(trim($senha)) > 0 ){
				$this->pass = $senha;
			}
		}

		function getErros(){
			$erros = print_r($this->error, true);
			return $erros;
		}

		public function setUppercase($bool){
			if(is_bool($bool)){
				$this->uppercase = $bool;
			}
		}

		public function getId(){
			return $this->id;
		}

		public function getSql(){
			return $this->sql;
		}

		public function getNumRows(){
			return $this->numRows;
		}

		public function getNumCols(){
			return $this->numCols;
		}

		private function connect(){
			try{
				$this->connection = new PDO("mysql:host=".$this->host.";dbname=".$this->dbName, $this->user, $this->pass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8';"));
				return true;
			}
			catch (PDOException $e) {
				print "Error: " . $e->getMessage() . "<br/>";
				die();
			}
		}

		private function logout(){
			$this->connection = null;
			$this->sql = null;
		}

		public function setUtf8($bool){
			if(is_bool($bool)){
				$this->converterUtf8 = $bool;
			}
		}

		public function setSqlScript($sql, $complementation=false){

			$arrayExp[] = "/''/";
			$arrayExp[] = "/' '/";
			$arrayExp[] = "/\" \"/";
			$arrayExp[] = "/\"\"/";
			$arrayExp[] = "/\"null\"/";
			$arrayExp[] = "/\"NULL\"/";
			$arrayExp[] = "/'null'/";
			$arrayExp[] = "/'NULL'/";
			$sql = preg_replace($arrayExp, "null", $sql);
			$sql = preg_replace($arrayExp, "null", $sql);

			$arrayExp = null;
			$arrayExp[] = "/,[ \t\n\r\f\v]*,/";
			$arrayExp[] = "/,,/";
			$arrayExp[] = "/, ,/";
			$sql = preg_replace($arrayExp, ",\n null,", $sql);
			$sql = preg_replace($arrayExp, ",\n null,", $sql);

			$arrayExp = null;
			$arrayExp = "/=[ \t\n\r\f\v]*,/";
			$sql = preg_replace($arrayExp, "= null,", $sql);
			$sql = preg_replace($arrayExp, "= null,", $sql);
		   
			
			if($complementation == false){
				$this->sql = null;
				$this->sql = $sql;
			}
			else{
				$this->sql .= $sql."; \n";
			}
		}

		 public function select($sql){

			$this->connect();
			if($this->connection === false){
				die("Error.");
			}
			else{

				$this->setSqlScript($sql);
				$pdo = $this->connection;

				$db = $pdo->prepare($this->sql);

				$result = $db->execute();

				if($result === true){
					$data = $db->fetchAll(PDO::FETCH_ASSOC);

					$this->id = $pdo->lastInsertId();
					$this->numRows = $db->rowCount();
					$this->numCols = $db->columnCount();
					$pdo = null;

					if($this->uppercase == false){
						if(is_array($data)){
							foreach($data as $key=> $reg){
								foreach($reg as $campo=>$val){
									$val = ($this->converterUtf8==true)?utf8_encode($val):$val;
									$return[$key][$campo] = $val;
								}
							}
						}
						return $return;
					}else{
						
						if(is_array($data)){
							foreach($data as $key=> $reg){
								foreach($reg as $campo=>$val){
									$val = ($this->converterUtf8==true)?utf8_encode(strtoupper($val)):strtoupper($val);
									$returnUppercase[$key][$campo] = $val;
								}
							}
						}

						return $returnUppercase;
					}
				}else{

					$this->error = $db->errorInfo();
					$this->error['sql'] = $this->sql;
					return die($this->getErros());
				}
				$this->logout();
			}
		}


		public function insert($sql)
		{
			$this->connect();
			if($this->connection === false){
				die("Error.");
			}else{
				$sql = ($this->converterUtf8==true)?utf8_decode($sql):$sql;
				$this->setSqlScript($sql);
				$pdo = $this->connection;

				try {
					$transacao = $pdo->beginTransaction();
					if($transacao === true){
						$db = $pdo->prepare($this->sql);

						$result = $db->execute();
						if($result===true){
							$this->id = $pdo->lastInsertId(); 
							$this->numRows = $db->rowCount();
							$this->numCols = $db->columnCount();
							
							$commit = $pdo->commit();

							if($commit === true){
								$this->sql = null;
								return $result;
							}else{
								$this->error = $db->errorInfo();
								$this->error['sql'] = $this->sql;
								return die("Error commit: " .$this->getErros());
							}
						}else{
							$this->error = $db->errorInfo();
							$this->error['sql'] = $this->sql;
							return die("Error query: " .$this->getErros());
							$this->sql = null;
						}

						$pdo = null;
						$this->sql = null;
					}
					else{
						$pdo = null;
						$this->error['sql'] = $this->sql;
						return die("Error: " .$this->getErros());
						$this->sql = null;
					}
					$this->sql = null;

				}
				catch (PDOException $e) {
					$pdo->rollBack();
					$this->error = $db->errorInfo();
					$this->error['sql'] = $this->sql;
					$this->sql = null;
					
					return die("Failed: " . $e->getMessage().$this->getErros());
				}

				$this->logout();
			}

		}

		public function multInsert($sqlArray){

			$this->connect();
			if($this->connection === false){
				die("Error.");
			}

			if(is_array($sqlArray)==false){
				die('Error Script.');
			}

			else{
				$this->sql = null;
				$pdo = $this->connection;
				
				$Transaction = $pdo->beginTransaction();
				$Transaction = true;
				
				try {
				   
					if($transacao === true){
						foreach ($sqlArray as $sql) {
							$this->setSqlScript($sql);
							
							$db         = $pdo->prepare($this->sql);
							$result  = $db->execute();

							if($result===true){
								$this->id = $pdo->lastInsertId(); 
								$this->numRows = $db->rowCount();
								$this->numCols = $db->columnCount();
							}else{
								$this->error = $db->errorInfo();
								$this->error['sql'] = $this->sql;
								return die("Error query: " .$this->getErros());
							}
						}

						$commit = $pdo->commit();
						if($commit === true){
							$pdo = null;
							return true;
						}else{
							$pdo->rollBack();
							$this->error = $db->errorInfo();
							$this->error['sql'] = $this->sql;
							return die("Error commit: " .$this->getErros());
						}
						
					}
					else{
						$pdo = null;
						$this->error['sql'] = $this->sql;
						return die("Error: " .$this->getErros());
					}

				}
				catch (PDOException $e) {
					$pdo->rollBack();
					$this->error = $db->errorInfo();
					$this->error['sql'] = $this->sql;
					return die("Failed: " . $e->getMessage().$this->getErros());
					$pdo = null;
				}

				$this->logout();
			}

		}
	}

?>

以下是简单的测试代码：

	$city                 = '厦门市';
	echo "<br> city 是【".$city."】<br>";
	$sql3 = "SELECT `city_id`, `prov_id` FROM `0city` WHERE `city_name`='".$city."'";   
	$row3                 = selectDb($sql3);
	$arr3                 = selectRows($sql3,1);
	$count3               = $arr3['count'];
	if($count3 == 1){
		$city_id          = $arr3['row'][0]['city_id'];
		$province_id      = $arr3['row'][0]['prov_id'];
		$city_id2         = $row3[0]['city_id'];
		$province_id2     = $row3[0]['prov_id'];
	}else{
		$city_id          = 0;
		$province_id      = 0;
	}

	echo "<pre>";
	print_r($row3);
	echo "</pre>";
	
	echo "<pre>";
	print_r($arr3);
	echo "</pre>";
	
	echo "<br> city_id 是【".$city_id."】<br>";
	echo "<br> province_id 是【".$province_id."】<br>"; 
	echo "<br> city_id2 是【".$city_id2."】<br>";
	echo "<br> province_id2 是【".$province_id2."】<br>"; 


	$sql4	= "select * from a order by id desc limit 3";
	$row4                 = selectDb($sql4);
	echo "<pre>";
	print_r($row4);
	echo "</pre>";


	// INSERT 
	$sql5 = "INSERT INTO a (a,qq) VALUES ('你好！','".time()."')"; 
	$id = insertDb($sql5,1);
	echo "<br> id 是【".$id."】<br>";

	// UPDATE delete drop 等
	$var = '90'; 
	$sql6 = "UPDATE a SET a = 'queryUpdate', qq='".time()."' WHERE id = $var ";
	$update = updateDb($sql6,1);
	echo " <br>time 是【".time()."】<br>";
	echo " <br>update 是【".$update."】<br>";

下面是上述测试代码的打印结果：

city 是【厦门市】

city 是【厦门】

Array
(
    [0] => Array
        (
            [city_id] => 1207
            [prov_id] => 23
        )

)

Array
(
    [row] => Array
        (
            [0] => Array
                (
                    [city_id] => 1207
                    [prov_id] => 23
                )

        )

    [count] => 1
)

city_id 是【1207】

province_id 是【23】

city_id2 是【1207】

province_id2 是【23】

Array
(
    [0] => Array
        (
            [id] => 110
            [a] => 你好！
            [QQ] => 54981217
        )

    [1] => Array
        (
            [id] => 109
            [a] => 你好！
            [QQ] => 54981217
        )

    [2] => Array
        (
            [id] => 108
            [a] => 你好！
            [QQ] => 54981217
        )

)

id 是【111】

time 是【1452017206】

update 是【1】