Working with Complex Query

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
$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>