Keep in mind that any direct editing is always limited to the primary table. For example, in the following complex query with joins, it’s possible to edit the primary table “suppliers“. however, any updates to table “supplierproductlines” and “products” will be ignored. It’s recommended to set any non-primary table fields to readonly to avoid any confusion from your application end-users.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | // Always include namespace and conf.php on TOP of the script. use phpCtrl\C_DataGrid; use phpCtrl\C_DataBase; require_once("/file/path/to/conf.php"); $sql = 'select s.supplierCode, s.supplierZip, s.supplierPhonenumber, spl.productLineNo, spl.productLine, p.productName, p.MSRP from suppliers s inner join supplierproductlines spl on s.supplierName = spl.supplierName inner join products p on s.supplierZip = p.supplierZip'; $dg = new C_DataGrid($sql, "supplierCode", "suppliers"); $dg->enable_edit('INLINE'); $dg->set_col_readonly("productLineNo, productLine, productName, MSRP"); $dg->display(); |
The followings are steps to edit complex query based datagrid using the array. The application developer must provide his own implementation of “save_local_array.php”.
1. Generate PHP array from your query:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | $sql = 'select s.supplierCode, s.supplierZip, s.supplierPhonenumber, spl.productLineNo, spl.productLine, p.productName, p.MSRP from suppliers s inner join supplierproductlines spl on s.supplierName = spl.supplierName inner join products p on s.supplierZip = p.supplierZip'; $db = new C_DataBase(PHPGRID_DB_HOSTNAME, PHPGRID_DB_USERNAME, PHPGRID_DB_PASSWORD, PHPGRID_DB_NAME, PHPGRID_DB_TYPE,PHPGRID_DB_CHARSET); $results = $db->db_query($sql); $data1 = array(); $count = 0; while($row = $db->fetch_array_assoc($results)) { $data_row = array(); for($i = 0; $i < $db->num_fields($results); $i++) { $col_name = $db->field_name($results, $i); $data1[$count][$col_name] = $row[$col_name]; } $count++; } |
2. Pass above generated array “data1” to phpGrid.
1 2 3 | $dg = new C_DataGrid($data1, "id", "data1"); $dg->enable_edit('INLINE'); $dg->display(); |
Optional: Additional code snippet to submit data back to server. Users must provide their own save routine.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | <script src="http://malsup.github.com/jquery.form.js"></script> <form id="admin_form"> <div> <input type="submit" value="Submit Local Changes"> </div> </form> <script> $(function() { // bind to the form's submit event $('#admin_form').submit(function(event) { $(this).ajaxSubmit({ type: 'post', dataType:'json', url:'save_local_array.php', data:{ langArray:[] //leave as empty array here }, beforeSubmit: function(arr, $form, options){ options.langArray = $('#data1').jqGrid('getRowData'); // get most current console.log(JSON.stringify(options.langArray)); // return false; // here to prevent submit }, success: function(){ // add routine here when success } }); // return false to prevent normal browser submit and page navigation return false; }); }); </script> |