Location: PHPKode > scripts > PDO wrapper > pdo_wrapper_class.php
<?php
#################################################
/*
 * class pdo_wrapper_class.php - wrapper for the PHP PDO functions
 *
 * methods:
 * pdoConnect      - connects to database
 * pdoExecute      - executes an SQL query, can have SQL parameters
 * pdoRowCount     - returns number of rows affected
 * pdoGetAll       - returns all rows in a 2-dimensional hash array
 * pdoGetAssoc     - returns all rows in a 2-dimensional hash array, 1st retrieved column is index of array
 * pdoGetRow       - returns the first row of results in a 1-dimensional array
 * pdoInsUpd       - creates an insert or update statement from tablename and array with data
 * pdoLastInsertId - returns last created ID from an autoincrement column
 * pdoException    - displays error message after exception
 * 
 * Licensed under the terms of the BSD License:
 *      http://www.opensource.org/licenses/bsd-license.php
 *
 * ****************************************************
 * You must copy this copyright notice into any modification of this class
 * ****************************************************
 * Copyright by:
 * Author    Emile Schenk
 *           http://www.donauweb.at
 *           https://www.xing.com/profile/Emile_Schenk
 * License   BSD
 * ****************************************************
 * End copyright notice 
 * ****************************************************
 * Created   February 2010
 * Revision  1.1
 * 
 * Changes 
 *     Revision 1.0
 *         - Initial release
 *         - well tested, is in use in commercial projects
 *     Revision 1.1
 *         - More comments added
 *     Revision 1.2
 *         - pdoInsUpd: Older PHP version need separate variables when the same value is used several times in one SQL statement
 */ 

