Direct queries

All models contain a Database class object, which allows you to make direct SQL queries to the database. This object is available inside the model object as the $this -> db property. The model table is available inside the model class as the $this -> table property. To build secure SQL queries, the secure() method is used, described in the Security section.

The following methods exist for sending direct queries:

query() - executes the received SQL query

//All published records of the current table
$result = $this -> db -> query("SELECT *
                                FROM `'.$this -> table.'` 
                                WHERE `public`='1'
                                ORDER BY `date` DESC");
while($row = $this -> db -> fetch($result, 'ASSOC'))
{
   ...
}

getAll() - retrieves all records and returns the associated array; if the table has an 'id' field, this field will become the key in the resulting array.

//All published comments to the news with id=32
$rows = $this -> db -> getAll("SELECT `name`,`date`,`content`
                               FROM `news_comments`
                               WHERE `news_id`='32'
                               AND `public`='1'
                               ORDER BY `date` DESC");
foreach($rows as $row)
{
   ...
}

getCount() - count the number of records in a table, returns an integer

//Total number of articles
$total = $this -> db -> getCount('articles');

//Number of active products in the section
$total = $this -> db -> getCount('products', "`catalog`='98' AND `active`='1'");

getRow() - retrieves a row from the table, returns an associated array of fields

//All product fields with id=527
$row = $this -> db -> getRow("SELECT * FROM `products` WHERE `id`='527'");

getColumn() - retrieves a column from the table, returns an array

//Titles of all events for a specific date
$titles = $this -> db -> getColunm("SELECT `title` FROM `events` WHERE `date`='2011-03-24'");

getCell() - retrieves a cell from the table

//Returns the price of the product with the name 'New product'
$price = $this -> db -> getCell("SELECT `price` FROM `products` WHERE `name`='New product'");