Using PHP Objects to Access Your Database Tables (Part 2)
By Tony Marston2005-04-07
Extending the MySQL SELECT statement
|
|
In the 'getData' method of Part 1 of this article I showed the basic MySQL syntax to read data from a database table, as shown below:
$query = "SELECT count(*) FROM $this->tablename $where_str";
The problem with this statement is that it can only read from one table and it will always retrieve all the columns. This does not cover all the options that are available and will quickly cause us to want to execute some custom code instead of using the standard code, which defeats the aim of maximum reusability in OOP. If you look at the section on SELECT statement syntax in the MySQL manual you will notice that it is comprised of several parts, some of which are mandatory while others are optional. You can change the code to allow for these different parts as follows:
$query = "SELECT $select_str FROM $from_str $where_str $group_str $having_str $sort_str $limit_str";
Some of these parts are optional and may be empty, but when they are all put together they must construct a valid query. This should immediately raise two questions:
• How do I construct each of these component parts?
• Where does the data for these component parts come from?
To answer the second question first each of these component parts has its own class variable defined like this:
class Database_TableThe calling script can set the options it needs using code similar to this:
{
var $sql_select;
var $sql_from;
var $sql_where;
var $sql_groupby;
var $sql_having;
var $sql_orderby;
$dbobject = new mytable;The code inside my generic function then constructs each of these component parts using code similar to this:
$dbobject->sql_select = '...';
$dbobject->sql_from = '...';
$dbobject->sql_where = '...';
$dbobject->sql_groupby = '...';
$dbobject->sql_having = '...';
$dbobject->sql_orderby = '...';
$data = $dbobject->getData($where);
if (empty($this->sql_select)) {
$select_str = '*'; // the default is all fields
} else {
$select_str = $this->sql_select;
} // if
if (empty($this->sql_from)) {
$from_str = $this->tablename; // the default is current table
} else {
$from_str = $this->sql_from;
} // ifHere I am actually constructing the WHERE clause from two possible sources both of which are optional, but if both are present they must be combined.
if (empty($where)) {
$where_str = NULL;
} else {
$where_str = "WHERE $where";
} // if
if (!empty($this->sql_where)) {
if (!empty($where_str)) {
$where_str .= " AND $this->sql_where";
} else {
$where_str = "WHERE $this->sql_where";
} // if
} // ifNow for the remainder:
if (!empty($this->sql_groupby)) {
$group_str = "GROUP BY $this->sql_groupby";
} else {
$group_str = NULL;
} // if
if (!empty($this->sql_having)) {
$having_str = "HAVING $this->sql_having";
} else {
$having_str = NULL;
} // if
if (!empty($this->sql_orderby)) {
$sort_str = "ORDER BY $this->sql_orderby";
} else {
$sort_str = NULL;
} // if
if ($rows_per_page > 0) {
$limit_str = 'LIMIT ' .($pageno - 1) * $rows_per_page .',' .$rows_per_page;
} else {
$limit_str = NULL;
} // ifFinally I can execute the completed query, construct an associative array from the result, then return to the calling script.
$query = "SELECT $select_str FROM $from_str $where_str $group_str $having_str $sort_str $limit_str";As you can see it is not rocket science, but it does the trick.
$result = mysql_query($query, $dbconnect) or trigger_error("SQL", E_USER_ERROR);
$array = array();
while ($row = mysql_fetch_assoc($result)) {
$array[] = $row;
} // while
mysql_free_result($result);
return $array;
Tutorial Pages:
» Intended Audience
» Extending the MySQL SELECT statement
» Adding the features of a Data Dictionary/Repository
» Using the Data Dictionary/Repository
» Changing Candidate Keys
» Deleting Rows
» Custom Processing
» Virtual Database Tables
» Summary
|
