Models

Models are the basis for every single application, since every application has data. PHPDevShell attempts to make data management in the MVC structure as easy as possible. We see all data as objects, and these objects can be any form of data. If it contains information, it can be queried and the data will be returned. Queries can include actual database queries, functions, or manually set data.

Invoke a data Query

It does not matter if you are in a helper class, controller or even a model itself, you can always execute a query request using:

$this->db->invokeQuery('nameOfDataClass');

The query you are calling sits in a file that has the same name as your controller. For instance, if you have a controller called

my-controller.php

the model with all the queries will be called

my-controller.query.php

in the models folder.

A model file can also be reused in other controllers/models/classes by simply including the filename from wherever else it is needed.

Each data query is a class of its own, meaning for each data query you will create a class like this:

class nameOfDataClass extends PHPDS_query
{
	// Class always extends PHPDS_query
	/**
	 * Override invoke
	 * @return string
	 */
	public function invoke()
	{
		$array = array('Data Value 1', 'Data Value 2');
		return $array;
	}
}

Now whenever we call

$array = $this->db->invokeQuery('nameOfDataClass');

we will have the data that 'nameOfDataClass' returns

The use of the optional public function invoke() from within the query class always instructs the class that you wish to return custom data. You can either manipulate data from a database query or return data that has nothing to do with the database at all.

The Database Layer

The query system does not stop there. It allows you to query the database with powerful and easy to use database functions with really little effort.

So lets do our first read query. Lets just get the results from the database. One query file can have multiple class objects, in our example file my-controller.query.php we write

class exampleQuery extends PHPDS_query
{
	protected $sql = "
		SELECT
			id, example_name, example_note
		FROM
			_db_ExamplePlugin_example
    	";
}

Now if we call this data from our controller with something like this:

$array = $this->db->invokeQuery('exampleQuery');

This will return return an array ready to be used or looped.

Passing data to query

Obviously life is not that simple and we will usually need to modify the sql conditions in our query. How would we handle this?

The query model handles whatever you throw at it. Passing as many variables to it is very easy actually.

Lets look at the example;

class exampleQuery extends PHPDS_query
{
	protected $sql = "
		SELECT
			id, example_name, example_note
		FROM
			_db_ExamplePlugin_example
		WHERE
			example_name = '%s'
		AND
			example_note = '%s'
    	";
}

As can be seen, in this example, we need to pass through two values to the database query for the WHERE clause. To do this is simple;

$array = $this->db->invokeQuery('exampleQuery', 'Jason', 'Hello');

Now 'Jason' and 'Hello' will be included in the WHERE condition and only the required results will be returned. The variables are used in the order passed. So Jason is the first variable passed so it will be used with the first occurrence of a data type specifier, in this case the %s in example_name.

This use case is simple but has two problems: you have to know the order of the parameters, and if a parameter is used twice, you have to pass it twice. To avoid these, just use named parameters:

class exampleQuery extends PHPDS_query
{
	protected $sql = "
		SELECT
			id, example_name, example_note
		FROM
			_db_ExamplePlugin_example
		WHERE
			example_name = '%(name)s'
		AND
			example_note = '%(note)s'
    	";
}

To use this query, you have to use an associative array:

$array = $this->db->invokeQueryWith('exampleQuery', 
		array('name' => 'Jason', 'note' => 'Hello')
	);

$array = $this->db->invokeQuery('exampleQuery', 'Jason', 'Hello')
	);
$array = $this->db->invokeQueryWith('exampleQuery', 
		array('name' => 'Jason', 'note' => 'Hello')
	);

Data query switches

