<?php
define('USEMOD', 'pdo'); // sets default connection method: 'pdo', or 'mysqli'
// class to connect to MySQL and perform SQL queries
// From - http://coursesweb.net/php-mysql/
class PDO_MySQLi {
protected $usemod = ''; // 'pdo', or 'mysqli'
static protected $conn = false; // stores the connection to mysql
protected $conn_data = array(); // to store data for connecting to database
public $affected_rows = 0; // number of affected rows for Insert, Update, Delete
public $num_rows = 0; // number of rows from Select /Show results
public $num_cols = 0; // number of columns from Select /Show results
public $last_insertid; // stores the last ID in an AUTO_INCREMENT column, after Insert query
public $error = false; // to store and check for errors
function __construct($conn_data) {
$this->conn_data = $conn_data; // stores connection data to MySQL database
}
// to set the connection to mysql, with PDO, or MySQLi
protected function setConn($conn_data) {
// sets the connection method, check if can use pdo or mysqli
if(USEMOD == 'pdo') {
if(extension_loaded('PDO') === true) $this->usemod = 'pdo';
else if(extension_loaded('mysqli') === true) $this->usemod = 'mysqli';
}
else if(USEMOD == 'mysqli') {
if(extension_loaded('mysqli') === true) $this->usemod = 'mysqli';
else if(extension_loaded('PDO') === true) $this->usemod = 'pdo';
}
if($this->usemod == 'pdo') $this->connPDO($conn_data);
else if($this->usemod == 'mysqli') $this->connMySQLi($conn_data);
else $this->setSqlError('Unable to use PDO or MySQLi');
}
// for connecting to mysql with PDO
protected function connPDO($conn_data) {
try {
// Connect and create the PDO object
self::$conn = new PDO("mysql:host=".$conn_data['host']."; dbname=".$conn_data['bdname'], $conn_data['user'], $conn_data['pass']);
// Sets to handle the errors in the ERRMODE_EXCEPTION mode
self::$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Sets transfer with encoding UTF-8
self::$conn->exec('SET character_set_client="utf8",character_set_connection="utf8",character_set_results="utf8";');
}
catch(PDOException $e) {
$this->setSqlError($e->getMessage());
}
}
// method that create the connection to mysql with MySQLi
protected function connMySQLi($conn_data) {
// if the connection is successfully established
if(self::$conn = new mysqli($conn_data['host'], $conn_data['user'], $conn_data['pass'], $conn_data['bdname'])) {
self::$conn->query('SET character_set_client="utf8",character_set_connection="utf8",character_set_results="utf8";');
}
else if (mysqli_connect_errno()) $this->setSqlError('MySQL connection failed: '. mysqli_connect_error());
}
// Performs SQL queries
// $sql - SQL query with prepared statement
// $param - array of values for SQL query
public function sqlExecute($sql, $param=array()) {
if(self::$conn === false || self::$conn === NULL) $this->setConn($this->conn_data); // sets the connection to mysql
$re = true; // the value to be returned
// if there is a connection set ($conn property not false)
if(self::$conn !== false) {
// gets the first word in $sql, to determine whenb SELECT query
$ar_mode = explode(' ', trim($sql), 2);
$mode = strtolower($ar_mode[0]);
// replace ":for_value" with "?" (for MySQLi)
if($this->usemod == 'mysqli') $sql = preg_replace('/:[^,|"|\'|;|\)\} ]*/i','?', $sql);
$sqlre = self::$conn->prepare($sql); // prepares statement
// if successfully prepared
if(is_object($sqlre)) {
// execute query
if($this->usemod == 'pdo') {
try { $sqlre->execute($param); }
catch(PDOException $e) { $this->setSqlError($e->getMessage()); }
}
else if($this->usemod == 'mysqli') {
// if values in $param, sets to use "bind_param" before execute()
if(count($param) > 0) {
// stores in $args[0] the type of the value of each value in $param, the rest of items in $args are the values
$args = array('');
foreach($param AS $k=>$v) {
if(is_int($v)) $args[0] .= 'i';
else if(is_double($v)) $args[0] .= 'd';
else $args[0] .= 's';
$args[] = &$param[$k];
}
// binds the values with their types in prepared statement
call_user_func_array(array($sqlre,'bind_param'), $args);
}
if(!$sqlre->execute()) {
if(isset(self::$conn->error_list[0]['error'])) $this->setSqlError(self::$conn->error_list[0]['error']);
else $this->setSqlError('Unable to execute the SQL query, check if values are passed to sqlExecute()');
}
}
}
else {
if(isset(self::$conn->error_list[0]['error'])) $this->setSqlError(self::$conn->error_list[0]['error']);
else $this->setSqlError('Unable to prepare the SQL query, check if SQL query data are correctly');
}
// if no error
if($this->error === false) {
// if $mode is 'select' or 'show', gets the result_set to return
if($mode == 'select' || $mode == 'show') {
$re = ($this->usemod == 'pdo') ? $this->getSelectPDO($sqlre) : $this->getSelectMySQLi($sqlre); // gets select results
$this->num_rows = count($re); // number of returned rows
if(isset($re[0])) $this->num_cols = count($re[0]) / 2; // number of returned columns
}
else $this->affected_rows = ($this->usemod == 'pdo') ? $sqlre->rowCount() : $sqlre->affected_rows; // affected rows for Insert, Update, Delete
// if Insert query, stores the last insert ID
if($mode == 'insert') $this->last_insertid = ($this->usemod == 'pdo') ? self::$conn->lastInsertId() : self::$conn->insert_id;
}
}
// sets to return false in case of error
if($this->error !== false) $re = false;
return $re;
}
// gets and returns Select results performed with PDO
// receives the object created with prepare() statement
protected function getSelectPDO($sqlre) {
$re = array();
// if fetch() returns at least one row (not false), adds the rows in $re for return (numerical, and associative)
if($row = $sqlre->fetch()){
do {
// check each column if it has numeric value, to convert it from "string"
foreach($row AS $k=>$v) {
if(is_numeric($v)) $row[$k] = $v + 0;
}
$re[] = $row;
}
while($row = $sqlre->fetch());
}
return $re;
}
// gets and returns Select results performed with MySQLi
// receives the object created with prepare() statement
protected function getSelectMySQLi($sqlre) {
$meta = $sqlre->result_metadata();
$re = array(); $parameters = array();
// gets column names, to be passed as parameters to bind_result()
while ($field = $meta->fetch_field()) {
$parameters[] = &$row[$field->name];
}
// accesses $sqlre->bind_result with arguments stored in $parameters
call_user_func_array(array($sqlre, 'bind_result'), $parameters);
// gets array with results (numerical ($x1), and associative ($x2))
while($sqlre->fetch()) {
$i=0;
foreach($row as $k => $v) {
$x1[$i] = $v;
$x2[$k] = $v;
$i++;
}
$re[] = array_merge($x1, $x2);
}
return $re;
}
// set sql error in $error
protected function setSqlError($err) {
$this->error = '<h4>Error: '. $err .'</h4>';
}
}
|
Stumble It!
Bookmark in del.icio.us
Download
