Edit Master Detail Grid that Uses SQL Alias
Problem:
I am having issues with a master-detail form. To join both the forms, I am using SQL alias for a column, so that I can link the master-detail forms (The database fields in both the tables have different key names).
This works fine to display the data, but when we are trying to insert data into the master table, the insert statement is failing due to the dummy aliased column being used in the INSERT statements. I have set the SQL aliased column as read-only, hidden, etc..
Can you please advice how to solve this issue?
Solution:
Using our sample database here's a working snippet.
$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 the 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 the database for editing.
The only limitation is that the SQL alias must be in master grid, or the edit will again fail.
Happy coding!