##################################################################################################
##################################################################################################
class PdoWrapper {
	private $dbh;
#################################################
# When useTablecols==1 a file must be included which defines all fields of tables. 
# Only fields that are defined in this file will be INSERTED/UPDATED in the table.
# Example:
# global $tablecols;
# $tablecols['customers'] = array('id','firstname','lastname','address','postcode','city'); 
#################################################
	public $useTablecols = 0;
#################################################
# use nicely formatted output of datadumper: https://sourceforge.net/projects/datadumper
# for output of Exceptions
#################################################
	public $useDatadumper = 0;

#################################################
function pdoConnect($server, $username, $password, $database) {
	try {
		$this->dbh = new PDO("mysql:dbname=$database;host=$server", $username, $password);
	} 
	catch(Exception $e) {
		$this->pdoException( $e->getMessage() );
	  exit;
	}
	$this->dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
  return true;
}


#################################################
function pdoExecute($query, $array=array()) {
	$this->sql = array('query'=>$query, input=>$array);
	try{
		$stmt = $this->dbh->prepare($query);
		$ret = $stmt->execute($array);
		$stmt->setFetchMode(PDO::FETCH_ASSOC);
		return $stmt;
	}
	catch (Exception $e) {
		$this->pdoException( $e->getMessage() );
	  exit;
	}
}

#################################################
function pdoRowCount($stmt) {
	try{
		$count = $stmt->rowCount();
		return $count;
	}
	catch (Exception $e) {
		$this->pdoException( $e->getMessage() );
	  exit;
	}
}

#################################################
function pdoGetAll($query, $array=array()) {
	$stmt = $this->pdoExecute($query, $array);
	$rows = $stmt->fetchAll();
	return $rows;
}

#################################################
function pdoGetAssoc($query, $array=array()) {
	$rows = $this->pdoGetAll($query, $array);
	foreach($rows as $row) {
		$idx = array_shift($row);
		$rows_assoc[$idx] = $row;
	}
	return $rows_assoc;
}

#################################################
function pdoGetRow($query, $array=array()) {
	$stmt = $this->pdoExecute($query, $array);
	$row = $stmt->fetch();
	return $row;
}

#################################################
/* function pdoInsUpd
 * Parameters:
 * $table: name of table 
 * $data: hash-array with values of data for INSERT/UPDATE
 * $action: 'insert' | 'insert duplicate' | 'insert delayed' | 'update'
 *          duplicate adds to SQL: ON DUPLICATE KEY UPDATE
 *          delayed adds to SQL: DELAYED
 * $parameters: WHERE parameters for UPDATE
 *              hash-array
 *              index can be: fieldname | 'fieldname <operator>' where operator can be '=', '<', '>', '<>', '>=', '<=', 'like'
 *              when no operator is mentioned '=' is taken as operator
 *              e.g. array('myfield', 'myvalue') results in "WHERE myfield='myvalue'"
 *              e.g. array('myfield >', 'myvalue') results in "WHERE myfield>'myvalue'"
 *              e.g. array('myfield like', 'myvalue') results in "WHERE myfield like '%myvalue%'"
 *              
 * When $this->useTablecols==1 a file must be included which defines all fields of the used table. 
 * Only fields that are defined in this file will be INSERTED/UPDATED in the table.
 * Example of file:
 * global $tablecols;
 * $tablecols['customers'] = array('id','firstname','lastname','address','postcode','city'); 
*/
#################################################
function pdoInsUpd($table, $data, $action = 'insert', $parameters = '') {
  global $tablecols;
  
  reset($data);
	$this->sql = '';

  foreach($data as $idx=>$d) {
    if($this->useTablecols==1) {
	    if(!is_array($tablecols[$table])) break;
	    if(!in_array($idx, $tablecols[$table])) continue;
	  }

    switch (strtolower((string)$d)) {
      case 'now()':
      case 'null':
		    $insertcolumns .= "$idx, ";
		    $insertvalues .= "$d, ";
		    $updatevalues .= "$idx=$d, ";
      break;
      default:
		    $insertcolumns .= "$idx, ";
		    $insertvalues .= ":$idx, ";
		    $duplicatevalues .= "$idx=:dup_$idx, ";
		    $updatevalues .= "$idx=:$idx, ";
		    if(is_null($d)) $d = '';
		    $insertarray[$idx] = $d;
		    $duplicatearray['dup_'.$idx] = $d;
      break;
    }
  }
	if(strlen($insertcolumns) <3) {
    $txt = '';
    if($this->useTablecols==1) {
    	$txt = "table $table not found in tablecols.php or ";
    }
		$this->pdoException($txt . "no columns to insert");
	  exit;
	}
	$insertcolumns    = rtrim($insertcolumns, ', ');
	$insertvalues     = rtrim($insertvalues, ', ');
	$duplicatevalues  = rtrim($duplicatevalues, ', ');
	$updatevalues     = rtrim($updatevalues, ', ');

  $action = strtolower($action);
 	$delayed = '';
  if(substr_count($action, 'delayed') >0) {
  	$delayed = 'DELAYED';
  }
  if(substr_count($action, 'insert') >0) {
    $query = "INSERT $delayed INTO $table ($insertcolumns) VALUES ($insertvalues)";
    if(substr_count($action, 'duplicate') >0) {
    	$query .= " ON DUPLICATE KEY UPDATE $duplicatevalues";
    	$insertarray = array_merge($insertarray, $duplicatearray);
    }
  } 
  elseif(substr_count($action, 'update') >0) {
    $operators = array('=', '<', '>', '<>', '>=', '<=', 'like');
    foreach($parameters as $idx=>$parameter) {
    	list($where_var, $where_operator) = explode(' ', $idx);
    	if($where_operator == '') {
    		$where_operator = '=';
    	}
    	if(!in_array($where_operator, $operators)) {
    		continue;
    	}
    	if($where_operator == 'like') {
    		$parameter = "%$parameter%";
    	}
    	$where_array[] = "$where_var $where_operator :where_$idx";
    	$insertarray['where_'.$idx] = $parameter;
    }
    $where = implode(' AND ', $where_array);
    $query = "UPDATE $table SET $updatevalues WHERE $where";
  }
  return $this->pdoExecute($query, $insertarray);
}

#################################################
function pdoLastInsertId() {
	return $this->dbh->lastInsertId();
}

#################################################
function pdoException($message) {
	$pdo_error[error] = 'PDO-SQL-ERROR';
	$pdo_error[PDO_error] = $message;
	$pdo_error[SQL] = $this->sql;
	$pdo_error[debug_backtrace]= debug_backtrace();
	if($this->useDatadumper) {
		de($pdo_error);
	}
	else {
		var_dump($pdo_error);
		exit;
	}
}

#################################################
} # end class






Return current item: PDO wrapper