Can I Hve Cod Pls!!!!
Enough theory, lets get coding. First we will create a template for our object and build from that.
PHP Code:
class DBQuery
{
/**
* Holds a reference to an object which implements the DB interface.
*/
protected $db;
/**
* Set to true if this is a stored procedure.
*/
protected $stored_procedure = false;
/**
* Holds a query with all the strings removed.
*/
private $query;
/**
* Used to match quotes in SQL
*/
private static $QUOTE_MATCH = "/(\".*(?<!\\\)\")|('.*(?<!\\\)')/Ue";
/**
* An array containing all the strings, if any, of the query.
*/
protected $query_strings = array();
protected $result;
/**
* Class Constructor
* @param DB $db Reference to an object which implements the DB interface.
*/
public function __construct(DB $db)
{
$this->db = $db;
}
public function prepare($query)
{
$this->stored_procedure = true;
}
public function compile($args)
{
}
public function execute($query)
{
}
}
The Prepare Function
We will build the prepare() function first. To ensure that no characters within quotes are accidentally parsed as place holders, we remove all strings and store them temporarily in an array. The strings themselves are also replaced with place holders identified by a sequence of characters which should never appear in an SQL statement. During compilation of the query, the procedure placeholders are first substituted, then the strings of put back into the query. We accomplish this with the help of the preg_replace() function and a helper, function, used as a callback of the preg_replace() function.
PHP Code:
/**
* Prepares the query as a stored procedure.
* @param string $query Prepared query text
* @return void
*/
public function prepare($query)
{
$this->stored_procedure = true;
$this->quote_store = array(); // clear the quote store
$this->query = preg_replace(self::$QUOTE_MATCH, '$this->sql_quote_replace("\1"?"\1":\'\2\')', $query);
}
private function sql_quote_replace($match)
{
$number = count($this->query_strings);
$this->query_strings[] = $match;
return "$||$$number";
}
Notice the use of the private visibility for the static QUOTE_MATCH property, the quote_store property and the sql_quote_replace() function. We want these to be private rather than protected to ensure that any subclass which overrides the query class prepare() method uses its own mechanisms to exclude quotes.
The Compile Function
Next task is to build the compile() and execute() functions.
The compile() function will:
- Take a variable number of arguments; which, match the place holders in the query.
- Check that the place holder is of the correct data type and substitute it for the value in the argument.
- Return the query as a string but does not execute it.
- Throw an exception if the query object has not been initialised as a stored procedure using the prepare() function.
PHP Code:
/**
* Returns the compiled query without executing it.
* @param mixed $args,... Query Parameters
* @return string Compiled Query
*/
public function compile($params)
{
if (! $this->stored_procedure) {
throw new Exception("Stored procedure has not been initialized.");
}
/* substitute parameters */
$params = func_get_args(); // get function arguments
$query = preg_replace("/(?<!\\\\)\:(\d+)([SIN])/e", '$this->compile_callback($params, \1, "\2")', $this->query);
return $this->add_strings($query); // put the strings back into the query
}
/**
* Re-inserts strings removed by the prepare() function.
*/
private function add_strings($string)
{
$numbers = array_keys($this->query_strings);
$count = count($numbers);
$searches = array();
for($x = 0; $x < $count; $x++) {
$searches[$x] = "$||\${$numbers[$x]}";
}
return str_replace($searches, $this->query_strings, $string);
}
/**
* Executed each time a place holder is substituted in the stored procedure.
*/
protected function compile_callback($params, $index, $type)
{
--$index;
/* throw an exception */
if (! isset($params[$index])) {
throw new Exception("Required number of arguments not sent to stored procedure.");
}
/* you may want to add additional types such as dates and times here */
switch ($type) {
case 'S':
return '"' . $this->db->escape_string($params[$index]) . '"';
break;
case 'I':
return (int) $params[$index];
break;
case 'N':
return (float) $params[$index];
default:
throw new Exception("Unrecognised data type '$type' specified in stored procedure.");
}
}
The compile() function uses two additional functions. The compile_callback() function is used as a callback in the preg_replace() function call, it is executed each time a place holder is found in the query; replacing the place holder with the value in passed to the compile function.
The Execute Function
Finally, the execute() function. The execute() function compiles the query and then executes it using the DB object which was used to initialise the DBQuery object, notice how the call_user_func_array() function is used to get the compiled query:
PHP Code:
/**
* Executes the current Query
*
* Executes the current query replacing any place holders with the supplied
* parameters.
*
* @param mixed $queryParams,... Query parameter
* @return resource A reference to the resource representing the executed query.
*/
public function execute($queryParams = '')
{
//example: SELECT * FROM table WHERE name=:1S AND type=:2I AND level=:3N
$args = func_get_args();
if ($this->stored_procedure) {
/* call the compile function to get the query */
$query = call_user_func_array(array($this, 'compile'), $args);
} else {
/* a stored procedure was not initialised, so executre this as a standard query */
$query = $queryParams;
}
$this->result = $this->db->query($query);
return $this->result;
}
The complete source for the query object may be found as an attachment to this post. Remember that we are reinventing a very simplistic version of the wheel here, as stored procedures and database abstraction has already been addressed by the PearDB PHP extension.
A short Example
To demonstrate how the query object may be used, i have constructed the a small example which uses the DBQuery object as a stored procedure in checking for a correct user name and password.
PHP Code:
require 'mysql_db.php5';
require_once 'query2.php5';
$db = new MySqlDb;
$db->connect('host', 'username', 'pass');
$db->query('use content_management_system');
$query = new DBQuery($db);
$query->prepare('SELECT fname,sname FROM users WHERE username=:1S AND pword=:2S AND expire_time<:3I');
if ($result = $query->execute("visualad", "apron", time())) {
if ($db->num_rows($result) == 1) {
echo('Correct Credentials');
} else {
echo('InCorrect Credentials / Session Expired');
}
} else {
echo('Error executing query: ' . $db->error());
}
Next Time: We will have a look at another new feature in PHP 5, type hinting. We will also extend the DBQuery allowing it to support all the functions in the DB interface which operate on the result of an executed query. This will be accomplished using delegation.
If you have any comments regarding this tutorial please post them here, not in this thread.
