Data Selection

where()

Sets the condition of choice.

Multiple calls are united all the conditions with the operator 'AND'.

Parameter	Type	Default	Description
$fields	string	false	Left-hand side and operator of an SQL expression e.g 'tbl.field = ' in tbl.field = 5
$where_val	mixed	false	Right-hand side of an SQL expression e.g 5 in tbl.field = 5.
			Values will automatically get escaped.
$glue	string	'AND'	SQL operator binding the WHERE clause expressions.
$index	string	false

Option 1:

Accept the input field and value. The field should be appended with an operator. Value will be automatically get escaped.

$last_visit = '2018-04-05';

$xcrud->where('catid =', 5);
$xcrud->where('created >', $last_visit);
// or
$xcrud->where('catid =', 5)->where('created >', $last_visit);

//output
//catid = 5 AND created > '2018-04-05'

Option 2

Accepts an associated array. The array field should be the table field appended with an SQL operator. Value will be automatically get escaped.

$cond = array('catid =' => 5, 'created >' => $last_visit);
$xcrud->where($cond);

//output
//catid = 5 AND created > '2018-04-05'

NOTE

All the conditions in the array will be merged with the operator 'AND'.

Option 3

Accepts custom line environment. To avoid conflicts in relationships with other tables, qualify each field i.e tablename.fieldname. You also have to take care of properly escaping values.

$xcrud->where("content.catid = 5 AND content.created > '{$last_visit}'");
// or
$xcrud->where("", "content.catid = 5 AND content.created > '{$last_visit}'"); // 1.5 compat.

//output
//content.catid = 5 AND content.created > '2018-04-05'

Alternative usage

// using OR 
$xcrud->where('catid =', 5);
$xcrud->where('created >', $last_visit, 'OR'); 
//output
//catid = 5 OR created > '2018-04-05'
 
// using IN 
$xcrud->where('catid', array(5,7,8)); // `catid` IN (5,7,8)

// using NOT IN
$xcrud->where('catid !', array(5,7,8)); // `catid` NOT IN (5,7,8)

or_where()

The same as where($fields, $where_val, 'OR'). Multiple calls will be bound with the 'OR' operator.

Parameter	Type	Default	Description
$fields	string	''	Left-hand side and operator of an SQL expression e.g 'tbl.field = ' in tbl.field = 5
$where_val	mixed	false	Right-hand side of an SQL expression e.g 5 in tbl.field = 5.
			Values will automatically get escaped.

no_quotes()

The method allows to cancel the automatic shielding values, so you can use the functions in mysql query. Affects on where expressions and pass_var () method. Takes comma-separated fieldnames or array of fieldnames in first parameter.

Parameter	Type	Default	Description
$fields	string	''

$xcrud->no_quotes('created');
$xcrud->pass_var('created','now()');
//or
$xcrud->where('created !=','null');

relation()

Set a 1-n database relation between the main table and $rel_tbl respectively. Hence the field $rel_tbl.$rel_name:

• is displayed as dropdown list in the 'create' and 'edit' screens.
• and replaces the main table's $fields.

Parameter	Type	Default	Description
$fields	string	''	Main table's relation field. It's value gets replaced by $rel_name.
$rel_tbl	string	''	Related, target or n-table name. Dropdown options will come from this table.
$rel_field	string	''	n-table relation field to the main table i.e. main_table.$fields = $rel_tbl.$rel_field
$rel_name	mixed	''	n-table field that will be displayed in place of main_table.$fields and in the dropdown options.
			This can be the field name or an array of field names. These will automatically be concatenated with $rel_concat_separator (defaults to a space).
$rel_where	array	array()	An associated array allowing to specify dropdown options from the n-table, see where().
$order_by	string	false	Order by condition (eg. 'username desc'). Defaults to $rel_name ASC.
$multi	boolean	false	Change dropdown to multiselect. Options will be saved separated by comma.
$rel_concat_separator	string	' '	Used to concatenate $rel_name when provided as an array.
$tree	array	false	Array, sets tree rendering of dropdown list.
			Options: 1. array('primary_key' => 'some_id_field_name', 'parent_key' => 'some_field_name') - primary and parent key field name, will be created pk tree.
			Options: 2. array('left_key' => 'some_field_name', 'level_key' => 'some_field_name') - left key and level field names, will be created nested sets tree.
$depend_field	string	''	Field from current table, options will be extracted based on parent field value ( like country_id column in cities table).
$depend_on	string	''	Field, thats will be parent to current dropdown.

$xcrud->table('payments'); //this table

// simple relation
// customerName will be displayed instead of the customerNumber, 
// customerNames will be displayed in a dropdown during create/edit
$xcrud->relation('customerNumber','customers','customerNumber', 
	'customerName');

// $rel_where - limit customers in dropdown
$xcrud->relation('customerNumber','customers','customerNumber', 
	'customerName', array('customerNumber <' => 129));
// or
$xcrud->relation('customerNumber','customers','customerNumber', 
	'customerName', 'customerNumber < 129');

$xcrud->relation('customerNumber','customers','customerNumber', 
	'customerName', "customerNumber in (131,141,171)");
$xcrud->relation('customerNumber','customers','customerNumber', 
	'customerName', "customerName LIKE '%ee%'");

fk_relation()

Create an n-n database relation. Create, manage and display many-to-many connections. The syntax is similar to relation().

