Code Blog

A project of substratum.ca

PHP PDO qrs()

So I started working with Web Development using ASP and still mentally think of SQL calls in my mind as “RecordSets” so when I went to make PDO faster to access data in my code I created a function called qrs() which stands for “Quick Record Set”. This also wraps a “SafePDO” extension to the PDO.

Example presumes use in a namespace but can be changed outside of namespace if required. Lots of OLD code in this as it was one of the first things I did when switching to PHP so forgive that it’s not the neatest or most modern code.

There are also other methods in there such as auto caching results for heavily hit queries on pages which aren’t immediately time sensitive to have showing to users. Likely should move my caching to JSON one of these days..

The joy of this is fast access to data. IE: Get all the “Mark” records.

$q ='SELECT * FROM table WHERE firstName LIKE :fn';
$p[':fn'] = 'Mark';
$allMarks = $Db->qrs($q,$p)->fetchAll(PDO::FETCH_OBJ);
SafePDO.php
<?php namespace NAMESPACE\Db;
use PDO;
Class SafePDO extends PDO 
	{
	public static function exception_handler($exception)
		{
		// Output the exception details
		die('Uncaught exception: '.$exception->getMessage());
		}
	public function __construct($dsn, $username='', $password='', $driver_options=array())
		{
		// Temporarily change the PHP exception handler while we . . .
		set_exception_handler(array(__CLASS__, 'exception_handler'));
		// . . . create a PDO object
		parent::__construct($dsn, $username, $password, $driver_options);
		// Change the exception handler back to whatever it was before
		restore_exception_handler();
		}
	 }
Db.php
<?php namespace NAMESPACE\Db;
use NAMESPACE\Db\SafePDO;
use \PDO;
use NAMESPACE\Exception;
/*
EXAMPLE:
	$q ="SELECT * FROM dbname.table WHERE id = :id";
	$parms = [':id'=>[22,PDO::PARAM_INT]];
	$db = new NAMESPACE\Db\Db('nameOfDb'); // adding ",true" will add logging

	$rs = $db->qrs($q,$parms)->fetchAll(PDO::FETCH_ASSOC);
		or
	$st = db->qrs($q,$parms);
	while($rs=$st->fetch(PDO::FETCH_ASSOC)){ .. }
*/

