Smart SQL Queries

<?php
/**
  * getSmartCommand
  *
  * <p>Returns a set of command names by detecting it's driver type</p>
  * @param resource $dbh A sql connection resource
  * @return array A set of command names
  *
  */
function getSmartCommand($driver) {
  // GLOBAL SmartCommandsDriver is used only to detect the connection once every script.
  // can be overidden by setting $GLOBALS['SmartCommandsDriver'] to the driver of your choice.


  // extend for your database here!
  $commands = array(
    'mysql' => array(
      'query' => 'mysql_query',
      'lastid' => 'mysql_insert_id',
      'escape' => 'mysql_real_escape_string',
      'error' => 'mysql_error',
      'affected_rows' => 'mysql_affected_rows',
      'fetchassoc' => 'mysql_fetch_assoc'
    ),
    'pgsql' => array(
      'query' => 'pg_query',
      'lastid' => 'pg_last_oid',
      'escape' => 'pg_escape_string',
      'error' => 'pg_last_error',
      'affected_rows' => 'pg_affected_rows',
      'fetchassoc' => 'pg_fetch_assoc'
    )
  );

  if (!isset($GLOBALS["SmartCommandsDriver"]) || !key_exists($commands[$GLOBALS["SmartCommandsDriver"]])) {
    if (@pg_ping($driver)) {
      $GLOBALS["SmartCommandsDriver"] = "pgsql";
    } elseif (@mysql_ping($driver)) {
      $GLOBALS["SmartCommandsDriver"] = "mysql";
    } else {
      trigger_error("No valid database connection");
    }
  }
  return ($commands[$GLOBALS["SmartCommandsDriver"]]);
};


/**
  * smartQuery
  *
  * <p>Returns results for a specific sql query. Select queries will return  , replaces ? with a quoted/escaped corresponding array value. </p>
  * @param string $query A query like "SELECT id FROM table WHERE field = ? AND nextfield = ?"
  * @param array @params [a,b] Parameters corresponding with the number of questionmarks
  * @return string SELECT id FROM table WHERE field = 'a' AND nextfield = 'b'
  *
  */

function smartQuery() {
  $arguments = func_get_args();
  if (is_resource($arguments[0])) {
    $dbh = array_shift($arguments);
  } elseif (isset($GLOBALS['dbh']) && is_resource($GLOBALS['dbh'])) {
    $dbh = $GLOBALS['dbh'];
  } else {
    trigger_error("No valid database connection, please provide a database resource as a first argument or define the global dbh");
    return false;
  }
  $commands = getSmartCommand($dbh);

  $query = array_shift($arguments);

  // the second argument can either be an array or (multiple) values
  if (isset($arguments) && isset($arguments[0]) && is_array($arguments[0])) {
    $arguments = $arguments[0];
  }
  $query = escapeQuery($dbh, $query, $arguments);

  if (isset($arguments)) {
    $sth = $commands['query']($query);
  } else {
    return false;
  }

  if (!$sth) {
    trigger_error("Error in smartQuery:" . $commands['error']() . "<br /><pre>" . print_r(debug_backtrace(),1) . "</pre>", E_USER_ERROR);
  }
  //remove the first whitespace
  $query = preg_replace('/^(\s+)/', "", $query);
  if (preg_match("/^select/i", $query)) {
    $info = array();
    while ($row = $commands['fetchassoc']($sth)) {
      array_push($info,$row);
    }
    return $info;

  } elseif (preg_match("/^insert/i", $query)) {
     return $commands['lastid']($sth);
  } elseif (preg_match("/^(update|delete)/i", $query)) {
     // aantal updates terug geven
     return $commands['affected_rows']($sth);
  }
}

  /**
  * PDO/DBI-Like query escaping
  *
  * <p>Returns an escaped sql string, replaces ? with a quoted/escaped corresponding array value</p>
  * @param string $query A query like "SELECT id FROM table WHERE field = ? AND nextfield = ?"
  * @param mixed @params e.g. array('a','b') Parameters corresponding with the number of questionmarks
  * @return string e.g. "SELECT id FROM table WHERE field = 'a' AND nextfield = 'b'"
  *
  */
function escapeQuery () {
  $arguments = func_get_args();
  if (is_resource($arguments[0])) {
    $dbh = array_shift($arguments);
  } elseif (isset($GLOBALS['dbh']) && is_resource($GLOBALS['dbh'])) {
    $dbh = $GLOBALS['dbh'];
  } else {
    trigger_error("No valid database connection, please provide a database resource as a first argument or define the global dbh");
    return false;
  }
  $query = array_shift($arguments);
  if (isset($arguments) && isset($arguments[0]) && is_array($arguments[0])) {
      $arguments = $arguments[0];
  }
  foreach ($arguments as &$arg) {
    $query = preg_replace("/\?/", escapeByType($arg), $query, 1);
  }
  return $query;
}

  /**
  * Specific escaping per type
  *
  * <p>Returns a quoted string, dependent on it's type, currently only recognizes integers</p>
  *
  */
function escapeByType ($argument) {
  $commands = getSmartCommand($dbh);
  if (gettype($argument) == "integer" && is_numeric($argument)) {
    // don't quote integers!
    return $commands['escape']($argument);
  } elseif (isnull($argument)) {
    return "NULL";
  } else {
    return "'" . $commands['escape']($argument) . "'";
  }
}
?>