Location: PHPKode > scripts > My One Line SQL > my-one-line-sql/RunDBQuery.class.php
<?php
/**
 * @package myOneLineSQL
 */
/**
 * Inclusion of interface
 *
 */
include_once("SQLable.class.php");
/**
 * RunDBQuery class
 *
 * Executes SQL queries with DBQuery objects
 * @package myOneLineSQL
 * @author Carlo Tasca
 * @version 1.0
 */
class RunDBQuery implements SQLable
{
   /**
    * To store database connection reference for this object
    *
    * @var dbconnectReference
    */
   private $dbc;
   /**
    * To store query result reference
    *
    * @var queryResultReference
    */
   private $queryResult;
   /**
    * To store result (as assoc array) coming from select queries
    *
    * @var assocArray
    */
   private $queryResultArray;
   /**
    * To store keyword used in DBQuery object passed as argument to constructor
    *
    * @var string
    */
   private $keyword;
   /**
    * String representation of query to execute
    * Comes from DBQuery object
    *
    * @var string
    */
   private $queryToRun;
   /**
    * Constructor for RunDBQuery objects
    * 
    * Takes a DBQuery object and a database connection reference
    * as arguments and initialise instance variable needed to execute
    * query given by DBQuery object argument
    *
    * @param DBQuery object $dbqueryObject
    * @param dbconnectReference $dbcReference
    */
   function __construct($dbqueryObject, $dbcReference)
   {
      $this->fieldNameSet = array();
      $this->queryResultArray = array();
      $this->setDbc($dbcReference);
      $this->queryToRun = $dbqueryObject->getThisQuery();
      if (is_a($dbqueryObject, 'DBQuery'))
      {
         // run query and assign to $result
         $result =  mysql_query($dbqueryObject->getThisQuery(), $dbcReference);
         $queryKeyword = $dbqueryObject->getKeyword();
         $this->queryResult = $result;
         $this->keyword = $queryKeyword;
      }
      else
      {
         $exception = "RunDBQuery:: argument to constructor must be a DBQuery object";
         trigger_error($exception, E_USER_ERROR);
      }
   }
   /**
    * Runs Queries
    * 
    * Executes query and sets queryResult and queryResultArray values
    * For SELECT queries queryResultArray will refer to an associative array
    *  - array[0][QRESULT] = success+noDataFound
    * 
    * In case a SELECT query was successfully executed but not result were found:
    * This can also be checked by using the public method getNumberOfRows()
    * 
    * For updated, inserted, deleted etc, the result will be as follow:
    *  - array[0][QRESULT] = success 
    *
    */
   public function runQuery()
   {
      if (mysql_errno($this->getDbc()) > 0)
      {
         $this->queryResultArray[0] = array("QRESULT" => mysql_error($this->getDbc()));
      }
      else
      {
         

         if ( $this->getKeyword() == "SELECT" or $this->getKeyword() == 'SELECT_WHERE' or $this->getKeyword() == 'SELECT_ORDER' or $this->getKeyword() == 'SELECT_WHERE_ORDER' or $this->getKeyword() == 'SELECT_WHERE_ORDER_LIMIT')
         {

            if ($this->getNumberOfRows() == 0)
            {
               $this->queryResultArray[0] = array("QRESULT" => "success+noDataFound");
            }
            else
            {
               while ($row = mysql_fetch_assoc($this->getQueryResult()))
               {
                  array_push($this->queryResultArray, $row);
               }
            }
         }
         else
         {
            // query did not need to loop rows
            // special case SHOW
            if ($this->getKeyword() == "SHOW")
            {
               while ($row = mysql_fetch_row($this->getQueryResult()))
               {
                  array_push($this->queryResultArray, $row);
               }
            }
            else
            {
               $this->queryResultArray[0] = array("QRESULT" => "success");
            }
         }
      }
   }

   /**
    * Getter for keyword instance variable
    *
    * @return string
    */
   public function getKeyword()
   {
      return $this->keyword;
   }
   /**
    * Returns value of queryResultArray
    *
    * @return array
    */
   public function getQueryResultArray()
   {
      return $this->queryResultArray;
   }
   /**
    * Returns reference result for current query
    *
    * @return string
    */
   public function getQueryResult()
   {
      return $this->queryResult;
   }
   /**
    * Returns string of query to execute
    *
    * @return string
    */
   public function getQueryToRun()
   {
      return $this->queryToRun;
   }
   /**
    * Returns representation of query
    * 
    * Returns string representation of the query
    * with key value pair
    *
    * @return string $s
    */
   public function toString()
   {
      $s = "";
      foreach ($this->queryResultArray as $value)
      {
         foreach ($value as $k => $v)
         {
            $s .= $k . " " . $v . "<br />";
         }
         $s .= "<br />";
      }
      return $s;
   }

