PHP Classes

File: class.pdo_mysqli.php

Recommend this page to a friend!
  Classes of MarPlo   PDO_MySQLi class   class.pdo_mysqli.php   Download  
File: class.pdo_mysqli.php
Role: Class source
Content type: text/plain
Description: PDO-MySQLi class code
Class: PDO_MySQLi class
Access MySQL using PDO or MySQLi extensions
Author: By
Last change:
Date: 10 years ago
Size: 7,916 bytes
 

Contents

Class file image Download
<?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>';
  }
}