flexi auth

Functions that Utilise CodeIgniters Active Record Class

Many of the functions in the flexi auth library that perform CRUD operations to the libraries database tables allow for custom SQL statements to be returned or defined when running the query.

The custom SQL statements can be defined for applicable functions using one of two available methods, either via the Query Builder functions listed below, or via submitting an SQL formatted string or array directly to a functions corresponding 'sql_select' or 'sql_where' parameter.

The examples below explain how to set custom SQL statements using flexi auth functions that utilise CodeIgniters Active Record class.

Library Requirements

How it Works

Reference

SQL SELECT

Defines the table columns to be returned by the SQL SELECT statement.

Library Requirements

Function Name and Parameters

Notes

Example

// This example sets the SQL SELECT statement using an array.
// Read the defining SQL documentation for further information on setting SQL statements.

$sql_select = array(
	'example_column_1', 
	'example_column_2'
);

$this->flexi_auth->sql_select($sql_select);

$this->flexi_auth->get_users();

// Produces:
// SELECT `example_column_1`, `example_column_2` 
// FROM (`user_accounts`)

SQL WHERE

Defines the table column and comparision value to be used by the SQL WHERE statement.

Library Requirements

Function Names and Parameters

Notes

Example

// This example sets the SQL WHERE statement using arrays.
// Read the defining SQL documentation for further information on setting SQL statements.

// SQL WHERE
$sql_where = array(
	'example_column_1' => 'example-value-1'
);
$this->flexi_auth->sql_where($sql_where);

// SQL OR WHERE
$sql_or_where = array(
	'example_column_2' => 'example-value-2'
);
$this->flexi_auth->sql_or_where($sql_or_where);

$this->flexi_auth->get_users();

// Produces:
// SELECT * 
// FROM (`user_accounts`) 
// WHERE `example_column_1` = 'example-value-1' 
// 	OR `example_column_2` = 'example-value-2'

SQL WHERE IN

Defines the table column and comparision values to be used by the SQL WHERE IN statement.

Library Requirements

Function Names and Parameters

Notes

Example

// This example sets the SQL WHERE statement using strings and arrays.

// SQL WHERE IN (Set as an array)
$sql_where_in = array(
	'example-value-1a'
);
$this->flexi_auth->sql_where_in('example_column_1', $sql_where_in);

// SQL OR WHERE IN (Set as an array)
$sql_or_where_in = array(
	'example-value-2a', 
	'example-value-2b', 
	'example-value-2c'
);
$this->flexi_auth->sql_or_where_in('example_column_2', $sql_or_where_in);

// SQL WHERE NOT IN (Set as a string)
$sql_where_not_in = 'example-value-3a';
$this->flexi_auth->sql_where_not_in('example_column_3', $sql_where_not_in);

// SQL OR WHERE NOT IN (Set as a string)
$sql_or_where_not_in = 'example-value-4a, example-value-4b';
$this->flexi_auth->sql_or_where_not_in('example_column_4', $sql_or_where_not_in);

$this->flexi_auth->get_users();

// Produces:
// SELECT *
// FROM (`user_accounts`)
// WHERE `example_column_1` IN ('example-value-1a') 
// 	OR `example_column_2` IN ('example-value-2a', 'example-value-2b', 'example-value-2c') 
// 	AND `example_column_3` NOT IN ('example-value-3a') 
// 	OR `example_column_4` NOT IN ('example-value-4a, example-value-4b') 

SQL WHERE LIKE

Defines the table column and comparision values to be used by the SQL WHERE LIKE statement.

Library Requirements

Function Names and Parameters

Notes

Example

// SQL LIKE with the wildcard positioned on both sides.
$this->flexi_auth->sql_like('example_column_1', 'example-value-1');

// SQL OR LIKE with the wildcard positioned at the start.
$this->flexi_auth->sql_or_like('example_column_2', 'example-value-2', 'before');

// SQL NOT LIKE the wildcard positioned at the end
$this->flexi_auth->sql_not_like('example_column_3', 'example-value-3', 'after');

// SQL OR NOT LIKE with the wildcard positioned on both sides
$this->flexi_auth->sql_or_not_like('example_column_4', 'example-value-4', 'both');

$this->flexi_auth->get_users();

// Produces:
// SELECT *
// FROM (`user_accounts`)
// WHERE  `example_column_1`  LIKE '%example-value-1%'
// 	OR  `example_column_2`  LIKE '%example-value-2'
// 	AND  `example_column_3` NOT LIKE 'example-value-3%'
// 	OR  `example_column_4` NOT LIKE '%example-value-4%'

SQL JOIN

Defines the table name and join relationship between the to-be-joined tables used by the SQL JOIN statement.

Library Requirements

Function Name and Parameters

Notes

Example

// SQL JOIN table name.
$sql_join = 'example_table_2';

// SQL JOIN condition.
$sql_join_on = 'example_table_1.example_column_1 = example_table_2.example_column_1';

$this->flexi_auth->sql_join($sql_join, $sql_join_on);

$this->flexi_auth->get_db_table_data('example_table_1');

// Produces:
// SELECT *
// FROM (`example_table_1`)
// 	JOIN `example_table_2` ON `example_table_1`.`example_column_1` = `example_table_2`.`example_column_1`

SQL ORDER BY

Defines the table column and sort direction used by the SQL ORDER BY statement.

Library Requirements

Function Name and Parameters

Notes

Example

$this->flexi_auth->sql_order_by('example_column_1', 'DESC');

$this->flexi_auth->get_users();

// Produces:
// SELECT *
// FROM (`user_accounts`)
// ORDER BY `example_column_1` DESC

SQL GROUP BY

Defines the table column used by the SQL GROUP BY statement.

Library Requirements

Function Name and Parameters

Notes

Example

$this->flexi_auth->sql_group_by('example_column_1');

$this->flexi_auth->get_users();

// Produces:
// SELECT *
// FROM (`user_accounts`)
// GROUP BY `example_column_1`

SQL LIMIT

Defines the limit value and offset value used by the SQL LIMIT statement.

Library Requirements

Function Name and Parameters

Notes

Example

$this->flexi_auth->sql_limit(10, 2);

$this->flexi_auth->get_users();

// Produces:
// SELECT *
// FROM (`user_accounts`)
// LIMIT 2, 10

Clear SQL statements

Clears any custom user defined SQL Active Record functions.

Function Names

Library Requirements

Example

$this->flexi_auth->sql_clear();