First and foremost you should take into consideration is:
- There is no good general model. Every projects needs its own model.
- easy to read, managable code
- do not repeat the same code (or queries), so if you have a function for a certain task and you want it to be ordered another way, modify the function itself and do not clone it
- Use complex data structures, like arrays or objects to send data to a function, so you don't have to always modify the parameters a function needs
- resource usage. The more you want it to be all around, general solution the more resource it will use.
How badly will the performance be affected if I'm selecting * just incase the calling function might need any of the information pulled down?
It depends on the load of your website. Most of the time (if you don't pull big blobs and text) * is ok, but when resources are scarce, you have to specify the columns. So you can save some IO time.
I feel this makes the model extremely bloated and very tied, creating a lot of duplicated code. It might be better having this kind of model;
Maybe try this:
First of all, for complex queries, I use this class I made long time ago for MySQL. It helps a helluva lot.
class sqlAssembler
{
private $data = array();
var $S = array();
var $F = array();
var $W = array();
var $G = array();
var $H = array();
var $O = array();
var $L = array();
//Clause abbreviations
var $clauselist = array
(
'S' => 'SELECT',
'F' => 'FROM',
'W' => 'WHERE',
'G' => 'GROUP BY',
'H' => 'HAVING',
'O' => 'ORDER BY',
'L' => 'LIMIT'
);
//Default clause separators
var $clausesep = array
(
'S' => ',',
'F' => ',',
'W' => ' AND ',
'G' => ',',
'H' => ' AND ',
'O' => ',',
'L' => ''
);
function gen()
{
$tmp = '';
foreach ( $this->clauselist as $area => $clause )
{
if ( count($this->{$area}) )
{
$tmp .= ($clause != 'S' ? ' ' : '') . $clause . ' ';
for ($i=0; $i < count($this->{$area}); $i++)
{
//echo $area = (string)$area;
$tmp .= $this->{$area}[$i];
} //for
} //if
} //foreach
return $tmp;
} //function
function genSection($area, $showsection = 0)
{
$tmp = '';
if ( count($this->{$area}) )
{
for ($i=0; $i < count($this->{$area}); $i++)
{
$tmp .= $this->{$area}[$i];
} //for
} //if
return $tmp;
} //function
function clear()
{
foreach ($this as $area => $v)
{
//We only care about uppercase variables... do not declare any else variable with ALL UPPERCASE since it will be purged
if (ctype_upper($area))
{
if ($area == 'L')
$this->$area = '';
else
$this->$area = array();
} //if
} //foreach
} //function
public function add($area, $str, $criteria = 1, $sep = '#')
{
if ($criteria)
{
if ($sep == '#')
$sep = $this->clausesep[$area];
//Postgres' OFFSET should be set like: $str = '25 OFFSET 0'
//Not very neat I know, but fuck it
if ($area == 'L')
{
$this->{$area} = array();
} //if
//$ref = $this->$area;
$this->{$area}[] = (count($this->$area) ? $sep : '').$str;
return count($this->$area)-1;
} //if
} //function
public function del($area,$index)
{
if ( isset($this->{$area}[$index]) )
unset($this->{$area}[$index]);
else
trigger_error("Index nr. {$index} not found in {$area}!",E_USER_ERROR);
} //function
//-*-* MAGIC CHAIN FUNCTIONS
public function S($str,$criteria = 1,$sep = '#')
{
$this->add(__FUNCTION__,$str,$criteria,$sep);
return $this;
} //function
public function F($str,$criteria = 1,$sep = '#')
{
$this->add(__FUNCTION__,$str,$criteria,$sep);
return $this;
} //function
public function W($str,$criteria = 1,$sep = '#')
{
$this->add(__FUNCTION__,$str,$criteria,$sep);
return $this;
} //function
public function G($str,$criteria = 1,$sep = '#')
{
$this->add(__FUNCTION__,$str,$criteria,$sep);
return $this;
} //function
public function H($str,$criteria = 1,$sep = '#')
{
$this->add(__FUNCTION__,$str,$criteria,$sep);
return $this;
} //function
public function O($str,$criteria = 1,$sep = '#')
{
$this->add(__FUNCTION__,$str,$criteria,$sep);
return $this;
} //function
public function L($str,$criteria = 1,$sep = '#')
{
$this->add(__FUNCTION__,$str,$criteria,$sep);
return $this;
} //function
} //_sql
Maybe try this:
function getShoppingCart($d)
{
$xx = new sqlAssembler();
$xx->S('*')->
F('items')->
//Notice, that we specified a criteria... if $d['id_item'] exists it will be joined to the WHERE clause, if not it will be left out
W("(id_item > '{$d[id_item]}')",$d['id_item'])->
//Same here
O("dt DESC",$d['date'])
$sql = echo $xx->gen();
//id_item = 11, date = 2009-11-12
//$sql = "SELECT * FROM items WHERE (id_item > '11') ORDER BY dt DESC";
//id_item = null, date = null
//$sql = "SELECT * FROM items";
$data = sqlArray($sql);
//... handle data
}