One-off PHP/mySql Query

oneoff_example.php

<?php
  require 'OneOffQuery.php';

  // ( $hst, $usr, $psswrd, $db_url, $qry_strng, $prmtrs )
$dbObj = new OneOffQuery("localhost", "root", "", "information_schema", 
  "SELECT table_name, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_name like ? and table_type like ?", 
  array( "table_name" => "table_%", "table_type" => "SYSTEM %") 
);
print "\nQuery: ".$dbObj->getQueryString()."\n";
print "\nParams: [".join(",",$dbObj->getQueryParams())."]";
print "\nParam Types: \"".$dbObj->getQueryParameterTypes()."\"";
if ($dbObj->isPrepared()) {
  if ( $dbObj->execute() ) {
    print "\nRows: \n";
    $rows = $dbObj->getRows();
    foreach ($rows as $row => $fields) {   // Hashs are generally slower to access but much faster to code with.
      echo "\n".$row . ": ".join("|",$fields)."\n";
    }
    print "\nMax Rows Exceeded? ".($dbObj->exceededRows()?"true":"false")."\n";
  } else {
    print "Execute failed\n";
    print join( ",\n", $dbObj->error_messages)."\n";
  }
} else {
  print "Not prepared\n";
  print join( ",\n", $dbObj->error_messages)."\n";
}

?>

OneOffQuery.php

<?php

/** 
 * @package One-off mySQL Query in PHP via mysqli
 * @description Express a query as an object in PHP
 * @version 1.0
 * @copyright Copyright 2010 by Mitchell Amiano All Rights Reserved.
 * @license http://www.gnu.org/licenses/gpl-2.0.html GNU/GPL
 * */

// Namespace Mylibname;

class OneOffQuery {

  const    max_param_length = 100;
  const    max_result_size = 100;

  public    $error_messages=array();

  protected $db_connection, $query_string, $statement;
  protected $parameters=array();  // provide default overridable parameters here; as associative paramname/paramvalue array
  protected $parameter_types=array();  // provide default overridable parameter types here; as list
  protected $rows = array(); // stuff the data here 

  private   $host, $user, $password, $db_url, $valid=false, $fetched=false, $prepared=false, $exceeded_rows = false;

  function __construct( $hst, $usr, $psswrd, $db_url, $qry_strng, $prmtrs) {
    $this->host = $hst;
    $this->user = $usr;
    $this->password = $psswrd;
    $this->db_url = $db_url;
    $this->query_string = $qry_strng;

    $this->db_connection = new mysqli( $hst, $usr, $psswrd, $db_url) or $this->invalidate( "Cannot connect: no database access." );

    $this->parameters = $this->sanitize( array_merge($this->parameters, $prmtrs) );  // override and extend default parameters

    if (mysqli_connect_errno()) {
      $this->invalidate( "Connect failed: ". mysqli_connect_error() );
    } else {

      $this->statement = $this->db_connection->prepare($this->query_string);
      if (! $this->statement) {
        $this->invalidate( mysqli_error( $this->db_connection ) );
      } else {
        $this->prepared = true;
      }
    }
  }
  function __destruct() {
    if ($this->db_connection) 
      $this->db_connection->close();
  }

  public function isValid() { return $this->valid; }
  public function isPrepared() { return $this->prepared; }
  public function isFetched() { return $this->fetched; }
  public function exceededRows() { return $this->exceeded_rows; }

  public function getQueryString() { return $this->query_string; }
  public function getQueryParams() { return $this->parameters; }

  public function getRows() { 
    if ( (!$this->isFetched()) && $this->isPrepared()) $this->execute();
    if (!$this->isFetched()) return array();
    return $this->rows; 
  }

  public function getQueryParameterTypes() {
    $picture = array();
    foreach ($this->parameters as $k => $v) {
      $picture[] = array_key_exists( $k, $this->parameter_types ) ? $this->parameter_types[ $k ] : "s";
    }
    return join($picture);
  }

  public function execute() {
    if (!$this->isPrepared()) return false;  // failed to prepare, bail out
    if ($this->isFetched()) return true;  // just return previous result
    if (!$this->ok_params()) return false;  // bad args, bail out

    // $this->statement->bind_param($picture, $p1, $p2, $p3, $p4 ...);
    $parms = $this->parameters;
    array_unshift( $parms, $this->getQueryParameterTypes() );

    call_user_func_array(array($this->statement, 'bind_param'), $this->refValues($parms) );

    if (! $this->statement->execute() ) $this->invalidate( mysqli_stmt_error($this->statement) );

    if (! strlen($this->db_connection->error)==0 ) return $this->invalidate("The query failed to execute.");

    $result = $this->statement->get_result();
    
    if(! $result) return $this->invalidate( "bind_result() failed: " . $this->db_connection->error );

    $result_count=0;
    while ($row = $result->fetch_assoc()) {
      $this->rows[] = $row;
      if (++$result_count > self::max_result_size) {
        $this->exceeded_rows = true;
        break;
      }
    }
    $result->free();
    $this->statement->free_result();
    $this->statement->close();

    $this->fetched=true;
    return $this->validate();
  }

  protected function validate() {
    return ($this->valid = true);
  }

  protected function invalidate( $mssge ) {
    $this->valid = false;
    $this->error_messages[] = $mssge;
    return false;
  }

  protected function sanitize( $params ) {
    // var $db = $this->db_connection;
    $sanitized_params = array();
    foreach ($params as $k => $v) {
      $sanitized_params[$k] = $this->db_connection->real_escape_string($v);
    }
    return $sanitized_params;
  }
  protected function ok_params() {
    $parms = $this->parameters;
    $lengths=array_map( function($arg) { return strlen($arg); }, $parms );
    $maxparmlen = self::max_param_length;

    if ( count($lengths) != count(
      array_filter($lengths, function ($element) use ($maxparmlen) { return ($element > 0 && $element < $maxparmlen); } )) ) {
      $this->invalidate("Improperly long parameter values");
      return false;
    }
    return true;
  }

  private function refValues($arr) {   // map parameters to references, but only for newer PHP's
    if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+ 
    { 
      $refs = array(); 
      foreach($arr as $key => $value) 
        $refs[$key] = &$arr[$key]; 
      return $refs; 
    } 
    return $arr; 
  } 
}

?>