Class Db 
{
	// List of dbs in array with "referenceName"=>["dbName","dbUser","dbPass","host(optional-Assumed.localhost)")]
	// NOTE: if using cachedb (qrsCached()) then a DB named "cachedb" must be included
	protected $dbs = array(
        "readOnly"	=>array("","acct_readOnly","ajn6nkl5?g$"), // read only access to dbs
        "write"		=>array("","acct_writeAccess","J[]asf]UyM31zs}"), // read only access to dbs
    					);
    // calls to the below will redirect to the specified db
    protected $dbsShortcuts = array(
                    "r"=>"readOnly",
                    "w"=>"write",
                                );
	
	public $conn; // holds the actual connection
	private $dbVars = array(); // to be populated during __construct with [db,username,password,host]
	
    public function __debugInfo() {
		$showThis = array();
		foreach($this as $k => $v) {
			$showThis[$k] = $v;
		}
		$showThis['dbs'] = 'hidden';
		$showThis['dbVars'] = 'hidden';
        return [$showThis];
    }
	
	function __construct($whichDb, $logging=FALSE)	{
		if (!class_exists('NAMESPACE\Db\SafePDO'))	{	throw new Exception("No SafePDO");	}
		$this->logging($logging);
        if (isset($this->dbsShortcuts[$whichDb])) {
            $whichDb = $this->dbsShortcuts[$whichDb];
        }
        $this->whichDb = $whichDb;
		$this->set_dbVars($whichDb);
		$this->connect();
    }
		
	function __destruct() {
		$this->conn = NULL;
		unset($this->conn);
		if ($this->console_log) { echo '<script>console.log("Db_disconnect('.$this->dbVars['db'].')")</script>'; }
	}
	
	// accepts true or false
	public function logging($yn) {	
		if ($yn){ $this->console_log = TRUE; }
		else 	{ $this->console_log = FALSE; }
	}
	
	public function query($q) {
		return $this->conn->query($q);
	}
	
	public function lastInsertId() {
		return $this->conn->lastInsertId();
	}
	
	public function set_dbVars($whichDb) {
		if (!$this->dbs[$whichDb]) {
			throw new Exception("DBVars not found for requested DB[1].");
		}
		$dbVars['db'] = $this->dbs[$whichDb][0];
		$dbVars['username'] = $this->dbs[$whichDb][1];
		$dbVars['password'] = $this->dbs[$whichDb][2];
		if (isset($this->dbs[$whichDb][3])) { $dbVars['host'] = $this->dbs[$whichDb][3]; }
		else { $dbVars['host'] = 'localhost'; }
		if (!$dbVars['db'] && !$dbVars['username'] && !$dbVars['password']) {
			throw new Exception("DBVars not found for requested DB[2].");
		}
		$this->dbVars = $dbVars;
	}

	// quick recordset
	public function qrs($query,$vars=NULL,$altDb=NULL) { 
		if (!isset($this->conn)) { $this->connect(); }
		$qrsDb = $this->conn;
		if ($altDb) {	$qrsDb=$altDb;	}
		$st=$qrsDb->prepare($query);
		if ($vars)
			{ // if vars are passed they should be an array of ":id"=>1 type, if specific param then ":id"=>[1,PDO::PARAM_INT"]
			foreach ($vars as $a=>$b)
				{
				$c = \PDO::PARAM_STR;
				if (is_array($b)) { $c = $b[1]; $b=$b[0]; }
				if (!$c) { $c = \PDO::PARAM_STR; }
				$st->bindValue($a,$b,$c);
				}
			}
		$st->execute();
		return $st;
	}
	
	// quick recordset with caching, $maxAge in minutes
	// NOTE: fetch is by default fetchAll unless specified
	public function qrsCached($query,$vars=NULL,$maxAge=60,$fetchType="all") { 
		$dbVars = $this->dbVars; // capture dbVars to replace it once our cached call is completed.
		$this->set_dbVars('cachedb');
		$cachDbVars = $this->dbVars;
		$cachDb = $this->connect("queryCache",true);
		$this->dbVars = $dbVars; // replace the dbVars
		$query_Table =	'
				CREATE TABLE IF NOT EXISTS `'.$cachDbVars['db'].'`.`cachedQuery` (
				  `cacheId` int(10) unsigned NOT NULL AUTO_INCREMENT,
				  `query` varchar(8000) DEFAULT NULL,
				  `vars` varchar(8000) DEFAULT NULL,
				  `data` text,
				  `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
				  PRIMARY KEY (`cacheId`),
				  UNIQUE KEY `cacheId_UNIQUE` (`cacheId`)
				) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
						';
		$cachDb->query($query_Table); // make sure that the table exists
		$querySM = str_replace('	','',str_replace(' ','',$query));
		$varsSM = null;
		if ($vars)	{	$varsSM = json_encode($vars);	}
		$q ="
			SELECT dts, data FROM `{$cachDbVars['db']}`.`cachedQuery`
			 WHERE query = :query 
			 ";
		if ($vars)
			{
			$q.="
			   AND vars = :vars
				";
			}
		$q.="
			   AND TIMESTAMPDIFF(MINUTE,dts,NOW()) < :maxAge
			";
		$st=$cachDb->prepare($q);
		$st->bindValue(':query',$querySM);
		if ($vars) {
			$st->bindValue(':vars',$varsSM);
		}
		$st->bindValue(':maxAge',$maxAge+1);
		$st->execute();
		$rsCached = $st->fetch(PDO::FETCH_ASSOC);
		if ($rsCached['data']) {
			$GLOBALS['qrsCachedDts'] = $rsCached['dts'];
			return json_decode($rsCached['data'],true);
		} else {
			$st = $this->qrs($query,$vars);
			if ($fetchType=="all") {	$rs = $st->fetchAll(PDO::FETCH_ASSOC);	}
			else {	$rs = $st->fetch(PDO::FETCH_ASSOC);	}
			if ($vars)	{	$varsDel = " vars = :vars ";	}
			else		{	$varsDel = " isnull(vars) ";	}
			$q ="
				DELETE FROM `{$cachDbVars['db']}`.`cachedQuery`
				 WHERE query = :query 
				   AND {$varsDel} ;
				INSERT INTO `{$cachDbVars['db']}`.`cachedQuery`
				 ( query, vars, data )
				VALUES
				 ( :query, :vars, :data ) ;
				";
			$st=$cachDb->prepare($q);
			$st->bindValue(':query',$querySM);
			$st->bindValue(':vars',$varsSM);
			$st->bindValue(':data',json_encode($rs));
			$st->execute();
			$GLOBALS['qrsCachedDts'] = date('Y-M-D h:i:s');
			return $rs;
		}
	}

	public function connect($dbWhich=NULL,$returnConn=FALSE) {
		if ($this->console_log) { echo '<script>console.log("Db_connect('.$dbWhich.')")</script>'; }
        $dbConnfunc = array(
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
            PDO::ATTR_PERSISTENT => true
        );
		if ($returnConn) {
			return new SafePDO(
                'mysql:host='.$this->dbVars['host'].';dbname='.$this->dbVars['db'].';charset=utf8', 
                $this->dbVars['username'], 
                $this->dbVars['password'], 
                $dbConnfunc
            );
        } else {
			$this->conn = new SafePDO(
                'mysql:host='.$this->dbVars['host'].';dbname='.$this->dbVars['db'].';charset=utf8', 
                $this->dbVars['username'], 
                $this->dbVars['password'], 
                $dbConnfunc
            );
        }
	}

	public function getColumns($schema,$table,$selectWhat=null)
	{ // return Columns for requested schema / table
		if (!$selectWhat) {
			$selectWhat = 'ORDINAL_POSITION, COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA, IS_NULLABLE, DATA_TYPE, COLUMN_COMMENT';
		}
		$q='SELECT '.$selectWhat.' FROM INFORMATION_SCHEMA.Columns
 			 WHERE TABLE_SCHEMA = :s 
			   AND TABLE_NAME = :t ';
		$p[':s'] = [$schema,PDO::PARAM_STR];
		$p[':t'] = [$table,PDO::PARAM_STR];
		return $this->qrs($q,$p)->fetchAll(PDO::FETCH_OBJ);
	}
	
	public function areColumnsInTable(array $columnNames,$schema,$table)
	{
		if (count($columnNames)==0){ return true; }
		$Columns = $this->getColumns($schema,$table,'COLUMN_NAME');
		$arr = [];
		foreach ($Columns as $C) {
			$arr[] = $C->COLUMN_NAME;
		}
		foreach ($columnNames as $cn){
			if (!in_array($cn,$arr)) {
				return false;
			}
		}
		return true;
	}
	
}

KevenD

, ,