Master Detail with Column Alias

It can be tricky working with master detail grids with column name alias, especially during edit. Using our sample database, here’s a working snippet.

1
2
3
4
5
6
7
8
$sg = new C_DataGrid("SELECT employeeNumber, employeeNumber as salesRepEmployeeNumber, lastName, firstName, email, jobTitle FROM  employees","employeeNumber","employees");
$sg_d1 = new C_DataGrid("SELECT customerNumber, customerName, city, state, salesRepEmployeeNumber, phone FROM customers","customerNumber","customers");

$sg -> set_masterdetail($sg_d1, 'salesRepEmployeeNumber');
$sg -> enable_edit()->set_col_readonly("salesRepEmployeeNumber");
$sg_d1 -> enable_edit();

$sg -> display();

In master table “employeeNumber” is the FK reference to the detail table that has the column named “salesRepEmployeeNumber”. Create an alias that matches the corresponding name used in detail table, in this case it’s “salesRepEmployeeNumber”; it’s important to also keep the original column name because in some database such as MySQL does not allow alias in WHERE clause.

Lastly, use set_col_readonly() to set the alias name “salesRepEmployeeNumber” to read only so it won’t be passed to database for editing.

The only limitation is that the SQL alias must be in master grid, or the edit will again fail.

See Live Example!

Also available on KB: http://phpgrid.uservoice.com/knowledgebase/articles/411029