Showing Tables
Join Operation
Notes:
- 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 1
Notes:
- 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. ("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", "INNER JOIN");
echo $pdocrud->dbTable("users")->render();
Inner Join 2
Notes:
- 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 (Added in v4.0)
Notes:
- 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();
Multi Table Relation - Nested Tabled (Added imn v2.0)
Notes:
- Editing of related records in other table.
- 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. Click here for tab version.
//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 (Asdded in v2.0)
Notes:
- Editing of related records in other table.
- 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)
$pStudentSection = new PDOCrud(true);
$pStudentSection->dbTable("section");
//first paramater is first table(object) columnn name and 2nd parameter is 2nd object column name
$pStudentClass->multiTableRelation("class_id", "class_id", $pStudentSection);
$pStudentSection->multiTableRelationDisplay("tab", "Section");
$pStudent->crudTableCol(array("student_id","first_name","last_name"));//optional
echo $pStudent->dbTable("student")->render();
Multi Table Relation in Tabs - Nested Tables of View (Added in v2.6)
Notes:
- Editing of related records in other table.
- 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.
- Please note that main tables must have fields present that are used for where condition to bind records. You need to enable $config["viewFormTabs"] = true; to make this work.
//student table object
$pStudent = new PDOCrud();
$pStudent->setSettings("viewFormTabs", true);
$pStudent->multiTableRelationDisplay("tab", "Student");
//student's class object
$pStudentClass = new PDOCrud(true);
$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");
$pStudentTransport->setSettings("viewFormTabs", true);
//2nd level of nesting - relating section to class(first student to class and then class to section)
$pStudentSection = new PDOCrud(true);
$pStudentSection->dbTable("section");
//first paramater is first table(object) columnn name and 2nd parameter is 2nd object column name
$pStudentClass->multiTableRelation("class_id", "class_id", $pStudentSection);
$pStudentSection->multiTableRelationDisplay("tab", "Section");
echo $pStudent->dbTable("student")->render();