   /**
    * Returns representation of query
    * 
    * Returns string representation of the query with values only.
    * 
    * If checking query result via this method, mainly for non-select queries
    * strip_tags($sV) before checking if equal to "success"
    * 
    * Example 
    * 
    * In cases such as:
    * <code>
    * if ($queryObj->toStringV() == "success")</code>
    * should  be 
    * <code>if (strip_tags($queryObj->toStringV()) == "success")</code>
    * @return string
    *
    */
   public function toStringV()
   {
      $sV = "";
      foreach ($this->queryResultArray as $value)
      {
         foreach ($value as $v)
         {
            $sV .= $v . "<br />";
         }
         $sV .= "<br />";
      }
      return $sV;
   }
   /**
    * Returns query mysql error details
    * 
    * Returns query mysql error details in case a query did not run,
    * otherwise return the string representation of the query notifying it had run
    * successfully.
    * 
    * NOTE: it does not return actual result of the query
    * 
    * @return string
    *
    */
   public function getQueryResultInfo()
   {
      if (mysql_errno($this->getDbc()) > 0)
      {
         return mysql_error($this->getDbc());
      }
      else
      {
         return $this->getQueryToRun() . " has run successfully.";
      }
      return "";
   }
   /**
    * Frees query result
    * 
    * Frees the memory associated with this query object
    * Returns TRUE on success or FALSE on failure
    * 
    * @return boolean 
    *
    */
   public function freeQueryResult()
   {
      return mysql_free_result($this->getQueryResult());
   }
   /**
    * Returns number of rows
    * 
	 * Returns number of rows found in executed query
	 *
	 * @return integer
	 */
   public function getNumberOfRows()
   {
      if ($this->getKeyword() == 'SELECT' OR $this->getKeyword() == 'SELECT_WHERE' OR $this->getKeyword() == 'SELECT_ORDER' OR $this->getKeyword() == 'SELECT_WHERE_ORDER' OR $this->getKeyword() == 'SELECT_WHERE_ORDER_LIMIT')
      {
         return mysql_num_rows($this->getQueryResult());
      }
      return 0;
   }
   /**
    * Returns number of fields
    * 
	 * Returns number of fields found in executed query
	 *
	 * @return integer
	 */
   public function getNumberOfFields()
   {
      return mysql_num_fields($this->getQueryResult());
   }
   /**
	 * Returns result of query
	 *  
	 * key = database field name
	 * index = integer row
	 *
	 * @param string $aKey
	 * @param integer $aIndex
	 * @return mixed
	 */
   public function giveValueOf($aKey,$aIndex)
   {
      return mysql_result($this->getQueryResult(), $aIndex, $aKey);
   }

   /**
    * Sets connection to db
    * 
    * Sets this object connection to database
    * Implementation of SQLable interface
    *
    * @param dbconnect $adbc
    */
   public function setDbc($adbc)
   {
      $this->dbc = $adbc;
   }
   /**
    * Returns DB connection
    * 
    * Returns mySQL connection
    * Implementation of SQLable interface
    *
    * @return dbconnect
    */
   public function getDbc()
   {
      return $this->dbc;
   }
   //------------------------------
   /**
    * Returns query field names set
    * 
    * Returns integer indexed array containing values of the fields names
    * for the executed query.
    * If query did not produce any result returns an empty array
    *
    * @return array
    */
   public function getFieldNameSet()
   {
      $fieldsNameArray = array();
      for ($i=0; $i<$this->getNumberOfFields(); $i++)
      {
         array_push($fieldsNameArray,mysql_fieldname($this->getQueryResult(),$i));
      }

      return $fieldsNameArray;
   }
   /**
    * Returns integer indexed result array
    *
    * @return array
    */
   public function getIntIndexedResultArray()
   {
      $indexedArray = array();
      foreach ($this->queryResultArray as $value) 
      {
      	foreach ($value as $v)
      	{
      	   array_push($indexedArray, $v);
      	}
      }
      return $indexedArray;
   }
   /**
    * Returns number of rows
    * 
    * Returns the number of affected rows by the query,
    * 0 (zero) if none were affected
    *
    * @return int
    */
   public function getAffectedRows()
   {
      return mysql_affected_rows();
   }
   /**
    * Removes duplicate
    * 
    * Removes duplicate values from query result array given by RunDBQuery object
    * passed as argument,leaves just uniques values and returns array created, or false otherwise
    *
    * @return mixed
    */
   public static function resultArrayToSet($rqo)
   {
      if (is_a($rqo, 'RunDBQuery'))
      {
         $a = array();
         $ab = array();
         foreach ($rqo->queryResultArray as $v)
         {
            foreach ($v as $value)
            {
               array_push($a, $value);
            }
         }
         $i = 0;
         $a = array_unique($a);
         foreach ($a as $value)
         {
            $ab[$i] = $value;
            $i++;
         }
         return $ab;
      }
      return false;
   }
}
?>
Return current item: My One Line SQL