Gullele's Corner

Here is some more pattern for you. I have provided how to implement singleton pattern for database.
The idea is:
Why don’t we encapsulate our queries to the database in an object?
What we need is to execute a query whether it is update query – like delete, update and insert or retrieval query like select. In both cases, the the accomplishment would be one – executing – with different query parameters.
Which means we need only a single interface per application for that would simply execute our queries independent of the query string.
The encapsulating class for queries (acting as command) shall contain the implementation of method executeQuery().

/* 
Query class: implementing the basic tasks of query
Author: Kaleb B. Woldearegay
Date: 18.Apr.2009

Open for any kind of modification and use.
Please let me know when you use and modify it as I will learn from that 
--Query.php--
*/
	class Query{
		protected $sql;
		protected $db; /*any database implementing IDatabase */
		/*Method executeQuery would accept query string 
and executes it using the database's execute query method */
		public function __construct($database){
			$this->db = $database;
		}		
		protected function executeQuery($sql){
			return $this->db->executeQuery($sql);
		}
	}

Another point here would be, since we have two major tasks to perform against database – namely update queries and retrieval queries – we need two query commands which would be the derived classes of Query class

	/* This class would perform non resulted queries - 
like update, insert and delete
-- Query.php--
 */
	class UpdateQuery extends Query{
		/* constructor would pass the database for query class */
		public function __construct($db){
			parent::__construct($db);
		}
		/* execution of queries would goes here */
		public function executeQuery($sql){
			return parent::executeQuery($sql);		
		}
	}


	/* This class would perform resulted queries - like select */
	class ResultQuery extends Query{
		/* constructor would pass the database for query class */
		private $result; /* result object that would be created from database */
		public function __construct($db){
			parent::__construct($db);
		}
		/* constructor would pass the database for query class */
		public function executeQuery($sql){
                        /* Assigning the object */
			$this->result =  parent::executeQuery($sql); 
			return $this; //return the result query;
		}
		/* Collect the row from the collected result */
		public function getRow(){
			/*using associative array for the rows*/
		       if ( $row=mysql_fetch_array($this->result,MYSQL_ASSOC) ) {

            			return $row;

        		} else if ( $this->size() > 0 ) {

            			mysql_data_seek($this->query,0);

            			return false;

        		} else {

            			return false;

        		}

    		}
		/* Size of the collected result */
    		public function size () {

        		return mysql_num_rows($this->result);

    		}
	}

The hot-to-go part is simple. Any query should be executed! But the type of query is different – some require result set and other don’t. Which is a kind of topping we want on our query pizza. UpdateQuery and ResultQuery would provide the different type of flavors.
So, the client would encapsulate its request in either of the commands and would call an execute method.
But, this has some problem. We would need to know specifically of which type the query is. But what if we have an interface which would accept any command object and invoke its execute method – this seems neat..

/*
Query class: implementing the basic tasks of query
Author: Kaleb B. Woldearegay
Date: 18.Apr.2009

An invoker class would implement the 'command' pattern over 
the database applicaion
the invoker would be called by any client who needs a service 
from database either update or non-update

--Query.Invoke.php--
*/
	class QueryInvoker{
		private $query_obj; /*any query object inheriting 
from Query class */
		public function __construct($query_obj){
			$this->query_obj=$query_obj;
		}


		public function setQuery($query_obj){
			$this->query_obj=$query_obj;
		}
		/*the invoker would accept object [ which could be 
either update query object or non update query object ] which it 
has no any idea, and would invoke its  execute method */
		public function execute($query_obj,$sql){
			return $query_obj->executeQuery($sql);
		}
	

An example showing the interaction would be:

/*this example assumes you would put all your classes in the 
folder named classes - which is a practice of course.
	require('classes/MySQL.Database.php');
	require('classes/Query.php');
	require('classes/Query.Invoke.php');
	$obj_invoker=new QueryInvoker();
	$objDb=Mysql::getInstance();
	$objDb->connect();
	$objUpdate=new ResultQuery($objDb);
	$sql="select * from tbl";
	$result = $obj_invoker->execute($objUpdate,$sql);
        while($row = $result->getRow()){do stuff}

You can get Mysql class in the singleton pattern part of my article.