Parameter	Type	Default	Description
$label	string	''	Displaying field label (must be unique, used as alias)
$fields	string	''	connection field from current table
$fk_table	string	''	n-n table.
$in_fk_field	string	''	n-n table field, connected with main table
$out_fk_field	string	''	n-n table field, connected with relation table
$rel_tbl	string	''	relation table
$rel_field	string	''	connection field from relation table
$rel_name	string	''	field, that will be displayed as name of dropdown option. This can be array of few fields.
$rel_where	string	array()	allows to specify selection items from the target_table, see where(). Default is null.
$rel_orderby	string	''	order by condition (eg. 'username desc'). Default is by rel_name.
$rel_concat_separator	string	' '	take effect only when rel_name is array. Default is ' '.
$before	string	''	if selected, field will be inserted before this field (by default - in the end)
$add_data	string	array()	additional inserting data

Structure of n-n connection

table
|- field  --|
            |
            |    fk_table
            |--  |- in_fk_field
                 |- out_fk_field  --|
                                    |
                                    |    rel_table
                                    |--  |- rel_field

nested_table()

This allows you to display drill down tables off the main table. The nested table(s) are displayed one after the other after the main table in the view and edit screens.

Parameter	Type	Default	Description
$instance_name	string	''	Assign an identifier to the xCRUD instance being created.
$field	string	''	The main table field related to the pivot table's $tbl_field.
$inner_tbl	string	''	The nested/pivot table.
$tbl_field	string	''	The nested/pivot table field related to $field in the main table.

TIP

The code samples are using the xCRUD demo database, included with your purchase.

$xcrud->table('orders'); // main table
$xcrud->nested_table('products_list','orderNumber','orderdetails', // nested table
	'orderNumber'); 

Each nested_table() call requires a unique $instance_name, as it creates a unique xCRUD instance. This may be assigned to a variable or invoked via get_instance()

$xcrud->table('orders'); // main table
$products_list = $xcrud->nested_table('products_list','orderNumber','orderdetails', 
	'orderNumber'); 

//OR
$xcrud->table('orders'); // main table
$xcrud->nested_table('products_list','orderNumber','orderdetails','orderNumber');
// nested table instance access
$products_list = $xcrud = Xcrud::get_instance('products_list'); 

The nested tables will display the create, view, edit and delete action buttons by default. You can set options for nested tables as well as you do for the main table.

$xcrud->table('orders');
$products_list = $xcrud->nested_table('products_list','orderNumber','orderdetails',
	'orderNumber'); // nested table
$products_list->unset_add(); // nested table instance access

Calling nested_table() on a previous nested_table() call, makes the previous nested_table() call's $nested_table the main table. Below is a rather ridiculous, inverse pivot demonstrating this.

$xcrud->table('orders'); //main table

// show customers of  this order
$order_customer = $xcrud->nested_table('order_customer','customerNumber','customers',
	'customerNumber'); 
$order_customer->columns('customerName');

// main table for this nesting/pivot is customers
// show all other orders by the customer
$customers_orders_fk = $order_customer->nested_table('customers_orders_fk',
	'customerNumber','customers_orders_fk','customer_id'); 
$customers_orders_fk->columns('order_id');

// main table for this nesting/pivot is customers_orders_fk
// and now show the other orders' detail
$customers_orders_fk->nested_table('customers_other_orders',
	'order_id','orders','orderNumber'); 

search_pattern()

Defines the search pattern for list view search. Method overrides the parameter $search_pattern from the xcrud_config.php file.

Parameter	Type	Default	Description
$left	string	'%'	Appended to the left of the search term.
$right	string	'%'	Appended to the right of the search term.

Table Joining

join()

Parameter	Type	Default	Description
$fields	string	''	Main table field to join on. Can be supplied as a fully qualified field name e.g users.age.
$join_tbl	string	''	Table to join to.
$join_field	string	''	Field in $join_tbl to join to the main table $fields. Can be supplied as a fully qualified field name e.g users.age.
$alias	mixed	false	Alias for the table to join to. You may reference the table using it's full name by default.
$not_insert	mixed	false	Disable create and delete actions on joined table(s). These actions are still possible for the main table.
			NOT to be confused with unset_add() OR unset_remove(). It does NOT disable the create and delete action buttons.

$xcrud->table('users');

// join users and profiles on users.id = profiles.user_id
$xcrud->join('id','profiles','user_id'); 
 
// now join 'profiles' and 'tokens' tables
$xcrud->join('profiles.token_id','tokens','id'); // on profile.token_id = tokens.id
 
// simple actions with fields: default and joined
$xcrud->column('username','email','profiles.city','tokens.created');

Multiple joins on the same table and disabling create and delete operations on joined table.

$xcrud->table('orderdetails'); 
// join products table and disable create and deletion from products
$xcrud->join('productCode', 'products', 'productCode', FALSE, TRUE);

$xcrud->table('orderdetails'); 
// join products table
$xcrud->join('productCode', 'products', 'productCode', FALSE, TRUE);
// join products table again with an alias
// NOTE: create and deletion from products 
// IS POSSIBLE as $not_insert == FALSE
$xcrud->join('productCode', 'products', 'productCode', 'p');
// use fully qualified names to fetch columns
$xcrud->columns('orderdetails.orderNumber, products.productCode, 
orderdetails.orderLineNumber, products.productScale');

INFO

join() uses INNER JOIN, that is, 1-1 row relation; each row in the first table is joined to the equivalent row in the second table based on the unique join condition.

Custom SQL

query()

This method allow you to display read-only results table of a regular SQL query.

Parameter	Type	Default	Description
$query	string	''	SQL query.

$xcrud = Xcrud::get_instance();
$xcrud->query('SELECT * FROM users WHERE age > 25');
echo $xcrud->render();