# 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 spaces.
$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 '' connection table
$in_fk_field string '' field, connected with main table
$out_fk_field string '' 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();
Last Updated: 5/5/2021, 4:29:34 PM