The query object allows you to request data manipulation through simple switches. There are a few switches available. Here they are with a short explanation:

	/**
	 * The name of the field to use as a key.
	 *
	 * By default this is set to the special value '__auto__', which means the class
	 *  will automatically figure out which field/column to be use as the key
	 * If you want the returned array keys to be set to a specific field/column
	 *  value, then you just need to specify that field/column name in $keyField
	 * If you want the returned array keys to be set to a numerical index
	 *  starting at 0, then you need to set $keyField=false
	 * @var string
	 */
	protected $keyField = '__auto__';

	/**
	 * Make a field the point of interest
	 *
	 * This field changes the way some arrays are returned:
	 * - if $focus contains a field name, a row will be the value of this field (scalar) instead of an array of all values in the row
	 * - if the row doesn't contain a field an empty value is used for the row
	 * @var string
	 */
	protected $focus = ''; // can be empty for 'no' or any other value for field name

	/**
	 * strips any row with no content
	 * @var boolean
	 */
	protected $noEmptyRow = false;

	/**
	 * Guidelines to typecast/forcecast the result data
	 *
	 * @var string | array of strings
	 */
	protected $typecast;

	/**
	 * The first line of the result is returned instead of a one-line array
	 *
	 * @var boolean
	 */
	protected $singleRow = false;

	/**
	 * Automatically escape bad chars for all in-parameters
	 * @var boolean
	 */
	protected $autoProtect = false;

	/**
	 * Instead of the query result, returns the last_insert_id()
	 * @var boolean
	 */
	protected $returnId = false;

	/**
	 * Return one value from the asked field of the asked line
	 * @var boolean
	 */
	protected $singleValue = false;

To request data manipulation in a query, simply include the switch in the database object class, for example;

class editExampleQuery extends PHPDS_query
{
	protected $sql = "
		SELECT
			example_name
		FROM
			_db_ExamplePlugin_example
		WHERE
			id = %u
    	";
	protected $singleValue = true;
}

We are basically telling the query system to call the query, but don't return an array, only return the required value. Now only column name will be returned directly.

To further the example, say for instance we want to return the ID of the row we just inserted we can also add the following switch;

class writeExampleQuery extends PHPDS_query
{
	protected $sql = "
		INSERT INTO
			_db_ExamplePlugin_example (id, example_name, example_note, alias)
		VALUES
			(%u, '%s', '%s', '%s')
    	";
	protected $returnId = true;
}

Note the %u and %s are data type specifiers (string=%s and unsigned integer=%u). These are the same as what sprintf would use.

Lets invoke the query and get the returned id;

$id = $this->db->invokeQuery('exampleQuery', '', 'Jason', 'Some Note', 'hello');
// $id now contains the returned database id.

Checking or altering parameters before executing the query

Not all parameters of a query are to be specified when invoking it. For example, a query could use the current user ID. Let's look at this example which extends the query above:

class editMeExampleQuery extends editExampleQuery
{
	
	public function checkParameters(&$parameters = null)
	{
		$me = $this->user->currentUserID();
		$parameters = array($me);

		return true;
	}
}

The method checkParameters() is called just before the query is actually sent to the database. It allows the query object to check the caller's parameter values and/or inject its own values. It can also prevent the query from being sent (if for example some parameters are missing).

As you can also see in this example, queries are real OOP entities, and therefore enjoy all the mechanisms like extending an existing class.

Checking or altering results after executing the query

The data spit from the database sometimes need a little polishing before being sent back to the caller. A query object has the opportunity to ensure its results are conformant to what the caller expects. See this example:

class editPrettyExampleQuery extends editExampleQuery
{
	
	public function checkResults(&$results = null)
	{
		return (is_string($results)) ? "User '$result'" : false;
	}
}

The method checkResults() allows the query to check and/or alter the result data before sending it back. In this case, it adds the word "User" before the user name if the query returned one, or signals an error (by returning False) if not.

Further enhancing database objects before they are returned.

Like everything else in PHPDevShell, the query can be overridden. In many cases you would want to manipulate data, further enhancing queries and filtering your data before it is returned to whatever requested it.

To do so is straight forward, you will override the invoke (using public function invoke()), do the query and return the data. To override invoke we will do this;

class exampleQuery extends PHPDS_query
{
	protected $sql = "
		SELECT
			id, example_name, example_note
		FROM
			_db_ExamplePlugin_example
		WHERE
			example_name = '%s'
		AND
			example_note = '%s'
    	";

