Public Member Functions | Data Fields
Model Class Reference

MySql query abstractions. Base for all database models. More...

Inheritance diagram for Model:
DataBaseMySql Dispersion

Public Member Functions

 Model ($db_connection)
 
 setTable ($table)
 
 getPrimaryKey ()
 
 where ($val, $logical_operator= 'and', $compare_sign= '=')
 
 whereIn ($value, $invalues, $not=false)
 
 values ($values=null)
 
 setValues ($values)
 
 select ($conditions="", $values=null)
 
 selectRow ($primary_value)
 
 selectRows ($options=array())
 
 insert ($values)
 
 updateRow ($values, $primary_value)
 
 updateRows ($values, $where="")
 
 deleteRow ($primary_value)
 
 deleteRows ($where="")
 
 countRows ($options="")
 
- Public Member Functions inherited from DataBaseMySql
 DataBaseMySql ($db_connection)
 
 connection ()
 
 query ($query)
 
 execute ($query)
 
 nextObject ($result=null)
 
 numRows ($result=null)
 
 affectedRows ()
 
 queryOneRow ($query)
 
 lastId ()
 
 getQueriesCount ()
 
 resetRow ($result=null)
 
 close ()
 
- Public Member Functions inherited from Dispersion
 Dispersion ()
 
 emptyLayout ()
 
 set ($name, $value)
 
 _set ($name, $value)
 
 insertView ($name, $index=-1)
 

Data Fields

 $_primary_key = null
 
 $table
 
- Data Fields inherited from DataBaseMySql
 $_connection = null
 
 $defaultDebug
 
 $nr_queries
 
 $last_result
 
 $sql_affected_rows
 
- Data Fields inherited from Dispersion
 $model_ob = null
 
 $debug_ob = null
 
 $_variables = array()
 
 $_content = array()
 
 $_content_count = 0
 
 $model
 
 $debug
 

Additional Inherited Members

- Protected Member Functions inherited from Dispersion
 requireConfigFile ($file_name, $required=false)
 

Detailed Description

MySql query abstractions. Base for all database models.

Version
1.1
Author
DinuSV

Simple Queries

Query with one result

$result = $this->model->query( 'SELECT * FROM `my_table`' );
$selectedRows = $this->model->numRows( $result );

Execute query and get number of affected rows.

$this->model->execute( 'UPDATE `my_table` SET `key`=1 ');
// Get number of affectd rows
$affectedRows = $this->model->affectedRows();

Query with one result

$row = $this->model->queryOneRow( 'SELECT * FROM `my_table` WHERE id=1' );

Selection Methods

The table used with the following methods is by default the same as the name of the model, or can be set using the Model::setTable() method.

Selecting, or executing simple selection queries can be done using the select() method :

// SELECT * FROM `current_table`
$this->model->select();
// SELECT * FROM `current_table` WHERE id=5 AND otherid=6
$this->model->select( array( 'id' => 5, 'otherid' => 6 ) ); // OR
$this->model->select( 'WHERE id=5 AND otherid=6' );
// SELECT * FROM `current_table` ORDER BY id DESC
$this->model->select( 'ORDER BY id desc' );
// SELECT id, otherid FROM `current_table`
$this->model->select( null, 'id, otherid' ); // OR
$this->model->select( null, array( 'id', 'otherid' ) );

Advanced selection can be done by using Model::selectRows

Selecting a single row can be done either by a given primary key, or by a where clause, depending on the type of the $condition. In case $condition is not an array, the primary key is obtained automatically and compared with the given parameter. Otherwise the where clause is used.

A table where the primary key is id will create the following queries :

// SELECT * FROM `current_table` WHERE id=5 LIMIT 1
$row = $this->model->selectRow(5); // OR
$row = $this->model->selectRow( array( 'id' => 5 ) );
// SELECT * FROM `current_table` WHERE id=5 AND otherid=6 LIMIT 1
$row = $this->model->selectRow( array( 'id' => 5, 'otherid' => 5 ) );
// SELECT * FROM `current_table` WHERE id>1 AND otherid=6 LIMIT 1
$row = $this->model->selectRow( array( array( 'id', '>', 1 ), 'otherid' => 6 );

Member Function Documentation

Model (   $db_connection)

Constructor

Parameters
$db_connectionarray : db connection information
setTable (   $table)

Set the default table

Parameters
$tablestring : The table name
getPrimaryKey ( )

Helper, get the primary key of the table

Returns
string
where (   $val,
  $logical_operator = 'and',
  $compare_sign = '=' 
)

Generate a query where clause

Parameters
$valstring/array : array of values, or a string containing the query
$logical_operatorstring : the default operator to be used between comparisons
$compare_signstring : the default sign used for comparisons
Returns
string : the query where clause generated
whereIn (   $value,
  $invalues,
  $not = false 
)

Generate a query where_in clause

Parameters
$valuestring : the value to look for
$invaluesarray : the values to look in
$notboolean : if set to true, the value will not be contained
Returns
string : the query generated as a string
values (   $values = null)

Generate a query string with comma separated values

Parameters
$valuesarray/string
Returns
string : the comma separated values as string
setValues (   $values)

Generate a query string with comma separated values to be set

Parameters
$valuesarray/string : if array, the values must have keys to match upon
Returns
string : the comma separated values
select (   $conditions = "",
  $values = null 
)

Quick select

Parameters
$conditionsstring/array : passed to the 'where' method if given
$valuesstring/array : passed to the 'values' method if given
Returns
resource : MySql resource
selectRow (   $primary_value)

Select a row based on a where clause or a primary key

Parameters
$primary_valuestring/array : if array, it will pe passed to the 'where' method, otherwise it will be matched with the primary key of the table
Returns
object : the row selected
selectRows (   $options = array())

Advanced select

Parameters
array$options: an array with specified keys as options
  • values : array of values that will be selected
  • order_by : key to order by
  • order_direction : asc / desc
  • group_by : key to group by
  • nr_items : number of items the page will be limited to
  • start_from_items : the start value for the items
  • where : array of keys that will be equal to values, using the 'and' separator
  • table : select from a custom table
Returns
resource : MySql resource
insert (   $values)

Inserts given values

Parameters
$valuesstring/array : parsed by the 'setValues' method
updateRow (   $values,
  $primary_value 
)

Update one row by its primary key

Parameters
$valuesarray/string : parsed by the 'setValues' method
$primary_valuestring/numeric : the value of the primary key
updateRows (   $values,
  $where = "" 
)

Update rows

Parameters
$valuesarray/string : parsed by the 'setValues' method
$wherearray/string : parsed by the 'where' method
deleteRow (   $primary_value)

Delete row by its primary key

Parameters
$primary_valuestring/numeric : value of the primary key
deleteRows (   $where = "")

Deletes rows selected by a where clause

Parameters
$wherestring/array : parsed by the 'where' method
countRows (   $options = "")

Count the total rows in a table

Parameters
$optionsarray : parsed by the 'selectRows' method
$optionsstring : added at the end of the query
Returns
numeric : number of rows

Field Documentation

$_primary_key = null

string : Holds the primary key of the table, used by functions that request or change one row. Can be set, or fetched by getPrimaryKey() method.

$table

string : Name of the table the model will be working with