Defining SQL Statements using a String or an Array

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

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

The examples below explain how to define and format custom SQL statements using either a string or an array.

SQL SELECT Statements

Some functions allow a custom SQL SELECT statement to be returned, rather than just returning every column available.

Setting the columns to be returned uses the same formatting available when defining an SELECT statement using CodeIgniters Active Record functions. The data can either be an SQL SELECT statement formatted as a string, or it can be formatted using an array.

Essentially, you can pass data to the 'sql_select' parameters using any of the methods that CodeIgniters SELECT function allows.

Examples

// Example #1: Setting an SQL SELECT statement using a string.

$sql_select = 'column_1, column_2, column_3';

// Example #2: Setting an SQL SELECT statement using an array.

$sql_select = array(
	'column_1', 
	'column_2', 
	'column_3'
);

SQL WHERE Statements

Some functions allow a custom SQL WHERE statement to be defined, rather than either filtering results using the functions default SQL WHERE statement, or by applying no filter at all.

Setting the columns and values to be compared uses the same formatting available when defining a WHERE statement using CodeIgniters Active Record functions. The data can either be an SQL WHERE statement formatted as a string, or it can be formatted using an array.

Essentially, you can pass data to the 'sql_where' parameters using any of the methods that CodeIgniters WHERE function allows.

Examples

// Example #1: Setting an SQL WHERE statement using a string.

$sql_where = 'column_1 = "example_value_1" AND column_2 = "example_value_2" AND column_3 = "example_value_3"';

// Example #2: Setting an SQL WHERE statement using an array.

$sql_where = array(
	'column_1' => "example_value_1",
	'column_2' => "example_value_2", 
	'column_3' => "example_value_3"
);

SQL INSERT Statements

Functions using SQL INSERT queries allow you to define the table columns and values that you want to insert into the database table.

Setting the table columns and values uses the same formatting when defining an INSERT statement using CodeIgniters Active Record functions.
The data must be formatted as an associative array with the array key acting as the table column, and the array value as the table column value.

Examples

// Setting an SQL INSERT statement using an array.

$sql_insert = array(
	'column_1' => "example_value_1",
	'column_2' => "example_value_2", 
	'column_3' => "example_value_3"
);

SQL UPDATE Statements

Functions using SQL UPDATE queries allow you to define the table columns and values that you want to UPDATE into the database table.

Setting the table columns and values uses the same formatting when defining an UPDATE statement using CodeIgniters Active Record functions.
The data must be formatted as an associative array with the array key acting as the table column, and the array value as the table column value.

Examples

// Setting an SQL UPDATE statement using an array.

$sql_update = array(
	'column_1' => "example_value_1",
	'column_2' => "example_value_2", 
	'column_3' => "example_value_3"
);