PHP pdo方式连接mysql数据库
黑灯舞
10年前
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的方法都可以连接数据库了