由于将服务器升级到 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】