pdo连接数据库
来源：互联网 发布：js裁剪上传图片的插件 编辑：程序博客网 时间：2024/06/05 04:08
1.基类ZDO.php:
<?php
abstract class ZDO
{
//用于缓存数据库类型，严禁直接调用
private $dbType;
private $svTables;
private $svFileds;
private $svWheres;
/*
* Creates a PDO instance representing a connection to a database
*/
abstract protected function getDbHandle();
/*
* @return string about database connection information
*/
abstract public static function getDbDsn();
/*
* Prepares a statement for execution and returns a statement object
*/
private function getStatementHandle($sql)
{
return $this->getDbHandle()->prepare($sql);
}
/*
* 对字符是单引号'、双引号"、反斜线\与 NUL（NULL 字符）添加反斜线进行转义
* 当php magic_quotes_gpc 设置为 on 时，它会对所有的 GET、POST 和 COOKIE 数据自动运行 addslashes()，可用get_magic_quotes_gpc()检测，防止双重转义
*/
public function escape($string)
{
if ('oracle' == $this->getDbType()) {
//oracle对单引号的转义
return str_replace("'", "''", $string);
} else {
return addslashes($string);
}
}
/*
* 获取所有记录集
* json_format数组中的项是否json格式
* $sql = "SELECT sku,sl FROM zzm_wms_pd_sku WHERE sku=:sku"; $data = array('sku'=>'xxx');
* @return array/array
*/
public function getAll($sql, array $data=array(), $json_format=false)
{
$sth = $this->getStatementHandle($sql);
$sth->setFetchMode(PDO::FETCH_ASSOC);
$sth->execute($data);
if (!$json_format) {
return $sth->fetchAll();
}
$arr = array();
while ($row = $sth->fetch()) {
$arr[] = json_encode($row);
}
return $arr;
}
/**
* 对对象进行处理
* 访问多条记录
*/
public function queryObj($obj) {
if(!$obj)
return false;
return $obj->fetchAll(PDO::FETCH_ASSOC);
}
/*
* 获取一行记录
* 若查询结果集有多行，出于性能考虑，sql中应含有LIMIT 1(mysql)
* @return array/bool(false)
*/
public function getRow($sql, array $data=array())
{
$sth = $this->getStatementHandle($sql);
$sth->setFetchMode(PDO::FETCH_ASSOC);
$sth->execute($data);
return $sth->fetch();
}
/*
* 获取一行column记录
* 若查询结果集有多行，出于性能考虑，sql中应含有LIMIT 1(mysql)
* @return string/bool(false)
*/
public function getOne($sql, array $data=array())
{
$sth = $this->getStatementHandle($sql);
$sth->setFetchMode(PDO::FETCH_ASSOC);
$sth->execute($data);
return $sth->fetchColumn();
}
/*
* 执行语句 insert update delete
* @return int 影响行数
*/
public function execute($sql, array $data=array())
{
$sth = $this->getStatementHandle($sql);
$sth->execute($data);
return $sth->rowCount();
}
/*
* 执行语句
* @return a PDOStatement object, or FALSE on failure.
*/
public function query($sql)
{
return $this->getDbHandle()->query($sql);
}
/*
* @return int 影响行数
*/
public function insert($tableName, array $data)
{
$field_arr = array_keys($data);
$fields = implode(', ', $field_arr);
$fields_pre = ':' . implode(', :', $field_arr);
$sql = "INSERT INTO {$tableName} ({$fields}) VALUES ({$fields_pre})";
if ('oracle' == $this->getDbType()) {
//对oracle表中的DATE字段特殊处理
$fields_arr = $this->getFields($tableName);
foreach ($fields_arr as $field_name=>$field_type) {
if ('DATE' == $field_type) {
$sql = str_replace(":{$field_name}", "TO_DATE(:{$field_name},'yyyy-mm-dd hh24:mi:ss')", $sql);
}
}
}
$sth = $this->getStatementHandle($sql);
$sth->execute($data);
return $sth->rowCount();
}
/*
* @param MIXED $condition 条件 string(调用前须转义) array(自动转义)
* @return int 影响行数
*/
public function update($tableName, array $data, $condition)
{
$fields_pre = '';
foreach ($data as $key=>$val) {
$fields_pre .= ", {$key}=:{$key}";
}
$fields_pre = ltrim($fields_pre, ', ');
if ('oracle' == $this->getDbType()) {
//对oracle表中的DATE字段特殊处理
$fields_arr = $this->getFields($tableName);
foreach ($fields_arr as $field_name=>$field_type) {
if ('DATE' == $field_type) {
$fields_pre = str_replace(":{$field_name}", "TO_DATE(:{$field_name},'yyyy-mm-dd hh24:mi:ss')", $fields_pre);
}
}
}
if (is_array($condition)) {
$arr = $condition;
$condition = '';
foreach ($arr as $key=>$val) {
$val = $this->escape($val);
$condition .= ($condition ? ' AND ' : '') . "{$key}='{$val}'";
}
}
$sql = "UPDATE {$tableName} SET {$fields_pre} WHERE {$condition}";
$sth = $this->getStatementHandle($sql);
$sth->execute($data);
return $sth->rowCount();
}
/*
* @param $sql
* @return int 影响行数
*/
public function updateEx($sql)
{
if(!$sql)
return false;
$sth = $this->getDbHandle()->query($sql);
return $sth->rowCount();
}
/*
* 批量插入大量数据
* @return int 影响行数
*/
public function massInsert($tableName, array $massData)
{
$tempData = $massData;
$data = array_shift($tempData);
unset($tempData);
$field_arr = array_keys($data);
$fields = implode(', ', $field_arr);
if ('oracle' == $this->getDbType()) {
//针对oracle
$temp_arr = $this->getFields($tableName);
$outer_sql = '';
foreach ($massData as $data) {
$inner_sql = '';
foreach ($data as $field_name=>$val) {
$inner_sql .= ('' == $inner_sql) ? 'SELECT ' : ', ';
//对oracle表中的DATE字段特殊处理
$inner_sql .= ('DATE' == $temp_arr[$field_name]) ? "TO_DATE('" . $this->escape($val) ."','yyyy-mm-dd hh24:mi:ss')" : "'" . $this->escape($val) . "'";
}
$inner_sql .= ' FROM DUAL';
$outer_sql .= ('' == $outer_sql) ? " {$inner_sql}" : " UNION ALL {$inner_sql}";
}
$sql = "INSERT INTO {$tableName} ({$fields}) {$outer_sql}";
} else {
$sql = "INSERT INTO {$tableName} ({$fields}) VALUES";
$outer_sql = '';
foreach ($massData as $data) {
$inner_sql = '';
foreach ($data as $val) {
$inner_sql .= ('' == $inner_sql ? '' : ', ') . "'". $this->escape($val) ."'";
}
$outer_sql .= ('' == $outer_sql ? " " : ", ") . "({$inner_sql})";
}
$sql .= $outer_sql;
}
return $this->execute($sql);
}
/*
* Returns the ID of the last inserted row or sequence value
* Notice: when with transactions in mysql，如果放在事务以外，returns 0 instead of the insert id
*/
public function lastInsertId()
{
return $this->getDbHandle()->lastInsertId();
}
/*
* 开始一个事务（关闭自动提交模式，此时会提交未提交的事务)
* @return bool
*/
public function beginTransaction()
{
return $this->getDbHandle()->beginTransaction();
}
/*
* 提交一个事务(恢复到自动提交模式)
* @return bool
*/
public function commit()
{
return $this->getDbHandle()->commit();
}
/*
* 回滚一个事务(恢复到自动提交模式， 当在一个事务内有类似删除或创建数据表等 DLL 语句时，会自动导致一个隐式地提交。隐式地提交将无法回滚此事务范围内的任何更改。)
* @return bool
*/
public function rollBack()
{
return $this->getDbHandle()->rollBack();
}
/*
* 获取表字段数组 mysql:KEY(PRI/UNI/MUL) oracle:DATA_TYPE(NUMBER/VARCHAR2)
* @return array
*/
public function getFields($tableName)
{
$fields = array();
switch ($this->getDbType()) {
case 'mysql':
$sql = "DESCRIBE {$tableName}";
$arr = $this->getAll($sql);
foreach ($arr as $val) {
$fields[$val['Field']] = $val['Key'];
}
break;
case 'oracle':
$sql = "SELECT COLUMN_NAME,DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME='{$tableName}'";
$arr = $this->getAll($sql);
foreach ($arr as $val) {
$fields[$val['COLUMN_NAME']] = $val['DATA_TYPE'];
}
break;
case 'sqlserver':
$sql = "SELECT NAME FROM SYSCOLUMNS WHERE ID=object_id('{$tableName}')";
$arr = $this->getAll($sql);
foreach ($arr as $val) {
$fields[$val['NAME']] = $val['NAME'];
}
break;
default:
throw new Exception('unknown database type');
}
return $fields;
}
/*
* 获取数据库类型
* @return string
*/
public function getDbType()
{
if ($this->dbType === null) {
if (strpos($this->getDbDsn(), 'mysql:') !== false) {
$this->dbType = 'mysql';
} else if (strpos($this->getDbDsn(), 'oci:') !== false) {
$this->dbType = 'oracle';
} else if(strpos($this->getDbDsn(), 'sqlsrv:') !== false){
$this->dbType = 'sqlserver';
} else {
$this->dbType = 'unkown';
}
}
return $this->dbType;
}
/*-----------------------------------------------SQL Sever 2008--------------------------------------------------------*/
/*$tableName 表名
*/
public function svTable($tableName){
if(!empty($tableName)){
if(is_array($tableName)){
foreach ($tableName as $key=>$val){
$tableNameArr[] = $val;
}
$this->svTables = implode(',', $tableNameArr);
}else{
$this->svTables = $tableName;
}
return $this;
}
return false;
}
/*$filed 字段
*string "$v,$v2,$v3"
*array array('$v','$v2','$v3')
*/
public function svFiled($filed){
if(!empty($filed)){
if(is_array($filed)){
foreach ($filed as $val){
$filedArr[] = "[".$val."]";
}
$this->svFileds = $filedArr;
}else{
$this->svFileds = $filed;
}
return $this;
}else{
if(!empty($this->svTables)){
$filed = $this->getFields($this->svTables);
foreach ($filed as $key=>$v){
$filedArr[] = "[".$v."]";
}
$this->svFileds = $filedArr;
return $this;
}
return false;
}
return false;
}
/*$svwhere 条件
*string
*array array($filed=>'=$v1',$filed2=>'<>$v2');
*/
public function svWhere($svwhere){
if(!empty($svwhere)){
if(is_array($svwhere)){
foreach ($svwhere as $key=>$val){
$whereArr [] = $key.$val;
}
$this->svWheres = implode(' AND ', $whereArr);
}else{
$this->svWheres = $svwhere;
}
return $this;
}
return false;
}
/*录入信息
*$params REQUEST数据
*必须属性
*string $this->svTables
*array $this->svFileds
*/
public function svInsert(){
$params = implode(',',$this->svGetParams());
if(!empty($this->svTables) && !empty($this->svFileds) && !empty($params)){
$fileds = implode(',', $this->svFileds);
$sql = "INSERT INTO {$this->svTables} ({$fileds}) VALUES ({$params})";
return $sql;
}
return false;
}
/*select 输出(循环)
*必须属性
*string $this->svTables
*array $this->svFileds
*/
public function svSelect(){
$filed = implode(',', $this->svFileds);
if(!empty($this->svWheres)){
$sql = "SELECT {$filed} FROM {$this->svTables} WHERE {$this->svWheres}";
return $this->query($sql);
}else{
$sql = "SELECT {$filed} FROM {$this->svTables}";
return $this->query($sql);
}
return false;
}
/*必须属性
*$updata array('$v','$v2','$v3') 与 $this->svFileds 相关
*string $this->svTables
*array $this->svFileds
*string $this->svWheres
*/
public function svUpdate($updata){
if(!empty($updata)){
foreach ($this->svFileds as $key=>$v){
$updataArr[] = $v."='".$updata[$key]."'";
}
$updata = implode(',', $updataArr);
$sql = "UPDATE {$this->svTables} SET {$updata} WHERE {$this->svWheres}";
return $this->execute($sql);
}
return false;
}
/*删除
* 必须属性
* string $this->svTables
* string $this->svWheres
*/
public function svDelete(){
if(!empty($this->svTables) && !empty($this->svWheres)){
$sql = "DELETE FROM {$this->svTables} WHERE ".$this->svWheres;
return $this->execute($sql);
}
return false;
}
/*获取 $start 到 $num 信息
*必须属性
*string $this->svTables
*array $this->svFileds
*/
public function svQuerySql($num, $start = 0, $pk_id = NULL){
if(!empty($this->svTables)){
$pk_id = $this->svGetPkid($this->svTables);
$filed = implode(',', $this->svFileds);
if(!empty($this->svWheres)){
$sql = "SELECT TOP ({$num}) {$filed} FROM {$this->svTables} WHERE {$pk_id} NOT IN (SELECT TOP ({$start}) {$pk_id} FROM {$this->svTables}) AND {$this->svWheres}";
}else{
$sql = "SELECT TOP ({$num}) {$filed} FROM {$this->svTables} WHERE {$pk_id} NOT IN (SELECT TOP ({$start}) {$pk_id} FROM {$this->svTables})";
}
return $sql;
}
return false;
}
/*$tableName 表名
*$pk_id 主键（默认为 $tableName 字段第一位）
*/
public function svGetPkid($tableName,$pk_id = NULL){
if($tableName){
if($pk_id == NULL){
$sql = "SELECT NAME FROM SYSCOLUMNS WHERE ID=object_id('{$tableName}')";
$pk_id_arr = $this->getAll($sql);
$pk_id = $pk_id_arr[0]['NAME'];
}else{
$pk_id = $pk_id;
}
return $pk_id;
}
return false;
}
/*分页涉及
* $table 表名
*$pk_id 主键
*$fileds 字段（默认为所有）
*/
public function svSearchSql($tableName,$fileds="*",$num, $start = 0){
if($tableName){
$pk_id = $this->svGetPkid($tableName);
$sql = "SELECT TOP ({$num}) {$fileds} FROM {$tableName} WHERE {$pk_id} NOT IN (SELECT TOP ({$start}) {$pk_id} FROM {$tableName}) AND ";
return $sql;
}
return false;
}
/*分页涉及
/*$table 表名
*$where 条件（默认为NULL）
*/
public function svSearchLimit($sql,$where = NULL){
if($sql){
if($where == NULL){
$sql = $sql;
}else{
$sql = $sql.$where;
}
return $this->query($sql);
}
return false;
}
/*录入信息
* $table 表名
* $result array($filed=>$val,$filed2=>$val2,$filed(n)=>$val(n))
*/
public function svInsertData($table,$result){
if(is_array($result)){
$result = $this->svAuthData($result);
foreach($result as $key=>$v){
$k[]=$key;
if(!get_magic_quotes_gpc()){
$val[]="'".addslashes($v)."'";
}else{
$val[]="'".$v."'";
}
}
$k=implode(",",$k);
$val=implode(",",$val);
$sql="INSERT INTO {$table}({$k})VALUES({$val})";
return $this->execute($sql);
}
return false;
}
/* REQUEST 验证
*/
public function svGetParams(){
if(isset($_REQUEST)){
$getType = $_SERVER['REQUEST_METHOD'];
switch ($getType){
case 'POST':
$data = $this->svAuthData($_POST);
break;
case 'GET':
$data = $this->svAuthData($_GET);
break;
}
return $data;
}
return false;
}
/*数据验证
* $data string
*/
public function svAuthData($data){
if(!empty($data)){
$Adata = preg_replace("/\<script\>|\<\/script\>|where|update|select|union|insert|delete|load_file|outfile|or|and/i","",$data);
return $Adata;
}
return false;
}
/*删除
*$table 表名
*$where 条件(默认为null)
*/
public function svDeletes($table,$where = NULL){
if($where){
$sql = "DELETE FROM {$table} WHERE ".$where;
return $this->query($sql);
}
return false;
}
/*更新
*$table 表名
*$updata array($filed=>$val,$filed2=>$val2,$filed(n)=>$val(n)) 更新值
*$where string / where array($filed=>'=$val',$filed=>'<=$val',$filed=>'<>$val')条件
*/
public function svUpdates($table,$updata,$where){
if(is_array($updata)){
foreach($updata as $key=>$v){
$k=$key;
if(!get_magic_quotes_gpc()){
$val="'".addslashes($v)."'";
}else{
$val="'".$v."'";
}
$up_key[] = $k."=".$val;
}
if(is_array($where)){
$arr = array();
foreach ($where as $key=>$val){
array_push($arr, $key.$val);
}
$where = implode(" AND ", $arr);
}
$up_key=implode(",",$up_key);
$update = "UPDATE {$table} SET {$up_key} WHERE {$where}";
return $this->query($update);
}
return false;
}
}
2.数据库连接类Dbxx.php：
<?php
class Dbxx extends ZDO
{
private static $dsn = 'mysql:host=localhost;port=3306;dbname=dbname';
//private static $dsn = 'oci:dbname=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=ORCL)));charset=utf8';
//private static $dsn = 'sqlsrv:Server=localhost;Database=dbname';
private static $username = 'username';
private static $password = 'passwd';
private static $dbh;
protected function getDbHandle()
{
if (self::$dbh === null) {
self::$dbh = new PDO(self::$dsn, self::$username, self::$password, array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
self::$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
return self::$dbh;
}
public static function getDbDsn()
{
return self::$dsn;
}
}
- pdo 连接操作数据库
- pdo连接数据库
- PDO连接数据库
- pdo连接数据库
- PDO连接数据库
- php pdo连接数据库
- pdo连接数据库
- pdo连接数据库
- PDO连接数据库
- PHP使用pdo连接数据库
- php pdo 连接db2 数据库
- php pdo 连接MySQL数据库
- php pdo连接mysql数据库
- PDO连接与操作数据库
- PHP数据库对象-PDO拓展 使用pdo连接数据库
- pdo连接数据库及pdo对象的方法介绍
- ADO And PDO 连接数据库例子
- PHP5中使用PDO连接数据库
- overflow
- Android开发要掌握的基本技能(面试常考)
- 进程——wait函数
- xcode升级 插件VVDocumenter-Xcode.xcplugin失效
- EditText编辑监听
- pdo连接数据库
- QT工程文件的条件编译选择与额外的编译参数配置
- Java之Reference使用讲解
- 探讨C++内存回收
- Leetcode89: Unique Paths
- Android每日范例——文字超链接
- 进程——管道
- 小议：End User如何添加自定义的Term？
- Markdown 语法
