Dynamic columns in EasyUI datagrid

user2482748 picture user2482748 · Jun 13, 2013 · Viewed 11.5k times · Source

I have a set of predefined MySQL queries whose results I want to present on an EasyUI datagrid (jQuery).

The problem is that each query returns different result columns, so I cannot use something similar to the jQuery tutorial section Dynamically change datagrid columns, as the column titles are not known before executing the query in the PHP file.

However, each JSON result returned by my PHP script contains the names of the data columns. Is there any way I can map the resultset to the datagrid without knowing the column (field) name in advance or my approach is wrong? I am a beginner in jQuery trying to understand.

HTML:

<script>
  function exec_prepared(index){
    $('#tt').panel({title:prepared_statements[index]});
    $('#tt').datagrid('load',{index:index})
  }
</script>
<div id="dlg" class="easyui-dialog" style="width:600px; height:280px; padding:10px 20px"
  closed="false" buttons="#dlg-buttons">
  <div class="ftitle">Prepared Statements</div>
  <form id="fm" method="post" novalidate>
    <div class="fitem">
      <a href="#" class="easyui-linkbutton" data-options="iconCls:'icon-search'"
        onclick="exec_prepared(0)">Test Query 1</a>
      <a href="#" class="easyui-linkbutton" data-options="iconCls:'icon-search'"
        onclick="exec_prepared(1);">Test Query 2</a>
    </div>
  </form>
</div>
</div>
<table id="tt" class="easyui-datagrid" style="width:600px;height:750px"
  url="getdata.php" rownumbers="true" pagination="true" pagesize="40">
</table>

PHP

switch $index
  case 0:
    $query="Select * from tableA";
    break;
  case 1:
    $query="Select * from tableB";
    break;
    ...
if (!$rs=mysql_query($query)){echo "Error in SQL line: ".__LINE__."<br>";echo $query;exit(1);}
$result["total"] = mysql_num_rows($rs);
$items = array();
while($row = mysql_fetch_object($rs)){
  array_push($items, $row);
}
$result["rows"] = $items;
echo json_encode($result);

Answer

arjunaaji picture arjunaaji · Feb 13, 2014

you can use this code:

$.ajax({            
   url:"data/getHeader.php?mo="+node.text,
   cache: false,
   timeout: 5000,   
   async: false,    
   success: function(data){
        var objekJSON=jQuery.parseJSON(data);       
        $('#dg').datagrid({                     
        url:urlData,
        fit:true,
        columns:objekJSON
        });
    }
});

and this is the JSON data output:

[[{"field":"CELLID","title":"CELLID"},{"field":"WEIGHTFORUSEDFREQ","title":"WEIGHTFORUSEDFREQ"},{"field":"TGL","title":"TGL"},{"field":"RNC_NAME","title":"RNC_NAME"}]]

PHP code:

$result = array();
$mo=$_GET['mo'];
$sql="SHOW FIELDS FROM NPT.".$mo;
$rs = mysql_query($sql);
$nodeParent = array();
while($row = mysql_fetch_array($rs)){
    $node = array();    
    $node['field'] = $row['Field']; 
    $node['title'] = $row['Field'];
    array_push($nodeParent,$node);
}  
array_push($result,$nodeParent);
echo json_encode($result);

`

wait, and the HTMLcode:`

<table id="dg" border="false" rownumbers="true" pagination="true"
                                            fit="true" fitColumns="true" singleSelect="true">
                                    </table>