php PDO mysql

最新推荐文章于 2021-02-28 22:13:00 发布
pengone
最新推荐文章于 2021-02-28 22:13:00 发布
阅读量619
点赞数
分类专栏： php
php 专栏收录该内容
105 篇文章
订阅专栏
本文介绍了一个PHP中的数据库操作类，该类使用PDO进行数据库连接，并实现了单例模式以确保资源的有效利用。此外，还提供了包括插入、更新、删除等基本CRUD操作的方法，以及用于查询数据的方法。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
<?php

/*//pdo连接信息

$pdo=array("mysql:host=localhost;dbname=demo;charset=utf8","root","");

//开始连接数据库

$db
 = Mysql::newClass();

$db->pdoConnect($pdo);

$updateRow
 = array(

    "user_id"
 => "2",

    "meta_key"
 => "username"

);

//$db->select("wp_usermeta");
 //发送sql

//$result=$db->selectOne();
 //获取一条数据

//$db->selectCount();
 //获取全部

//$db->update("wp_usermeta",$updateRow,"umeta_id=1");
 //更新信息

//$db->insert("wp_usermeta",$updateRow);
 //插入数据

//echo
 $db->lastinsertid(); //获取插入后的id

//$db->delete("wp_usermeta","umeta_id>18");
 //删除数据*/

class Mysql

{

    private static $object;

    private $PDO;

    private $prepare;

    /////单例模式
 start

    private function __construct()

    {

    }

    public static function newClass()

    {

        if(!(self::$object instanceof self))

        {

            self::$object = new self;

        }

        return self::$object;

    }

    public function __clone(){

        trigger_error('Clone
 is not allow!',E_USER_ERROR);

    }

    //////单例模式
 end

    //连接pdo

    public function pdoConnect($address)

    {

        try{

            $this->PDO
 =  new PDO($address[0],$address[1],$address[2]);

            $this->PDO->setAttribute(PDO::ATTR_PERSISTENT,true);

            //设置抛出错误

            $this->PDO->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

            //设置当字符串为空时转换为sql的null

            $this->PDO->setAttribute(PDO::ATTR_ORACLE_NULLS,true);

            //由MySQL完成变量的转义处理

            $this->PDO->setAttribute(PDO::ATTR_EMULATE_PREPARES,
 false);

        }catch (PDOException $e)

        {

            $this->Msg("PDO连接错误信息：".$e->getMessage());

        }

    }

    //错误提示

    private function Msg($the_error=""){

        $html="<html>

                  <head>

                    <meta
 http-equiv='Content-Type' content='text/html;
 charset=UTF-8'/>

                    <title>mysql
 error</title>

                  </head>

                  <body>

                    <div
 style='width:
 50%; height: 200px; border: 1px solid red; font-size: 12px;'>

                       $the_error

                    </div>

                  </body>

               </html>

               ";

        echo $html;

        exit;

    }

    /*

     *

     *
 insert,delete,update操作

     *

     *
 */

    public function insert($table,$row)

    {

       $sql=$this->sqlCreate("insert",$table,$row);

       $result = $this->sqlExec($sql);

    }

    public function update($table,$row,$where)

    {

        $sql=$this->sqlCreate("update",$table,$row,$where);

        $result = $this->sqlExec($sql);

    }

    public function delete($table,$where)

    {

        $sql=$this->sqlCreate("delete",$table,"",$where);

        $result = $this->sqlExec($sql);

    }

    //服务与insert,update,delete，生成sql

    private function sqlCreate($action,$table,$row="",$where="")

    {

        $actionArr = array(

            "insert" => "insert
 into ",

            "update" => "update
 ",

            "delete" => "delete
 from "

        );

        $row = empty($row)
 ? "": $this->rowCreate($row);

        $where = empty($where)
 ? "":"
 where ".$where;

        $sql = $actionArr[$action].$table.$row.$where;

        return $sql;

    }

    //拼成row

    private function rowCreate($row)

    {

       $sql_row="
 set";

       foreach($row as $key=>$val)

       {

          $sql_row.="
 ".$key."='".$val."',";

       }

       return trim($sql_row,",");

    }

    //执行sql，返还影响行数

    private function sqlExec($sql)

    {

         try{

              $result=$this->PDO->exec($sql);

          }catch (PDOException $e)

          {

              $this->Msg($e->getMessage());

          }

         return $result;

    }

    //获取insert插入的id

    public function lastinsertid()

    {

       return $this->PDO->lastinsertid();

    }

  /*

   *

   *
 select 部分

   *
 */

    public function select($table,$fields="", $where="",$orderby="", $sort="",$limit="")

    {

        $fields = empty($fields)
 ? "*":$fields;

        $sqlSelect=$this->sqlCreateSelect($table,$fields,$where,$orderby,$sort,$limit);

        $this->query($sqlSelect,$where);

    }

    //生成select
 sql

    private  function sqlCreateSelect($table,$fields="*", $where="",$orderby="", $sort="",$limit="")

    {

        $whereSql = empty($where)? "
 1=1 ":$this->whereCreate($where);

        $orderbySql = empty($orderby)? "":"
 order by ".$orderby."
 ".$sort;

        $limitSql = empty($limit)? "":"
 limit ".$limit;

        $sql="select
 $fields from $table where ".$whereSql.$orderbySql.$limitSql;

        return $sql;

    }

    private function whereCreate($where)

    {

      $whereSql="";

      foreach($where as $key=>$val)

      {

         $whereSql.="
 ".$key."=:".$key."
 and";

      }

      return $whereSql."
 1=1 ";

    }

    //执行select
 sql

    private function query($sql,$where)

    {

       try{

           $this->prepare
 = $this->PDO->prepare($sql);

       }catch (PDOException $e)

       {

           $this->Msg("预处理sql出错信息:".$e->getMessage()."<br>sql:(".$sql.")");

       }

       empty($where)? "":$this->bind($where);

       $this->prepare
 ->execute();

    }

    private function bind($where)

    {

        foreach($where as $key=>$val)

        {

            $this->prepare->bindValue(":".$key,$val);

        }

    }

    /*select获取数据*/

    //获取一条

    public function selectOne()

    {

        $result=$this->prepare->fetch(PDO::FETCH_ASSOC);

        return $result;

    }

    //获取全部数据

    public function selectAll()

    {

        $result=$this->prepare->fetchAll(PDO::FETCH_ASSOC);

        return $result;

    }

     //获取查询记录数

    public function selectCount()

    {

        $total = $this->prepare->rowCount();

        return $total;

    }

}