/**
* This function register the database connection.
*
* @param $db The YDDatabase object pointing to the database or the named instance.
*
* @returns A reference to the YDDatabase object.
*/
function ®isterDatabase($db = null)
{
if (is_null($db)) {
$this->_db = YDDatabase::getNamedInstance();
} else {
if (is_string($db)) {
$this->_db = YDDatabase::getNamedInstance($db);
} else {
$this->_db = $db;
}
}
$this->_query = YDDatabaseQuery::getInstance($this->_db);
return $this->_db;
}
/**
* This function register the database connection.
*
* @param $db Reference to the database abstraction layer.
*
* @returns A reference to the database abstraction layer.
*/
function ®isterDatabase(&$db)
{
$this->_db =& $db;
$this->_query = YDDatabaseQuery::getInstance($this->_db);
return $this->_db;
}
function actionDefault()
{
// Database instance
$db = YDDatabase::getInstance('mysql', 'test', 'root', '', 'localhost');
// Instantiate the class
$q = YDDatabaseQuery::getInstance($db);
$r = $q->reserved;
// Select action
$q->select();
// Additional options
$q->options(array('DISTINCT'));
// Add a table to the query
// It returns the table alias if defined
$u = $q->from('user');
// Select expressions
$q->expression("id", '', true);
// quote reserved
$q->expression("name", 'user_name', true);
// quote reserved
$q->expression("SUM( " . $r . "value" . $r . " )", 'total');
// Add a where statement
$q->openWhere();
$q->where($r . "id" . $r . "= 144");
// Add another where statement inside the above statement
$q->openWhere('OR');
$q->where($r . "name" . $r . " LIKE 'David%'");
$q->where($r . "id" . $r . " > 13");
$q->closeWhere();
// optional
$q->closeWhere();
// optional
// Add a group by clause
$q->group("id", true, true);
// DESC and quote reserved
// Add ordering
$q->order("name", false, true);
// ASC and quote reserved
$q->order("total", true, true);
// DESC and quote reserved
// Add a having clause
$q->having($r . "total" . $r . " > 100");
// Add a limit
$q->limit(100);
// Add an offset
$q->offset(50);
// Show the SQL statement
YDDebugUtil::dump($q->getQuery());
// Reset the contents of the query
$q->reset();
// ------------------------------------------------------------
// Select query
$q->select();
// Add table with alias 'u'
$u = $q->from('user', 'u');
// Add select expressions for this table
// The expr method is an alias of the expression method
$q->expr('id', '', true);
$q->expr('name', '', true);
// Add a left join
$g = $q->join('LEFT', 'group', "g");
// Specify the fields to use for the join
$q->on("{$u}.group_id = {$g}.id");
// Add the joined fields
$q->expr("{$g}.id", 'gid', true);
$q->expr("{$g}.name", 'group_name', true);
// Add an inner join
$a = $q->join('INNER', 'attach', "a");
// Specify the fields to use for the join
$q->on("{$g}.attach_id = {$a}.id");
// Open a group
$q->openOn('OR');
// Specify more fields to use for the join group
$q->on("{$a}.size > 150");
$q->on("{$a}.status = 3");
// Add some more fields
$q->expr("{$a}.id", 'aid', true);
$q->expr("{$a}.name", 'attach_name', true);
// Add a where clause
$q->where("{$g}.id = 144");
// Add ordering
$q->order("{$a}.name", false, true);
// Add a limit
$q->limit(50);
// Show the SQL statement
YDDebugUtil::dump($q->getQuery());
// Reset the query
$q->reset();
// ------------------------------------------------------------
// Delete query
$q->delete();
// Add a table
$q->from('user');
// Specify the where clause
$q->where("id = 144");
// Show the SQL statement
YDDebugUtil::dump($q->getQuery());
// Reset the query
$q->reset();
// ------------------------------------------------------------
$values = array('name' => "David O'Connor\r\nThe Great", 'email' => '*****@*****.**', 'admin' => 1, 'purple' => null);
// Insert query
$q->insert();
// Add a table
// The into method is an alias of the from method
$q->into('user');
// Set the values
$q->values($values);
// Add the where clause
$q->where("id = 144");
// Show the SQL statement
YDDebugUtil::dump($q->getQuery());
// Reset the query
$q->reset();
// ------------------------------------------------------------
// Update query
$q->update();
// Add a table
// The table method is an alias of the from method
$q->table('user');
// Set the values
// The set method is an alias of the values method
$q->set($values);
// Add the where clause
$q->where("id = 144");
// Show the SQL statement
YDDebugUtil::dump($q->getQuery());
}
