Original Source
https://pdocrud.com/demo/pages/join
https://pdocrud.com/demo/pages/inner-join-with-columns
https://pdocrud.com/demo/pages/related-data
https://pdocrud.com/demo/pages/multi-table-relation
https://pdocrud.com/demo/pages/multi-table-relation-tabs
https://pdocrud.com/demo/pages/one-page-template
https://pdocrud.com/demo/pages/table-col-add-col
https://pdocrud.com/demo/pages/field-order
https://pdocrud.com/demo/pages/quick-table-view
Left Join
PDOCrud supports both left join and inner join operation. If you want to apply master, detail table operation where master table contains single record and details table contains multiple records, use LEFT JOIN else INNER JOIN.
$pdocrud = new PDOCrud();
$pdocrud->crudTableCol(array("first_name","last_name","user_name","gender"));
/**
* Add join between tables, supported join condition are "INNER JOIN" & "LEFT JOIN"
* @param string $joinTableName name of table to be joined
* @param string $joinCondition join condition e.g. ("table2.col = table1.col")
* @param string $joinType type of join (Inner or left join)-default is inner join
* return object Object of class
*/
$pdocrud->joinTable("user_meta", "user_meta.user_id = users.user_id", "LEFT JOIN");
echo $pdocrud->dbTable("users")->render();
Inner Join
SQL INNER JOIN returns all rows from tables where the key record of one table is equal to the key records of another table. You can easily perform the INNER JOIN operation using pdcrud, all you need to do is specify the join table name and the join condition between two tables. Below examples shows inner join between user table and usermeta table that has matching column 'user_id'. It will select all records having matching user_id from both of these tables. During select/insert/update operation, data from both tables will be shown for inner join operation.
$pdocrud = new PDOCrud();
/**
* Add join between tables, supported join condition are "INNER JOIN" & "LEFT JOIN"
* @param string $joinTableName name of table to be joined
* @param string $joinCondition join condition e.g.
* @param string $joinType type of join (Inner or left join)-default is inner join
* return object Object of class
*/
$pdocrud->colRename("meta_key", "Meta Key Info");
$pdocrud->joinTable("user_meta", "users.user_id = user_meta.user_id", "INNER JOIN");
$pdocrud->crudTableCol(array("first_name","last_name","meta_key"));
echo $pdocrud->dbTable("users")->render();
Related Data
Display related column value based on relation
When your main table contains the fields related to other tables then you can use the related data function to easily get data from other tables and also display as list in the forms. For example, if you want to display crud for "student" table containing class_id which is related to "class" table (columns class_id, class_name ) then you can easily display class_name instead of class_id using the related data. Also, it converts the field class_id to dropdown with display field text as "class_name" and value saved will be "class_id".
In previous version, this can be done using join between main table and other tables and defining the field type and binding that field using the fielddatabinding function. Now this can be done using a single line.
Please note that it works on the main table field related to the other tables, it doesn't work on joined table. Also, currently it is supported by Mysql only. We will add for other database also in future versions.
$pdocrud = new PDOCrud();
$pdocrud->crudTableCol(array("student_id","register_number","first_name","last_name","class_id"));//optional
/**
* Get related column data as list from other tables
* @param string $mainTableCol Column name of the main table
* @param string $relTable Related table name
* @param mixed $relTableCol Matching related table columns
* @param mixed $relDisplayCol Related table column to be display
* @param array $where where condition array
* @param array $orderby Order by condition array
* @param mixed $fieldType Field type to be displayed for that field, default is "select", if empty, then textarea will be shown
* return object Object of class
*/
$pdocrud->relatedData('class_id','class','class_id','class_name');
/complete example of the related data function
// Please note that this will be applied in the form fields not in the grid
// $pdocrud->relatedData('class_id','class','class_id','class_name',array(array("class_id", 1,">=")), array("class_name"));
echo $pdocrud->dbTable("student")->render();
echo $pdocrud->dbTable("users")->render();
Multi Table Relation (Nested Tables)
Editing of related records in other table (Added in v 2.0)
When your main table contains the fields related to other tables then you can use the related data function to easily get data from other tables and also display as list in the forms. For example, if you want to display crud for "student" table containing class_id which is related to "class" table (columns class_id, class_name ) then you can easily display class_name instead of class_id using the related data. Also, it converts the field class_id to dropdown with display field text as "class_name" and value saved will be "class_id".
In previous version, this can be done using join between main table and other tables and defining the field type and binding that field using the fielddatabinding function. Now this can be done using a single line.
Please note that it works on the main table field related to the other tables, it doesn't work on joined table. Also, currently it is supported by Mysql only. We will add for other database also in future versions.
//class table object
$pClass = new PDOCrud();
//section of class
$pSection = new PDOCrud(true);
$pSection->crudTableCol(array("name", "start_time", "end_time"));
$pSection->dbTable("section");
//first paramater is first table(object) columnn name and 2nd parameter is 2nd object column name
$pClass->multiTableRelation("class_id", "class_id", $pSection);
//next level
//student's class object
$pStudent = new PDOCrud(true);
$pStudent->crudTableCol(array("first_name", "last_name", "gender"));
$pStudent->formFields(array("first_name", "last_name", "gender"));
$pStudent->dbTable("student");
//first paramater is first table(object) columnn name and 2nd parameter is 2nd object column name
$pSection->multiTableRelation("section_id", "section_id", $pStudent);
echo $pClass->dbTable("class")->render();
Multi Table Relation In Tabs - Nested Tables
Editing of related records in other table (Added in v 2.0)
PDOCrud allows the editing of related records of different table i.e. similar to nested table. Nested Table is a table inside a table. It is not stored in that way but similar concept is used. You can define relation between the two tables data using some binding column value. .
//student table object
$pStudent = new PDOCrud();
$pStudent->multiTableRelationDisplay("tab", "Student");
//student's class object
$pStudentClass = new PDOCrud(true);
$pStudentClass->crudTableCol(array("class_id", "class_name", "code"));
$pStudentClass->dbTable("class");
//first paramater is first table(object) columnn name and 2nd parameter is 2nd object column name
$pStudent->multiTableRelation("class_id", "class_id", $pStudentClass);
$pStudentClass->multiTableRelationDisplay("tab", "Class");
//student's class object - adding one more table
$pStudentTransport = new PDOCrud(true);
$pStudentTransport->dbTable("vehicle");
//first paramater is first table(object) columnn name and 2nd parameter is 2nd object column name
$pStudent->multiTableRelation("transport_id", "vehicle_id", $pStudentTransport);
$pStudentTransport->multiTableRelationDisplay("tab", "Vehicle");
//2nd level of nesting - relating section to class(first student to class and then class to section)
dentSection = new PDOCrud(true);
entSection->dbTable("section");
st paramater is first table(object) columnn name and 2nd parameter is 2nd object column name
entClass->multiTableRelation("class_id", "class_id", $pStudentSection);
dentSection->multiTableRelationDisplay("tab", "Section");
ent->crudTableCol(array("student_i"first_name","last_name"));//
One Page Template
You can also display crud and form on a single page.
$pdocrud = new PDOCrud();
echo $pdocrud->dbTable("orders")->render("ONEPAGE");
Add New Column dynamically
You can add new table column dynamically based on two columns value for example you want to show order total based on order sub total and tax. You can also merge two columns to generate a new column for example you can merge first name and last name to generate new column "Full Name". By default, new column is added at the last. Since it is dynamic column, so it doesn't support search/sort/export function.
$pdocrud = new PDOCrud();
$pdocrud->crudTableCol(array("product_name","product_id", "product_price","product_sell_price","tax","product_discount"));
//Add new column total which is calculated as sum of two columns (you can pass any no. of columns)
$pdocrud->tableColAddition("Total", "sum",array("product_sell_price","tax"));
//Add new colum Profit which is calculated as subtraction of two columns
$pdocrud->tableColAddition("Profit", "subtract",array("product_price","product_sell_price"));
//Add new colum Sell/price which is calculated as division of two columns
$pdocrud->tableColAddition("Sell/price", "divide",array("product_sell_price","product_price"));
//Merge column to generate a new column ( you can pass any no. of columns)
$pdocrud->tableColAddition("Product Info", "merge",array("product_id","product_name"));
$pdocrud->crudRemoveCol(array("product_name","product_id"));
echo $pdocrud->dbTable("products")->render();
Field Display Order
$pdocrud = new PDOCrud();
$pdocrud->fieldDisplayOrder(array("first_name","last_name","phone", "gender", "birth_date"));//display order of the fields(enter fields only till you want to change the order)
echo $pdocrud->dbTable("users")->render();
Quick Data View
You can set quickView=true to quickly view the table data just below the table on click of row. You can pass quickView=true in constructor function or set it directly in config file. Click on any table/grid row to view this operation in action. You can set the color/css of the table row selected in the style.css file.
//pass quickView=true in constructor function or you can set it via config file
$pdocrud = new PDOCrud(false, "", "", array("quickView" => true));
echo $pdocrud->dbTable("employee")->render();