	/**
	 * Override invoke
	 * @return string
	 */
	//NOTE: Adding this "public function invoke" is how we override and
	//      manipulate the data returned from the SQL query.
	public function invoke($parameters)
	{
		// We are now in a new override instance of invoke.
		// First lets capture the data that is being passed to parameters.
		list($example_name, $example_note) = $parameters;
		
		// We can manipulate the data now, lets strip white spaces from $example_note as an example.
		$example_note = trim($example_note);

		// We can now do the query as normal and further manipulate data. To do this we do the original invoke.
		$array = parent::invoke(array($example_name, $example_note));

		// Now we can loop the data and do whatever we want with it.
		foreach ($array as $values) {
			$id = $values['id'];
			$name = $values['example_name'];
			$count ++;
			$new_array[] = array($id, $name, $count);
		}
	
		// We can even do more queries from here.
		$this->db->invokeQuery('MoreQueries');

		return $new_array;
	}
}

As you can see, you truly have full control. Remember that you can invokeQueries inside other queries, or include any .query.php file in any other file, allowing you to reuse queries over and over again.

Passing an array of values instead of individual values

What if I have an array of values that I want to pass with invokeQuery, instead of passing individual values? Say for example:

$where_clause = array('Jason', 'Hello');
$array = $this->db->invokeQuery('exampleQuery', $where_clause);

Since the values passed are placed into an array, we would need to override the invoke to pull them back out. Notice in the code below that there is only one change.. See remarks:

class exampleQuery extends PHPDS_query
{
	protected $sql = "
		SELECT
			id, example_name, example_note
		FROM
			_db_ExamplePlugin_example
		WHERE
			example_name = '%s'
		AND
			example_note = '%s'
    	";

	/**
	 * Override invoke
	 * @return string
	 */
	//NOTE: Adding this "public function invoke" is how we override and
	//      manipulate the data returned from the SQL query.
	public function invoke($parameters)
	{
	/* Since we passed in an array of values, that array will be indexed into an array at index[0], so we need to get them back out.
	 * NOTICE: The only change to the below code is that we changed this:
	 * = parameters;
	 * to this:
	 * = $parameters['0'];
	 */
		// We are now in a new override instance of invoke.
		// First lets capture the data that is being passed to parameters.
		list($example_name, $example_note) = $parameters['0'];
		
		// We can manipulate the data now, lets strip white spaces from $example_note as an example.
		$example_note = trim($example_note);

		// We can now do the query as normal and further manipulate data. To do this we do the original invoke.
		$array = parent::invoke(array($example_name, $example_note));

		// Now we can loop the data and do whatever we want with it.
		foreach ($array as $values) {
			$id = $values['id'];
			$name = $values['example_name'];
			$count ++;
			$new_array[] = array($id, $name, $count);
		}
	
		// We can even do more queries from here.
		$this->db->invokeQuery('MoreQueries');

		return $new_array;
	}
}

Register a class

Sometimes models becomes complex and the same model is used over and over again. When this is the case it is highly recommended to register a class instead, add the class in includes and then call it with the factory method.

This is simpler than it might sound. First we will create the class inside your plugins includes folder. The class must be named with .class.php at the end to enable on-demand loading.

So lets call this class;

includes/myClass.class.php

Obviously a helper class should have its own models file to do its queries in, this is done exactly like with normal controllers, in the root models folder we add;

models/myClass.query.php

You can now invokeQueries from your helper class called myClass.class.php like you would expect.

The helper class is generally called from the controller or model file, but there is no right location for calling it, just wherever it is needed. A helper class must start with the name you called the file, it should also extend PHPDS_dependant, this allows it to share resources;

class myClass extends PHPDS_dependant
{
	public function someMethod ()
	{
		// We can also invoke queries.
		$name = $this->db->invokeQuery('getName');
		return "Hello $name";
	}
}

To call helper classes is easy too.
NOTE: You might want to register your class in your plugins config file enabling you to share your class with all your plugins.

