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

Inheritance diagram for Model:

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_connection array : db connection information

setTable ( $table )

Set the default table

Parameters

$table string : 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

$val	string/array : array of values, or a string containing the query
$logical_operator	string : the default operator to be used between comparisons
$compare_sign	string : 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

$value	string : the value to look for
$invalues	array : the values to look in
$not	boolean : 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

$values array/string

Returns: string : the comma separated values as string

setValues ( $values )

Generate a query string with comma separated values to be set

Parameters

$values array/string : if array, the values must have keys to match upon

Returns: string : the comma separated values

select	(	$conditions = `""`,
		$values = `null`
	)

Quick select

Parameters

$conditions	string/array : passed to the 'where' method if given
$values	string/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_value string/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

$values string/array : parsed by the 'setValues' method

updateRow	(	$values,
		$primary_value
	)

Update one row by its primary key

Parameters

$values	array/string : parsed by the 'setValues' method
$primary_value	string/numeric : the value of the primary key

updateRows	(	$values,
		$where = `""`
	)

Update rows

Parameters

$values	array/string : parsed by the 'setValues' method
$where	array/string : parsed by the 'where' method

deleteRow ( $primary_value )

Delete row by its primary key

Parameters

$primary_value string/numeric : value of the primary key

deleteRows ( $where = "" )

Deletes rows selected by a where clause

Parameters

$where string/array : parsed by the 'where' method

countRows ( $options = "" )

Count the total rows in a table

Parameters

$options	array : parsed by the 'selectRows' method
$options	string : 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

Public Member Functions

Data Fields

Additional Inherited Members

Detailed Description

Simple Queries

Selection Methods

Member Function Documentation

Field Documentation