User Guide | Miscellaneous Admin Functions

Admin functions are available from the flexi cart admin library and are primarily used to manage data within flexi carts database tables.

Many of the functions perform CRUD functionality returning SELECT queries and running INSERT, UPDATE and DELETE statements.
The CRUD functions are automatically joined to other related tables and allow custom statements to be run, with minimal configuration required.

Miscellaneous Admin Functions

Customising SQL Statements Index | Querying SQL Results | Defining Custom SQL Statements | Building Custom SQL Queries

Help with Admin Functions

Show / Hide Help

Name: The name of the function (method).

Data Type: The data type that is expected by the function.

  • bool : Requires a boolean value of 'TRUE' or 'FALSE'.
  • string : Requires a textual value.
  • int : Requires a numeric value. It does not matter whether the value is an integer, float, decimal etc.
  • array : Requires an array.

Required: Defines whether the parameter requires a value to be submitted.

Default: Defines the default parameter value that is used if no other value is submitted.

get_db_table_data()

Gets records from a custom user defined table.


Library and Requirements

Available via the admin library only.

Function Parameters
get_db_table_data(table_name, sql_select, sql_where) Help
Name Data Type Required Default Description
table_name string Yes FALSE Define the database table to apply the SQL SELECT statement to.
sql_select string | array No FALSE Define the database fields returned via an SQL SELECT statement.
Read the defining SQL documentation for further information.
sql_where string | int | array No FALSE Set the SQL WHERE statement used to filter the database records to return.
Read the defining SQL documentation for further information.
How it Works

The function runs an SQL SELECT statement on the defined 'table_name' table.

The query can be customised by submitting 'sql_select' and 'sql_where' data to the functions parameters.

Notes

This function is compatible with flexi carts 'Query Builder' functions.


This function can be chained with CodeIgniters query functions 'result()', 'row()' etc.

Read the Query Result documentation for further information on all the combined flexi cart and CodeIgniter functions that are available.

Return Values

Failure:FALSE | An error message will be set if a required table/feature is disabled.

Success:object

Example
$table_name = 'example_name';

// Read the defining SQL documentation for further information on setting SQL statements.
$sql_select = array(...);
$sql_where = array(...);

// Example of chaining CI's query function 'result()'.
// Read the Query Result documentation for further information on available functions.
$this->flexi_cart_admin->get_db_table_data($table_name, $sql_select, $sql_where)->result();

insert_db_table_data()

Inserts a new record into a custom user defined table.


Library and Requirements

Available via the admin library only.

Function Parameters
insert_db_table_data(table_name, sql_insert) Help
Name Data Type Required Default Description
table_name string Yes FALSE Define the database table to apply the SQL INSERT statement to.
sql_insert array Yes FALSE Set the SQL INSERT statement used to insert data into the database.
Read the defining SQL documentation for further information.
How it Works

The function runs an SQL INSERT statement on the defined 'table_name' table, inserting data defined via the 'sql_insert' parameter.

Return Values

Failure:FALSE | An error message will be set if a required table/feature is disabled.

Success:int | id of the inserted record.

Example
$table_name = 'example_name';

// Read the defining SQL documentation for further information on setting SQL statements.
$sql_insert = array(...);

$this->flexi_cart_admin->insert_db_table_data($table_name, $sql_insert);

update_db_table_data()

Updates records in a custom user defined table.


Library and Requirements

Available via the admin library only.

Function Parameters
update_db_table_data(table_name, sql_update, sql_where) Help
Name Data Type Required Default Description
table_name string Yes FALSE Define the database table to apply the SQL UPDATE statement to.
sql_update array Yes FALSE Set the SQL UPDATE statement used to update data into the database.
Read the defining SQL documentation for further information.
sql_where string | int | array No FALSE Set the SQL WHERE statement used to filter the database records to update.
Read the defining SQL documentation for further information.
How it Works

The function runs an SQL UPDATE statement on the defined 'table_name' table, updating data defined via the 'sql_update' parameter and filtered via the 'sql_where' parameter.

Notes

This function is compatible with flexi carts 'Query Builder' functions.

Return Values

Failure:FALSE | An error message will be set if a required table/feature is disabled.

Success:int | The number of affected rows.

Example
$table_name = 'example_name';

// Read the defining SQL documentation for further information on setting SQL statements.
$sql_update = array(...);
$sql_where = array(...);

$this->flexi_cart_admin->update_db_table_data($table_name, $sql_update, $sql_where);

delete_db_table_data()

Deletes records from a custom user defined table.


Library and Requirements

Available via the admin library only.

Function Parameters
delete_db_table_data(table_name, sql_where) Help
Name Data Type Required Default Description
table_name string Yes FALSE Define the database table to apply the SQL DELETE statement to.
sql_where string | int | array Yes FALSE Set the SQL WHERE statement used to filter the database records to delete.
Read the defining SQL documentation for further information.
How it Works

The function runs an SQL DELETE statement on the defined 'table_name' table filtered via the 'sql_where' parameter.

Notes

This function is compatible with flexi carts 'Query Builder' functions.

Return Values

Failure:FALSE | An error message will be set if a required table/feature is disabled.

Success:int | The number of affected rows.

Example
$table_name = 'example_name';

// Read the defining SQL documentation for further information on setting SQL statements.
$sql_where = array(...);