Lets call our helper;

$myClass = $this->factory('myClass');
echo $myClass->someMethod();

TRANSACTIONS AND ROW LOCKING

There will be certain situations where you need to use START TRANSACTION and COMMIT to perform a database query and lock a row so no one else can access it until you are done with it. If you are using MySQL, the InnoDB engine supports this type of transaction and row locking.

Let's look at an example where we need to retrieve a unique number, and need to make sure nobody else retrieves that same number. Here are the steps we need to perform:

#1- Use START TRANSACTION.

#2- Use SELECT with a FOR UPDATE to lock the row and retrieve the number.

#3- UPDATE the row with a new number for the next person.

#4- Use COMMIT to unlock the row for others.

DATABASE TABLE example

Here is the SQL for the test database table we will use in this example:

CREATE TABLE `pds_test_unique_number` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `prefix` text COLLATE utf8_unicode_ci NOT NULL,
  `unique_number` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `pds_test_unique_number` (`id`, `prefix`, `unique_number`) VALUES
(1, 'ABC', 1000001);

CONTROLLER file example

Our controller file will look like this:

<?php
class getUniqueNumber extends PHPDS_controller
{
	public function execute()
	{
		//Give it a nice heading
		$this->template->heading(_('GET UNIQUE NUMBER'));

		//Call our query that will return a unique id
		$unique_number = $this->db->invokeQuery('myPlugin_getNewUniqueId');

		//Call the template file
		$view = $this->factory('views');
		$view->set('unique_number', $unique_number);
		$view->show();
	}
}
return 'getUniqueNumber';

MODEL file example

Our model file will look like this:

<?php
class myPlugin_getNewUniqueId extends PHPDS_query
{
	public function invoke($params)
	{
        
        //Need to START TRANSACTION so we can lock row.
	//NOTE: To lock a row with SELECT you need to use FOR UPDATE.
        $this->db->startTransaction();

        //Get the next unique id
        $results = $this->db->invokeQuery('myPlugin_getNextAvailableUniqueId');

	//Extract the row id and the unique_id from the returned query
        $id = $results['id'];
        $unique_id = $results['unique_id']; 
        
        //Increase the number portion of the id by one in the table for the next person
        $this->db->invokeQuery('myPlugin_increaseUniqueIdByOne', $id);

        //Need to COMMIT so we can unlock row
        $this->db->endTransaction();
        
        //return $id;
        return $unique_id;
	}
}

class myPlugin_getNextAvailableUniqueId extends PHPDS_query
{
    protected $sql = "
        SELECT id,prefix,unique_number 
        FROM pds_test_unique_number 
        LIMIT 1 
        FOR UPDATE;
    ";
    protected $keyField=false;

	public function invoke($params)
	{
        //Invoke the db query.
	//NOTE: The SELECT query needs to use FOR UPDATE in order to lock the row.
	$results = parent::invoke();
        
        //Lets get the db row id that was used so we can update it later
        $id = $results[0]['id'];
        
        //UNIQUE ID is a combination of the following two fields, so let's put together.
        $unique_id = $results[0]['prefix'].$results[0]['unique_number'];
 
        //Return the results
	return array('id' => $id, 'unique_id' => $unique_id);;
	}    
}

class myPlugin_increaseUniqueIdByOne extends PHPDS_query
{
    //This will increment the specified field by one for the next person.
    protected $sql = "
        UPDATE pds_test_unique_number 
        SET unique_number = unique_number + 1
        WHERE id=%u
        LIMIT 1;
    ";
}

require_once 'plugins/ExamplePlugin/models/someModel1.query.php';
require_once 'plugins/ExamplePlugin/models/someModel2.query.php';
// Now I can use their invokes.
$this->db->invokeQuery('queryFromModel1');
$this->db->invokeQuery('queryFromModel2');

VIEW file example

Our view file will look like this:

The unique id is: {$unique_number}

This documentation will be extended soon, there are many more advanced uses available for queries and the models system...