PHP's PDO-based database operation class "supports MySQL, SQL Server, and Oracle"

This article mainly introduces the PHP based on PDO database operation class, can achieve basic database connection, add and revise check, close connection and other operations, but also support for MySQL, SQL Server and Oracle database operations, the need for friends can refer to the following

This article describes the PHP database operation class based on PDO. Share to everyone for your reference, as follows:

Working with SQL Server, Oracle is using this class, which was improved on the basis of others, and now shares the next

<?phpclass pdodb{protected $pdo;  protected $res;  protected $config;    /* Constructor */function __construct ($config) {$this->config = $config;  $this->connect (); }/* Database connection */Public function connect () {try {$this->pdo= new PDO ($this->config[' DSN '), $this->config['       Username '], $this->config[' password ');//$dbh = new PDO (' Mysql:host=localhost;dbname=test ', $user, $pass);    $this->pdo->query ("Set names UTF8"); }catch (Exception $e) {echo ' database connection failed, details: '. $e->getmessage ().      ' Please database connection information in the configuration file ';    Exit (); }/* IF ($this->config[' type ']== ' Oracle ') {$this->pdo->query ("Set names {$this->config[' charset ']};"    );    }else{$this->pdo->query ("Set names {$this->config[' charset ']};");    }///The results are serialized into Stdclass//$this->pdo->setattribute (Pdo::attr_default_fetch_mode, pdo::fetch_obj);    Write your own code capture EXCEPTION//$this->pdo->setattribute (Pdo::attr_errmode, pdo::errmode_exception); $this->pdo->setattribute (Pdo::attr_default_fetch_mode, PDO::FETCH_ASSOC);//Property Name property value array returned with associative array}/* Database off */public  function Close () {$this->pdo = null;    }//For operations that have logged results returned, especially the Select Operation Public Function query ($sql, $return =false) {$res = $this->pdo->query ($sql);    if ($res) {$this->res = $res;//return returned $this->res;    } if ($return) {return $res; }}//is primarily for operations returned without a result set, such as INSERT, UPDATE, delete, and so on public function exec ($sql, $return =false) {$res = $this->pdo->    EXEC ($sql);    if ($res) {$this->res = $res;    if ($return) {//return operation successfully returned 1 failed 0 return $res;  }}//will return $this->res as an array (return all) public Function Fetchall () {return $this->res->fetchall ();  }//returns $this->res as an array (a record) public function fetch () {return $this->res->fetch ();  }//Returns all fields Public function Fetchcolumn () {return $this->res->fetchcolumn (); }//Returns the last inserted ID public function Lastinsertid () {return $this->res->lastinsertid (); }//Returns the last inserted ID public function lastInsertId2 () {return $this->pdo->lastinsertid (); }/** * Parameter description * String/array $table database table, two modes of transmission * Normal Mode: * ' Tb_member, tb_money ' * Array mode: * Array (' tb_member ', ' Tb_mon  EY ') * string/array $fields the database fields that need to be queried, allow nulls, default to find all, two-pass mode * Normal mode: * ' username, password ' * array mode: * Arrays (' username ', ' Password ') * string/array $sqlwhere query condition, allow NULL, two modes of transmission * Normal mode (must be added with and, $sqlwhere is empty 1=1 normal query): * ' and type = 1 and Userna Me like "%os%" ' array pattern: * Arrays (' type = 1 ', ' username like '%os% ') * string $orderby Sort, default to reverse ID *int $debug whether to turn on debug, open The output SQL statement * 0 does not turn on * 1 on * 2 Open and terminate the program * int $mode return type * 0 returns multiple records * 1 returns a single record * 2 returns the number of rows */Public Function Select ($ta ble, $fields = "*", $sqlwhere = "", $orderby = "", $debug =0, $mode =0) {//Parameter processing if (Is_array ($table)) {$table = implode (    ', ', $table);      } if (Is_array ($fields)) {$fields = implode (', ', $fields); /* IF ($this->config[' type ']== ' Oracle ') {//$fields = Implode (', ', $fields);//customer_id,first_name,last_name,email//$fields = Implode (", ' UTF8 ', ' ZHS16GBK '), convert (", $fields);      $fields = "Convert (". $fields. ", ' UTF8 ', ' ZHS16GBK ')";      }else{$fields = Implode (', ', $fields);    } */} if (Is_array ($sqlwhere)) {$sqlwhere = ' and '. Implode (' and ', $sqlwhere);  }//Database operation if ($debug = = = 0) {if ($mode = = = 2) {//Statistics $this->query ("SELECT count (*) from $table where 1=1        $sqlwhere ");      $return = $this->fetchcolumn ();        }else if ($mode = = = 1) {//Returns a $this->query ("Select $fields from $table where 1=1 $sqlwhere $orderby");      $return = $this->fetch ();        }else{$this->query ("Select $fields from $table where 1=1 $sqlwhere $orderby");       $return = $this->fetchall ()//If $this->res is empty, the SQL statement error will prompt the call to a member function Fetchall () on a non-object}    return $return; }else{if ($mode = = = 2) {echo "SELECT COUNT (*) from $table where 1=1 $sqlwheRe ";        }else if ($mode = = = 1) {echo "select $fields from $table where 1=1 $sqlwhere $orderby";        }else{echo "Select $fields from $table where 1=1 $sqlwhere $orderby";        } if ($debug = = = 2) {exit;  }}}/** * parameter description * String/array $table database table, two pass-through modes * Normal mode: * ' Tb_member, tb_money ' * Array mode: * Array (' Tb_member ', ' Tb_money ') * String/array $set The fields and contents that need to be inserted, two modes of transmission * Normal mode: * ' username = ' Test ', type = 1, dt = Now () ' * Array mode: * ARRA Y (' username = "Test" ', ' type = 1 ', ' dt = Now () ') * int $debug If debug is turned on, output SQL statement * 0 does not turn on * 1 on * 2 Open and Terminate program * int $mod e return type * 0 no return information * 1 Returns the number of execution entries * 2 returns the ID of the last inserted record */Public function Oic_insert ($table, $set, $debug =0, $mode =0) {//    Number processing if (Is_array ($table)) {$table = implode (', ', $table);      } if (Is_array ($set)) {$s = '; $i = 0;        foreach ($set as $k = + $v) {$i + +;      $s [$i]= $k;//, Connection $val [$i]= $v; } $sarr =implode (",", $s);//Remove the last one,//array_pop ($sarr);    $set =implode ("', '", $val),////15221579236 ', ' Zhang San ', ', ' 2001 ', ' 8 ', ' 4 ', ' female ', ' is//$set = Implode (', ', $set); }//Database operation if ($debug = = = 0) {if ($mode = = = 2) {$this->query ("INSERT into $table ($sarr) VALUES ('". $set.        "')");      $return = $this->lastinsertid ();        }else if ($mode = = = 1) {$this->exec ("INSERT into $table ($sarr) VALUES ('". $set. "')");      $return = $this->res;        }else{$this->query ("INSERT into $table ($sarr) VALUES ('". $set. "')");      $return = NULL;    } return $return;      }else{echo "INSERT into $table ($sarr) VALUES ('". $set. "')";      if ($debug = = = 2) {exit; }}} Public function Insert ($table, $set, $debug =0, $mode =0) {//Parameter processing if (Is_array ($table)) {$table = Implo    De (', ', $table);      } if (Is_array ($set)) {$s = '; foreach ($set as $k = = $v) {$s. = $k. "      = ' ". $v." ', ";//, connection} $sarr =explode (', ', $s);//Remove the last one, Array_pop ($sarr); $set =imPlode (', ', $sarr);    $set = Implode (', ', $set);        }//Database operation if ($debug = = = 0) {if ($mode = = = 2) {$this->query ("INSERT into $table set $set");      $return = $this->pdo->lastinsertid ();        }else if ($mode = = = 1) {$this->exec ("INSERT into $table set $set");      $return = $this->res;        }else{$this->query ("INSERT into $table set $set");      $return = NULL;    } return $return;      }else{echo "INSERT into $table set $set";      if ($debug = = = 2) {exit; }}}/** * Parameter description * String $table database table, two pass mode * Normal mode: * ' Tb_member, tb_money ' * array pattern: * Arrays (' tb_member ', ' tb_m Oney ') * String/array $set need to update the fields and content, two modes of transmission * Normal mode: * ' username = ' Test ', type = 1, dt = Now () ' * Array mode: * Arrays (' use Rname = "Test" ', ' type = 1 ', ' dt = Now () ') * String/array $sqlwhere Modify condition, allow null, two modes of transmission * Normal Mode: * ' and type = 1 and Userna Me like "%os%" ' * Array pattern: * Arrays (' type = 1 ', ' username like '%os% ') * int $debug whether to turn on debug, openStart output SQL statement * 0 do not turn on * 1 on * 2 Open and Terminate program * int $mode return type * 0 no return information * 1 Returns the number of execution entries */Public Function update ($table, $set    , $sqlwhere = "", $debug =0, $mode =0) {//Parameter processing if (Is_array ($table)) {$table = implode (', ', $table);      } if (Is_array ($set)) {$s = '; foreach ($set as $k = = $v) {$s. = $k. "      = ' ". $v." ', ";      } $sarr =explode (', ', $s);//Remove the last one, Array_pop ($sarr);      $set =implode (', ', $sarr);    $set = Implode (', ', $set);    } if (Is_array ($sqlwhere)) {$sqlwhere = ' and '. Implode (' and ', $sqlwhere); }//Database operation if ($debug = = = 0) {if ($mode = = = 1) {$this->exec ("Update $table set $set where 1=1 $sqlwhere"        );      $return = $this->res;        }else{$this->query ("Update $table set $set where 1=1 $sqlwhere");      $return = true;    } return $return;      }else{echo "Update $table set $set where 1=1 $sqlwhere";      if ($debug = = = 2) {exit; }}}/** * Parameter description * String $table database table * StRing/array $sqlwhere Delete condition, allow null, two modes of transmission * Normal Mode: * ' and type = 1 and username like "%os%" ' * Array pattern: * Arrays (' type = 1 ', ' u Sername like "%os%" ') * int $debug whether to turn on debug, turn on output SQL statement * 0 do not turn on * 1 on * 2 Open and Terminate program * int $mode return type * 0 no return information * 1 return execution Number of entries */Public Function Delete ($table, $sqlwhere = "", $debug =0, $mode =0) {//Parameter processing if (Is_array ($sqlwhere)) {$SQLW here = ' and '. Implode (' and ', $sqlwhere); Is the string to be added by itself and}//Database operation if ($debug = = = 0) {if ($mode = = = 1) {$this->exec ("Delete from $table where        1=1 $sqlwhere ");      $return = $this->res;        }else{$this->query ("Delete from $table where 1=1 $sqlwhere");      $return = NULL;    } return $return;      }else{echo "Delete from $table where 1=1 $sqlwhere";      if ($debug = = = 2) {exit; }}}}/*sqlserver configure Extension=php_pdo_mssql.dll and Extension=php_pdo_sqlsrv.dll to install the corresponding ntwdblib.dllhttp:// msdn.microsoft.com/en-us/library/cc296170.aspx download PHP version corresponding to sqlsrv extension SQL Server configuration ODBC Connection need to open extension=php_pdo_odbc.dll*/$mssql 2008_config=array (' dsn ' = ' Odbc:driver={sql Server}; Server=192.168.1.60;database=his ',//Database server address ' username ' = ' sa ', ' password ' = ' xxxxx ', '; $mssql =new Pdodb ($ Mssql2008_config) $sql = "SELECT * FROM (select Row_number () over (order by Tempcolumn) temprownumber,* from (select Top tempcolumn=0,a.* from Da_gr_hbfs a where 1=1) t) ttwhere temprownumber>0 "; $mssql->query ($sql); w Hile ($res = $mssql->fetch ()) {$data []= $res;} Print_r ($data); Exit;//mysql Operation $msyql_config=array (' dsn ' = ' mysql:host=localhost;dbname=talk ', ' username ' and ' = ') Root ', ' password ' = ' 123456 '); $mysql =new pdo_db ($msyql _config); $sql = ' SELECT user_id, user_name, nickname from et_ Users '; $mysql->query ($sql), $data = $mysql->fetchall ();p rint_r ($data); Exit;//oracle Operation $oci_config=array (' DSN ' = ' oci:dbname=orcl ', ' username ' = ' baocrm ', ' password ' = ' Baocrm '); $oracle =new pdo_db ($oci _config);// Print_r ($oracle); exit;//pdo_db Object ([PDO:p rotected] = PDO Object () [res:protected] = [config:protected] = = [Config] + = Array ([DSN] = OCI:DBN AME=ORCL [Name] = PWACRM [Password] = PWACRM)) $sql = "SELECT * from Customer_level t"; $oracle->query ($sql); $dat      A= $oracle->fetchall ();p rint_r ($data) Exit;/*array ([0] = = Array ([level_id] = 1 [0] = 1       [Level_name] + General member [1] + General member [Level_detail] + General member [2] + General member [Sort_number] + 15      [3] = [Create_time] = December-July -12 [4] = December-July -12 [create_by] = 1 [5] = 1       [Update_time] = December-July -12 [6] = December-July -12 [update_by] = 1 [7] = 1 [state] = = Normal [8] = normal)) */?>