PHP pdo方式連接mysql數據庫

PDO方法連接數據庫是一種比較流行的方法：它的操作比較簡單
如果只是簡單的使用的話：

// 數據庫連接
        try {
            $this->pdo = new \PDO("mysql:host=" . $hostname . ";port=" . $port . ";dbname=" . $database, $username, $password, array(\PDO::ATTR_PERSISTENT => true));
        } catch(\PDOException $e) {
            trigger_error('Error: Could not make a database link ( ' . $e->getMessage() . '). Error Code : ' . $e->getCode() . ' <br />');
        }
// 調用方法很簡單
$query=$pdo->query('SELECT id,name FROM user');
$row=$query->fetch();

但我們更多的使用是用于實際的項目中，有必要把pdo的操作方式封裝成一個類

class mPDO 
{

    public $SqlBug = ''; // 記錄mysql調試語句，可以查看完整的執行的mysql語句
    private $pdo = null; // pdo連接
    private $statement = null; 

    public function __construct($hostname, $username, $password, $database, $charset, $port = "3306") 
    {
        try {
            $this->pdo = new \PDO("mysql:host=" . $hostname . ";port=" . $port . ";dbname=" . $database, $username, $password, array(\PDO::ATTR_PERSISTENT => true));
        } catch(\PDOException $e) {
            trigger_error('Error: Could not make a database link ( ' . $e->getMessage() . '). Error Code : ' . $e->getCode() . ' <br />');
        }

        $this->pdo->exec("SET NAMES '" . $charset . "'");
        $this->pdo->exec("SET CHARACTER SET " . $charset);
        $this->pdo->exec("SET CHARACTER_SET_CONNECTION=" . $charset);
        $this->pdo->exec("SET SQL_MODE = ''");

    }

    public function prepare($sql) 
    {
        $this->statement = $this->pdo->prepare($sql);
         $this -> SqlBug .= "\n". '<!--DebugSql: ' . $sql . '-->' . "\n";
    }

    public function bindParam($parameter, $variable, $data_type = PDO::PARAM_STR, $length = 0) 
    {
        if ($length) {
            $this->statement->bindParam($parameter, $variable, $data_type, $length);
        } else {
            $this->statement->bindParam($parameter, $variable, $data_type);
        }
    }

    public function execute() 
    {
        try {
            if ($this->statement && $this->statement->execute()) {
                $data = array();

                while ($row = $this->statement->fetch(PDO::FETCH_ASSOC)) {
                    $data[] = $row;
                }

                $result = new stdClass();
                $result->row = (isset($data[0])) ? $data[0] : array();
                $result->rows = $data;
                $result->num_rows = $this->statement->rowCount();
            }
        } catch(PDOException $e) {
            trigger_error('Error: ' . $e->getMessage() . ' Error Code : ' . $e->getCode());
        }
    }

    public function query($sql, $params = array()) 
    {
        $this->statement = $this->pdo->prepare($sql);
        $result = false;

        $this -> SqlBug .= "\n". '<!--DebugSql: ' . $sql . '-->' . "\n";

        try {
            if ($this->statement && $this->statement->execute($params)) {
                $data = array();

                while ($row = $this->statement->fetch(\PDO::FETCH_ASSOC)) {
                    $data[] = $row;
                }

                $result = new \stdClass();
                $result->row = (isset($data[0]) ? $data[0] : array());
                $result->rows = $data;
                $result->num_rows = $this->statement->rowCount();
            }
        } catch (PDOException $e) {
            trigger_error('Error: ' . $e->getMessage() . ' Error Code : ' . $e->getCode() . ' <br />' . $sql);
            exit();
        }

        if ($result) {
            return $result;
        } else {
            $result = new stdClass();
            $result->row = array();
            $result->rows = array();
            $result->num_rows = 0;
            return $result;
        }
    }

    public function executeUpdate($sql)
    {
        return $this->pdo->exec($sql);
    }

    /**
     * 獲得所有查詢條件的值 
     */
    public function fetchAll($sql, $params = array()) 
    {
        $rows = $this->query($sql, $params)->rows;
        return !empty($rows) ? $rows : false;
    }

    /**
     * 獲得單行記錄的值
     */
    public function fetchAssoc($sql, $params = array()) 
    {
        $row = $this->query($sql, $params)->row;
        return !empty($row) ? $row : false;
    }

    /**
     * 獲得單個字段的值
     */
    public function fetchColumn($sql, $params = array()) 
    {
        $data = $this->query($sql, $params)->row; 
        if(is_array($data)) {
            foreach ($data as $value) {
                return $value;
            }
        }

        return false;
    }

    /**
     * 返回statement記錄集的行數
     */
    public function rowCount($sql, $params = array())
    {
        return $this->query($sql, $params)->num_rows;
    }

    /**
     * 插入數據
     * @param   string  $table      表名
     * @param   Array   $data       數據
     * @return  int     InsertId    新增ID
     */
    public function _insert($table, $data)
    {
        if (!is_array($data) || count($data) == 0) {
            return 0;
        }

        $field_arr = array();
        foreach ($data as $key=>$val) {
            $field_arr[] = " `$key` = '$val' ";
        }

        $sql = "INSERT INTO " . $table . " SET " . implode(',', $field_arr);
        $this -> query($sql);

        return $this->getLastId();
    }

    /**
     * 更新數據
     * @param   string  $table      表名
     * @param   Array   $data       數據
     * @param   string  $where      更新條件
     * @return  int  影響數
     */
    public function _update($table, $data, $where = '')
    {
        // $this -> Affected = 0;
        if(empty($where)) {
            return 0;
        }
        if (!is_array($data) || count($data) == 0) {
            return 0;
        }

        $field_arr = array();
        foreach ($data as $key=>$val)
            $field_arr[] = " `$key` = '$val' ";

        $sql = "UPDATE " . $table . " SET " . implode(',', $field_arr) . " WHERE " . $where;

        return $this->pdo->exec($sql);
    }

    /**
     * 獲得影響集合中
     */
    public function _delete($table, $where = "")
    {
        if(empty($where)) {
            return 0;
        }
        $sql = "DELETE FROM " . $table . " WHERE " . $where;

        return $this->pdo->exec($sql);
    }

    /**
     * 獲得影響集合中
     */
    public function countAffected() 
    {
        if ($this->statement) {
            return $this->statement->rowCount();
        } else {
            return 0;
        }
    }

    /*
     * 獲得插入id
     */
    public function getLastId() 
    {
        return $this->pdo->lastInsertId();
    }

    public function escape($value) {
        $search = array("\\", "\0", "\n", "\r", "\x1a", "'", '"');
        $replace = array("\\\\", "\\0", "\\n", "\\r", "\Z", "\'", '\"');
        return str_replace($search, $replace, $value);
    }

    /**
     * 返回錯誤信息也包括錯誤號
     */
    public function errorInfo() 
    {
        return $this->statement->errorInfo();
    }

    /**
     * 返回錯誤號
     */
    public function errorCode() 
    {
        return $this->statement->errorCode();
    }

    public function __destruct()
    {
        $this->pdo = null;
    }
}

// 當然還有mysql_connect,mysqli的方法都可以連接數據庫了

轉載: php pdo方式連接mysql數據庫