$this->flexi_cart_admin->delete_db_table_data($table_name, $sql_where);

create_sql_where()

Generates a formatted SQL WHERE statement using CodeIgniters Active Record functions.


Function Parameters
create_sql_where(column_name, comparison_operator, value, logic_operator) Help
Name Data Type Required Default Description
column_name string Yes FALSE Defines the name of the column that is be compared against the 'value' parameter.
comparison_operator string Yes FALSE Defines the operator ('=', '!=' etc.) used to compare the 'column_name' against the 'value' parameters.
See the table below for further information on available operators.
value string | int No NULL Defines the value that is be compared against table column in the 'column_name' parameter.
logic_operator string No 'AND' Defines the operator used to relate each SQL WHERE statement to each other.
The available values are either 'AND' or 'OR'.
Comparison Operators

Comparison operators can either be defined using the numeric id or the string id.

Description Numeric id String id SQL Generated
Is equal to 1 = 'column_name' = 'value'
Is not equal to 2 != 'column_name' != 'value'
Is less than 3 < 'column_name' < 'value'
Is less than or equal to 4 <= 'column_name' <= 'value'
Is more than 5 > 'column_name' > 'value'
Is more than or equal to 6 >= 'column_name' >= 'value'
Contains 7 like 'column_name' LIKE '%value%'
Does not contain 8 not_like 'column_name' NOT LIKE '%value%'
Begins with 9 begin_like 'column_name' LIKE 'value%'
Does not begin with 10 not_begin_like 'column_name' NOT LIKE 'value%'
Ends with 11 end_like 'column_name' LIKE '%value'
Does not end with 12 not_end_like 'column_name' NOT LIKE '%value'
Is null 13 null 'column_name' IS NULL
Is not null 14 not_null 'column_name' IS NOT NULL
Is empty 15 empty 'column_name' = ''
Is not empty 16 not_empty 'column_name' != ''
Is between 17 between 'column_name' BETWEEN 'value_1' AND 'value_2'
Is not between 18 not_between 'column_name' NOT BETWEEN 'value_1' AND 'value_2'
Is in list 19 in 'column_name' IN ('value_1', 'value_2', 'value_3')
Is not in list 20 not_in 'column_name' NOT IN ('value_1', 'value_2', 'value_3')
How it Works

The primary purpose of this function is to be used in a loop, where the function will automatically generate a chain of SQL WHERE statements using the data that is submitted to it.

This function is used in the discount group demos, where it filters records from a user defined item (products) table, the items returned by the WHERE statement are then added to a discount group that discounts can be applied to.


The function works by calling one of CodeIgniters Active Record SQL WHERE functions, defined via the 'comparison_operator' parameter. This operator is then used to compare 'column_name' against the 'value'.

When multiple SQL WHERE statements are generated via a loop, the 'logic_operator' defines how to relate each statement to each other, e.g. via 'AND', 'OR' etc.

When CodeIgniter then calls an SQL query, the generated SQL WHERE statements are applied to the query.

Notes

To include multiple values for comparison operators as such as 'BETWEEN' and 'IN', separate values with a comma.
The 'BETWEEN' operators will only use the first 2 comma separated values, whilst the 'IN' operators will use all comma separated values.

Comparison operators can be defined using either the numeric id or the string id, see the examples below for further information.

Return Values

Failure:FALSE

Success:TRUE

Example
// Example #1 : Defining the SQL WHERE statement using a loop.

// Structure example data as a multi-dimensional array.
$example_data = array(
array(
	'column_name' => 'example_column_name_1',
	'comparison_operator' => '2', // Defining the numeric id for 'Is not equal to'.
	'value' => 'example_value',
	'logic_operator' => 'AND'
),
array(
	'column_name' => 'example_column_name_2',
	'comparison_operator' => 'between', // Defining the string id for 'Is between'.
	'value' => '10,20', // Separate 'BETWEEN' values with a comma.
	'logic_operator' => 'AND'
),
array(
	'column_name' => 'example_column_name_3',
	'comparison_operator' => 'not_in', // Defining the string id for 'Is not in list'.
	'value' => '5,10,15,20', // Separate 'IN' values with a comma.
	'logic_operator' => 'OR'
)
);

// Loop through example data.
foreach($example_data as $loop_data)
{
$this->flexi_cart_admin->create_sql_where(
	$loop_data['column_name'], 
	$loop_data['comparison_operator'], 
	$loop_data['value'], 
	$loop_data['logic_operator']
);
}

// Generate an SQL query using CodeIgniters database class.
$this->db->get('example_table');

// Produces:
// SELECT *
// FROM (`example_table`)
// WHERE `example_column_1` !=  'example_value'
// 	AND `example_column_2` BETWEEN '10' AND '20'
// 	OR `example_column_3` NOT IN ('5', '10', '15', '20')
// Example #2 : Defining the SQL WHERE statement without using a loop.

$column_name = 'example_column';
$comparison_operator = 'IN'; // Defining the string id for 'Is in list'.
$value = '1, 2, 3, 4'; // Separate 'IN' values with a comma.
$logic_operator = 'AND';

$this->flexi_cart_admin->create_sql_where($column_name, $comparison_operator, $value, $logic_operator);

// Produces:
// SELECT *
// FROM (`example_table`)
// WHERE `example_column` IN ('1', '2', '3', '4')