# Data Selection

# where ( fields, value )

Sets the condition of choice.

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

# Option 1:

Accept the input field and value. The field should be indicated by a sign of comparison. Value will be automatically quoted.

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

# Option 2

Accepts associations are an array of field-value. The field should be indicated by a sign of comparison. Value will be automatically quoted.

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

PLEASE NOTE

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

# Option 3

Accepts custom line environment, you must specify the name of the table in front of each field, to avoid any conflict in relationships with other tables. You also have to take care of yourself on preparing 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.

# Alternative usage

// using OR glue
$xcrud->where('catid', 5);
$xcrud->or_where('created >', $last_visit);
 
// using IN and NOT IN
$xcrud->where('catid', array('5','7','8'); // `catid` IN ('5','7','8')
$xcrud->where('catid !', array('5','7','8'); // `catid` NOT IN ('5','7','8')

# or_where()

The same as where() method, but multiple calls will be united with operator 'OR'

# no_quotes( fields )

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.

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

# relation ( field, target_table,target_id, target_name, where, order_by, multi, concat_separator, tree, depend_field, depend_on )

Binds the current table with a list of the other table. Takes as input the name of the field in the current table, the name of the linked table, the link field, the title field , the selection condition for the linked table (optional).

$xcrud->relation('catid','categories','cid','category_name',array('published' => 1));
//or
$xcrud->relation('catid','categories','cid','category_name','categories.published = 1');

# relation ( field, target_table, target_id, target_name, where_array, main_table, multi, concat_separator, tree, depend_field, depend_on )

Since 1.5.4 relation() has additional parameters:

Parameters:

  • field - main table relation field, that will be replases; data will be written in this field
  • target_table - related (target) table, options for dropdown will be get from here
  • target_id - row id from target table, will be writen into field
  • target_name - field, that will be displayed as name of dropdown option. This can be array of few fields.
  • where - (optional) - allows to specify selection items from the target_table, see where(). Default is null.
  • order_by - (optional) - order by condition (eg. 'username desc'). Default is by target_name.
  • multi - (optional, boolean) - can change dropdown to multiselect (items will be saved separated by comma). Default is false.
  • concat_separator - (optional) - take effect only when target_name is array. Default is ' '.
  • tree - (optional) - 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 - field from current table, options will be extracted based on parent field value ( like country_id column in cities table)
  • depend_on - field, thats will be parent to current dropdown.

Info

You can use {field_tags} in 'where' parameter to get variable from current row

# fk_relation()

Allows to create, manage and display many-to-many connections. The syntax is similar to relation().

Parameters:

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

# Structure of connections

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

# nested_table ( inst_name, connect_field, nested_table, nested_connect_field )

Takes instance name in first parameter, main field in second parameter, nested table in third and connection field from nested table in 4th.

Nested tables are using for easy editing of related records in other tables, such as the order and the goods in the order (see demo).

You can specify one nested table for each field of your main table. You can set options for nested tables as well as you do for the main table. Method nested_table () creates an instance of a nested table, access to which can be obtained through the field name (specified in the first parameter of the method nested_table ()), only add to it the prefix nested_.

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

# search_pattern( '%', '%' )

Allows to define search pattern for grid search. Method replaces default param ($search_pattern) from configuration.

# Table Joining

xCRUD provide table joining and manipulating with a few tables in one box. You can use extended field syntax, when you type field name (column name or alias) as table and field with dot separator (example: $xcrud->change_type('order.total', 'price');). You no need to use this when you not use table joining.

# join()

Parameters:

  • field - field from current table to join.
  • joined_table - table, that is joined.
  • join_on_field - joining on this column from joined table.
  • alias - (optional) - need to use when you join one tabes more than one times.
  • not_insert - (optional) - allows to disable inserting and deleting rows from joined table.
$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','profile.city','tokens.created');

INFO

join() uses INNER JOIN, thats mean in all tables must be present joined rows. join() connects only one row from table, joined on fields must be unique. When joined rows in not unique xCRUD will be worked incorrect.

# Custom SQL

# query()

This method allow you to use custom sql query and display read-only datagrid.

Parameters:

sql_query - custom sql query

$xcrud = Xcrud::get_instance();
$xcrud->query('SELECT * FROM users WHERE age > 25');
echo $xcrud->render();
Last Updated: 3/11/2021, 5:43:48 PM