Location: PHPKode > scripts > MpQuery > mpquery/MpQuery.php
<?php
/* 
 * MyQuery Singleton class.
 *
 * Use 1 database connection to run MySQL queries.
 *
 *
 *
 *
 * TODO:
 *      Create example use file with create table statements.
 *      ADD - Exception error handling.
 *      ADD - Debugging assistance.
 *
 */

define('MP_QUERY_REGEXP', '/(%d|%s|%%|%f|%b)/');

define('MYQ_HOST', 'localhost');
define('MYQ_DB', 'your_db');
define('MYQ_USR', 'root');
define('MYQ_PASS', 'password');

class MpQuery {
    // single instance of self shared among all instances
    private static $instance = null;
    
    private $con = null; // database connection
    private $results; // holds all row values from querys. $results['object'] contains array of objects, $results['assoc'] contains array of assoc arrays, $results['num'] contains array of numerical arrays.
    private $result = 1;
    private $last_result = 1;

    private function __construct(){
        $this->con = mysql_connect(MYQ_HOST, MYQ_USR, MYQ_PASS)
                or die ("Could not connect to db: " . mysql_error());
        mysql_select_db(MYQ_DB, $this->con)
                or die ("Could not select db: " . mysql_error());
    }

    public static function getInstance() {
      if (!self::$instance instanceof self) {
        self::$instance = new self;
      }
      return self::$instance;
    }

    // The clone and wakeup methods prevents external instantiation of copies of the Singleton class,
    // thus eliminating the possibility of duplicate objects.
    public function __clone() {
      trigger_error('Clone is not allowed.', E_USER_ERROR);
    }
    public function __wakeup() {
      trigger_error('Deserializing is not allowed.', E_USER_ERROR);
    }

    /**
     * Runs a basic query in the active database.
     *
     * User-supplied arguments to the query should be passed in as separate
     * parameters so that they can be properly escaped to avoid SQL injection
     * attacks.
     *
     * @param string $query
     *   A string containing an SQL query.
     * @param 
     *   Any number of arguments which are substituted in the query
     *   using printf() syntax. Instead of a variable number of query arguments,
     *   you may also pass a single array containing the query arguments.

     *   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
     *   in '') and %%.
     *
     */
    public function mp_query($query) {
      $this->last_result = $this->result;
      $args = func_get_args();
      array_shift($args);
      if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
        $args = $args[0];
      }
      $this->_mp_query_callback($args, TRUE);
      $query = preg_replace_callback(MP_QUERY_REGEXP, array(get_class($this), '_mp_query_callback'), $query);
      $this->result = mysql_query($query)
        or die('db_query ERROR: ' . mysql_error());
    }

    /**
     * SQL injection protection for mp_query().
     *
     */
    private function _mp_query_callback($match, $init = FALSE) {
      static $args = NULL;
      if ($init) {
        $args = $match;
        return;
      }

      switch ($match[1]) {
        case '%d': 
          return (int) array_shift($args); 
        case '%s':
          return $this->mp_escape_string(array_shift($args));
        case '%%':
          return '%';
        case '%f':
          return (float) array_shift($args);
        case '%b': // binary data
          return $this->mp_encode_blob(array_shift($args));
      }
    }
    /**
     * Prepare user input for use in a database query, preventing SQL injection attacks.
     * @param string $text
     *   String to be escaped.
     * @return string
     *  A database safe string.
     */
    private function mp_escape_string($text) {
      return mysql_real_escape_string($text, $this->con);
    }
    /**
     * Returns a properly formatted Binary Large OBject value.
     * @param string $data
     *   Data to encode.
     * @return string
     *  Encoded data.
     */
    private function mp_encode_blob($data) {
      return "'" . mysql_real_escape_string($data, $this->con) . "'";
    }

    /**
     *
     * @return
     *   The number of rows in the current query.
     */
    public function mp_num_rows() {
        if ($this->result != 1) { // check to make sure there is a valid result resource.
            $number_rows = mysql_num_rows($this->result);
            return $number_rows;
        }
        return '<p><strong>No query call found.</strong></p>';
    }

    /**
     *
     * @param string $return_type
     *  values - OBJECT, ASSOC, NUM or BOTH.
     *  OBJECT returns each row of a query as an object stored in a number
     *  array.
     *  ASSOC, NUM & BOTH returns each row of a query as an array type,
     *  MYSQL_ASSOC, MYSQL_NUM & MYSQL_BOTH respectively. Also stored in a
     *  numbered array.
     *
     * @return
     *  Query result rows in a numbered array.
     *  FALSE if the query returns no results.
     *  And a 'no query called' string if the result resource is invalid.
     */
    public function mp_usable_results($return_type = NULL){
        if ($return_type == 'OBJECT'){
            $fetch_func = 'object';
        } else {
            $fetch_func = 'array';
        }
        $fetch_var = strtolower($return_type);

        if ($this->result != 1 && $this->mp_num_rows() > 0) { // check to make sure there is a valid result resource.
            $i = 0;
                while($row = call_user_func(array(get_class($this),'mp_' . $fetch_func), $return_type)){
                    $this->results[$fetch_var][$i] = $row;
                $i++;
                }

            mysql_data_seek($this->result, 0);// reset result resource.
            return $this->results[$fetch_var];
        }
        if ($this->result == 1) {
            return '<p><strong>No query call found.</strong></p>';
        }
        return FALSE;
    }

    /**
     *
     * @param string $return_type
     *  Only included so that if return type is an array it can specify the array type.
     * @return
     *  An object to mp_usable_results.
     */
    private function mp_object($return_type){
        return mysql_fetch_object($this->result);
    }
    /**
     *
     * @param string $return_type
     *  Values - ASSOC, NUM or BOTH to specify what type of array to return.
     * @return
     *  An array to db_usable_results.
     */
    private function mp_array($return_type){
        switch ($return_type){
            case 'NUM':
                return mysql_fetch_array($this->result, MYSQL_NUM);
            case 'ASSOC':
                return mysql_fetch_array($this->result, MYSQL_ASSOC);
            default:
                return mysql_fetch_array($this->result, MYSQL_BOTH);
        }
    }

    /**
     *
     * @return
     *  The current result resource.
     */
    public function get_result(){
        return $this->result;
    }

    /**
     *
     * @return
     *  The previous result resource.
     */
    public function get_last_result(){
        return $this->last_result;
    }

}

?>
Return current item: MpQuery