1. <?php
  2. /*//pdo连接信息
  3. $pdo=array("mysql:host=localhost;dbname=demo;charset=utf8","root","");
  4. //开始连接数据库
  5. $db = Mysql::newClass();
  6. $db->pdoConnect($pdo);
  7. $updateRow = array(
  8. "user_id" => "2",
  9. "meta_key" => "username"
  10. );
  11. //$db->select("wp_usermeta"); //发送sql
  12. //$result=$db->selectOne(); //获取一条数据
  13. //$db->selectCount(); //获取全部
  14. //$db->update("wp_usermeta",$updateRow,"umeta_id=1"); //更新信息
  15. //$db->insert("wp_usermeta",$updateRow); //插入数据
  16. //echo $db->lastinsertid(); //获取插入后的id
  17. //$db->delete("wp_usermeta","umeta_id>18"); //删除数据*/
  18. class Mysql
  19. {
  20. private static $object;
  21. private $PDO;
  22. private $prepare;
  23. /////单例模式 start
  24. private function __construct()
  25. {
  26. }
  27. public static function newClass()
  28. {
  29. if(!(self::$object instanceof self))
  30. {
  31. self::$object = new self;
  32. }
  33. return self::$object;
  34. }
  35. public function __clone(){
  36. trigger_error('Clone is not allow!',E_USER_ERROR);
  37. }
  38. //////单例模式 end
  39. //连接pdo
  40. public function pdoConnect($address)
  41. {
  42. try{
  43. $this->PDO = new PDO($address[0],$address[1],$address[2]);
  44. $this->PDO->setAttribute(PDO::ATTR_PERSISTENT,true);
  45. //设置抛出错误
  46. $this->PDO->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
  47. //设置当字符串为空时转换为sql的null
  48. $this->PDO->setAttribute(PDO::ATTR_ORACLE_NULLS,true);
  49. //由MySQL完成变量的转义处理
  50. $this->PDO->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
  51. }catch (PDOException $e)
  52. {
  53. $this->Msg("PDO连接错误信息:".$e->getMessage());
  54. }
  55. }
  56. //错误提示
  57. private function Msg($the_error=""){
  58. $html="<html>
  59. <head>
  60. <meta http-equiv='Content-Type' content='text/html; charset=UTF-8'/>
  61. <title>mysql error</title>
  62. </head>
  63. <body>
  64. <div style='width: 50%; height: 200px; border: 1px solid red; font-size: 12px;'>
  65. $the_error
  66. </div>
  67. </body>
  68. </html>
  69. ";
  70. echo $html;
  71. exit;
  72. }
  73. /*
  74. *
  75. * insert,delete,update操作
  76. *
  77. * */
  78. public function insert($table,$row)
  79. {
  80. $sql=$this->sqlCreate("insert",$table,$row);
  81. $result = $this->sqlExec($sql);
  82. }
  83. public function update($table,$row,$where)
  84. {
  85. $sql=$this->sqlCreate("update",$table,$row,$where);
  86. $result = $this->sqlExec($sql);
  87. }
  88. public function delete($table,$where)
  89. {
  90. $sql=$this->sqlCreate("delete",$table,"",$where);
  91. $result = $this->sqlExec($sql);
  92. }
  93. //服务与insert,update,delete,生成sql
  94. private function sqlCreate($action,$table,$row="",$where="")
  95. {
  96. $actionArr = array(
  97. "insert" => "insert into ",
  98. "update" => "update ",
  99. "delete" => "delete from "
  100. );
  101. $row = empty($row) ? "": $this->rowCreate($row);
  102. $where = empty($where) ? "":" where ".$where;
  103. $sql = $actionArr[$action].$table.$row.$where;
  104. return $sql;
  105. }
  106. //拼成row
  107. private function rowCreate($row)
  108. {
  109. $sql_row=" set";
  110. foreach($row as $key=>$val)
  111. {
  112. $sql_row.=" ".$key."='".$val."',";
  113. }
  114. return trim($sql_row,",");
  115. }
  116. //执行sql,返还影响行数
  117. private function sqlExec($sql)
  118. {
  119. try{
  120. $result=$this->PDO->exec($sql);
  121. }catch (PDOException $e)
  122. {
  123. $this->Msg($e->getMessage());
  124. }
  125. return $result;
  126. }
  127. //获取insert插入的id
  128. public function lastinsertid()
  129. {
  130. return $this->PDO->lastinsertid();
  131. }
  132. /*
  133. *
  134. * select 部分
  135. * */
  136. public function select($table,$fields="", $where="",$orderby="", $sort="",$limit="")
  137. {
  138. $fields = empty($fields) ? "*":$fields;
  139. $sqlSelect=$this->sqlCreateSelect($table,$fields,$where,$orderby,$sort,$limit);
  140. $this->query($sqlSelect,$where);
  141. }
  142. //生成select sql
  143. private function sqlCreateSelect($table,$fields="*", $where="",$orderby="", $sort="",$limit="")
  144. {
  145. $whereSql = empty($where)? " 1=1 ":$this->whereCreate($where);
  146. $orderbySql = empty($orderby)? "":" order by ".$orderby." ".$sort;
  147. $limitSql = empty($limit)? "":" limit ".$limit;
  148. $sql="select $fields from $table where ".$whereSql.$orderbySql.$limitSql;
  149. return $sql;
  150. }
  151. private function whereCreate($where)
  152. {
  153. $whereSql="";
  154. foreach($where as $key=>$val)
  155. {
  156. $whereSql.=" ".$key."=:".$key." and";
  157. }
  158. return $whereSql." 1=1 ";
  159. }
  160. //执行select sql
  161. private function query($sql,$where)
  162. {
  163. try{
  164. $this->prepare = $this->PDO->prepare($sql);
  165. }catch (PDOException $e)
  166. {
  167. $this->Msg("预处理sql出错信息:".$e->getMessage()."<br>sql:(".$sql.")");
  168. }
  169. empty($where)? "":$this->bind($where);
  170. $this->prepare ->execute();
  171. }
  172. private function bind($where)
  173. {
  174. foreach($where as $key=>$val)
  175. {
  176. $this->prepare->bindValue(":".$key,$val);
  177. }
  178. }
  179. /*select获取数据*/
  180. //获取一条
  181. public function selectOne()
  182. {
  183. $result=$this->prepare->fetch(PDO::FETCH_ASSOC);
  184. return $result;
  185. }
  186. //获取全部数据
  187. public function selectAll()
  188. {
  189. $result=$this->prepare->fetchAll(PDO::FETCH_ASSOC);
  190. return $result;
  191. }
  192. //获取查询记录数
  193. public function selectCount()
  194. {
  195. $total = $this->prepare->rowCount();
  196. return $total;
  197. }
  198. }