最受欢迎

将ParamQuery Grid与MySQL数据库集成，并通过创建一个JSON对象来显示查询结果——不显示获取的记录

Integrating ParamQuery Grid with MySQL DB and displaying query results by creating a JSON object - does not show the fetched records

本文关键字：显示 对象 一个 JSON 查询 结果 记录 获取 MySQL 数据库集成 Grid | 更新日期: 2023-09-26

我正试图将Paramquery网格插件(来源:http://paramquery.com/)与MySQL DB集成-以便从任何表中获取的记录在分页和排序后显示。

下面是我要做的:

步骤1:从MySQL表中获取数据，并将结果集转换为JSON对象

<?PHP
    include "connection.php";       // Sets up connectivity to MySQL database
    // Check if the table has at least one record.
    $query = "SELECT count(*) AS C FROM pr_vendor_list ";
    $result     =   mysql_query($query);
    $row    =   mysql_fetch_object($result);
    if ($row->C > 0)
     {
        // Fetch all records from the table.
        $query1 = "SELECT vendor_nm, addr_1, addr_2, city FROM pr_vendor_list  ";
        $result1    =   mysql_query($query1);
        $rows       =   array();

        while( $row1 = mysql_fetch_assoc($result1) )
         {
           $rows[] = $row1;
         }
         // create JSON object
        $json_data  =    json_encode($rows);
        else
        {
              ; // Empty table - do something else
        }

?>

步骤2:创建JSON对象后，我试图在上述jQuery插件中呈现结果:

    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<!--jQuery dependencies-->
<link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/themes/base/jquery-ui.css" />
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>    
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/jquery-ui.min.js"></script>
<!--PQ Grid files-->
<link rel="stylesheet" href="pqgrid.min.css" />
<script src="pqgrid.min.js"></script>
<!--PQ Grid Office theme-->
<link rel="stylesheet" href="themes/office/pqgrid.css" />
<script>
$(function () {
// Following commented block has the orginal data source inline
/*
var data = [[1, 'Exxon Mobil', '339,938.0', '36,130.0'],
[2, 'Wal-Mart Stores', '315,654.0', '11,231.0'],
[3, 'Royal Dutch Shell', '306,731.0', '25,311.0'],
[19, 'Crédit Agricole', '110,764.6', '7,434.3'],
[20, 'American Intl. Group', '108,905.0', '10,477.0']];
*/
var data = <?PHP echo $json_data;?>;
var obj = { width: 800, height: 400, title: "Registered Vendors" };
obj.colModel = 
[
{ title: "Vendor Name", width: 200, dataType: "string" },
{ title: "Address 1", width: 200, dataType: "string" },
{ title: "Address 2", width: 200, dataType: "string", align: "center" },
{ title: "City", width: 200, dataType: "string", align: "center"}
];
obj.dataModel = 
{ 
data: data ,
sorting: "local",
paging: "local",
dataType: "JSON",
method: "GET",
curPage: 1,
rPP: 20,
sortIndx: 0,
sortDir: "up",
rPPOptions: [5, 10, 15, 100, 1000]
};
var $grid = $("#grid_parts").pqGrid(obj);
$("#grid_parts_topVisible").change(function (evt) {
//alert("");
$grid.pqGrid("option", "topVisible", $(this).is(":checked"));
}).attr("checked", $grid.pqGrid("option", "topVisible"));
$("#grid_parts_bottomVisible").change(function (evt) {
//alert("");
$grid.pqGrid("option", "bottomVisible", $(this).is(":checked"));
}).attr("checked", $grid.pqGrid("option", "bottomVisible"));
$("#grid_parts_columnBorders").change(function (evt) {
//alert("");
$grid.pqGrid("option", "columnBorders", $(this).is(":checked"));
}).attr("checked", $grid.pqGrid("option", "columnBorders"));
$("#grid_parts_rowBorders").change(function (evt) {
//alert("");
$grid.pqGrid("option", "rowBorders", $(this).is(":checked"));
}).attr("checked", $grid.pqGrid("option", "rowBorders"));
$("#grid_parts_oddRowsHighlight").change(function (evt) {
//alert("");
$grid.pqGrid("option", "oddRowsHighlight", $(this).is(":checked"));
}).attr("checked", $grid.pqGrid("option", "oddRowsHighlight"));
$("#grid_parts_numberCell").change(function (evt) {
//alert("");
$grid.pqGrid("option", "numberCell", $(this).is(":checked"));
}).attr("checked", $grid.pqGrid("option", "numberCell"));
$("#grid_parts_autoHeight").change(function (evt) {
//alert("");
$grid.pqGrid("option", "flexHeight", $(this).is(":checked"));
}).attr("checked", $grid.pqGrid("option", "flexHeight"));
$("#grid_parts_autoWidth").change(function (evt) {
//alert("");
$grid.pqGrid("option", "flexWidth", $(this).is(":checked"));
}).attr("checked", $grid.pqGrid("option", "flexWidth"));
$("#grid_parts_horizontal").change(function (evt) {
//alert("");
$grid.pqGrid("option", "scrollModel", { horizontal: $(this).is(":checked") });
}).attr("checked", $grid.pqGrid("option", "scrollModel.horizontal"));
$("#grid_parts_resizable").change(function (evt) {
//alert("");
$grid.pqGrid("option", "resizable", $(this).is(":checked"));
}).attr("checked", $grid.pqGrid("option", "resizable"));
$("#grid_parts_paging").change(function (evt) {
var paging="";
if ($(this).is(":checked")) {
paging = "local";
}
$grid.pqGrid("option", "dataModel.paging", paging);
}).attr("checked", ($grid.pqGrid("option", "dataModel.paging")=="local")?true:false);
$("#grid_parts_roundCorners").change(function (evt) {
$grid.pqGrid("option", "roundCorners", $(this).is(":checked"));
}).attr("checked", $grid.pqGrid("option", "roundCorners"));
//
$( "#grid_parts" ).pqGrid( {numberCell:false} );
});     
</script>    
</head>
<body>
<center>
<div id="grid_parts" style="margin:10px;"></div>
<BR>
</body>
</html>

我能够看到创建的网格，但没有数据显示在那里。我所看到的只是在分页栏中正确显示已获取记录的总数的空网格。

========================================================================

根据Harshitha的建议，我修改了代码。我观察到的另一个现象是，如果我将值作为数组对象传递，则呈现会按照预期进行。但是，如果var obj被初始化为JSON对象，虽然网格被呈现，但不呈现与JSON数据对应的行。

我用适当的注释追加了下面最新的代码。

    <?PHP
    include "connection.php";       // Sets up connectivity to MySQL database
    // Check if the table has at least one record.
    $query = "SELECT count(*) AS C FROM pr_vendor_list ";
    $result     =   mysql_query($query);
    $row    =   mysql_fetch_object($result);
    if ($row->C > 0)
    {
    // Fetch all records from the table.
    $query1 = "SELECT vendor_nm, addr_1, addr_2, city FROM pr_vendor_list  ";
    $result1    =   mysql_query($query1);
    $rows       =   array();

    while( $row1 = mysql_fetch_assoc($result1) )
    {
    $rows[] = $row1;
    }
    // create JSON object
    $json_data  =    json_encode($rows);        // This var value will be used in HTML <script> section below to initialize the JS object
    }
    else
    {
    ; // Empty table - do something else
    }
    ?>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
    <html>
    <head>
    <!--jQuery dependencies-->
    <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/themes/base/jquery-ui.css" />
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>    
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/jquery-ui.min.js"></script>
    <!--PQ Grid files-->
    <link rel="stylesheet" href="pqgrid.min.css" />
    <script src="pqgrid.min.js"></script>
    <!--PQ Grid Office theme-->
    <link rel="stylesheet" href="themes/office/pqgrid.css" />
    <script>
    $(function () { 
    /*
    var data = 
    [
    [1, 'Exxon Mobil', '339,938.0', '36,130.0'],
    [2, 'Wal-Mart Stores', '315,654.0', '11,231.0'],
    [3, 'Royal Dutch Shell', '306,731.0', '25,311.0'],
    [4, 'BP', '267,600.0', '22,341.0'],
    [5, 'General Motors', '192,604.0', '-10,567.0'],
    [6, 'Chevron', '189,481.0', '14,099.0'],
    [7, 'DaimlerChrysler', '186,106.3', '3,536.3'],
    [8, 'Toyota Motor', '185,805.0', '12,119.6'],
    [9, 'Ford Motor', '177,210.0', '2,024.0'],
    [10, 'ConocoPhillips', '166,683.0', '13,529.0'],
    [11, 'General Electric', '157,153.0', '16,353.0'],
    [12, 'Total', '152,360.7', '15,250.0'],
    [13, 'ING Group', '138,235.3', '8,958.9'],
    [14, 'Citigroup', '131,045.0', '24,589.0'],
    [15, 'AXA', '129,839.2', '5,186.5'],
    [16, 'Allianz', '121,406.0', '5,442.4'],
    [17, 'Volkswagen', '118,376.6', '1,391.7'],
    [18, 'Fortis', '112,351.4', '4,896.3'],
    [19, 'Crédit Agricole', '110,764.6', '7,434.3'],
    [20, 'American Intl. Group', '108,905.0', '10,477.0']
    ];
    */  
    var data = [
    { rank: 1, company: 'Exxon Mobil', revenues: '339,938.0', profits: '36,130.0' },
    { rank: 2, company: 'Wal-Mart Stores', revenues: '315,654.0', profits: '11,231.0' },
    { rank: 3, company: 'Royal Dutch Shell', revenues: '306,731.0', profits: '25,311.0' },
    { rank: 4, company: 'BP', revenues: '267,600.0', profits: '22,341.0' },
    { rank: 5, company: 'General Motors', revenues: '192,604.0', profits: '-10,567.0' },
    { rank: 6, company: 'Chevron', revenues: '189,481.0', profits: '14,099.0' },
    { rank: 7, company: 'DaimlerChrysler', revenues: '186,106.3', profits: '3,536.3' },
    { rank: 8, company: 'Toyota Motor', revenues: '185,805.0', profits: '12,119.6' },
    { rank: 9, company: 'Ford Motor', revenues: '177,210.0', profits: '2,024.0' },
    { rank: 10, company: 'ConocoPhillips', revenues: '166,683.0', profits: '13,529.0' },
    { rank: 11, company: 'General Electric', revenues: '157,153.0', profits: '16,353.0' },
    { rank: 12, company: 'Total', revenues: '152,360.7', profits: '15,250.0' },
    { rank: 13, company: 'ING Group', revenues: '138,235.3', profits: '8,958.9' },
    { rank: 14, company: 'Citigroup', revenues: '131,045.0', profits: '24,589.0' },
    { rank: 15, company: 'AXA', revenues: '129,839.2', profits: '5,186.5' },
    { rank: 16, company: 'Allianz', revenues: '121,406.0', profits: '5,442.4' },
    { rank: 17, company: 'Volkswagen', revenues: '118,376.6', profits: '1,391.7' },
    { rank: 18, company: 'Fortis', revenues: '112,351.4', profits: '4,896.3' },
    { rank: 19, company: 'Crédit Agricole', revenues: '110,764.6', profits: '7,434.3' },
    { rank: 20, company: 'American Intl. Group', revenues: '108,905.0', profits: '10,477.0' }
    ];

    /*
    // This would be the code eventually to initialize the JS variable from PHP created JSON object
    var data = <?PHP echo $json_data;?>;
    */
    var obj = { width: 700, height: 400, title: "Grid From JSON data", flexHeight: true };
    obj.colModel = 
    [
    { title: "Rank", width: 100, dataType: "integer", dataIndex: "rank"  },
    { title: "Company", width: 200, dataType: "string", dataIndex: "company"  },
    { title: "Revenues ($ millions)", width: 150, dataType: "float" , dataIndex: "revenues" },
    { title: "Profits ($ millions)", width: 150, dataType: "float", dataIndex: "profits" }
    ];
    obj.dataModel = 
    { 
    data: data ,
    sorting: "local",
    paging: "local",
    dataType: "JSON",
    method: "GET",
    curPage: 1,
    rPP: 20,
    sortIndx: 0,
    sortDir: "up",
    rPPOptions: [5, 10, 15, 100, 1000]
    };
    $("#grid_sorted").pqGrid(obj);
    });
    </script>    
    </head>
    <body>
    <center>
    <div id="grid_sorted" style="margin:100px;"></div>  <!-- renders the grid here -->
    </body>
    </html>

您需要在colModel中包含dataIndx

将php代码放在页面的开头。然后在脚本部分使用

$(document).ready(function(){
var data = <?PHP echo 'var data ='.$json_data; ?>
obj = { width: 800, height: 400, title: "Registered Vendors" };
obj.colModel = 
[
{ title: "Vendor Name", width: 200, dataType: "string", dataIndx:"vendor_nm" },
{ title: "Address 1", width: 200, dataType: "string", dataIndx:"addr_1" },
{ title: "Address 2", width: 200, dataType: "string", align: "center", dataIndx:"addr_2"},
{ title: "City", width: 200, dataType: "string", align: "center", dataIndx:"city"}
];
});

将剩下的代码放在obj.dataModel

下面

我也有同样的问题，这里是我如何能够使它工作(我将使用您的数据为例)不确定这是否仍然是您的问题

  1. 创建一个PHP文件返回JSON输出，命名为json_sample.php 

    <?php
    include "connection.php";
    // Check if the table has at least one record.
    $query = "SELECT count(*) AS C FROM pr_vendor_list ";
    $result     =   mysql_query($query);
    $row    =   mysql_fetch_object($result);
    if ($row->C > 0)
    {
    // Fetch all records from the table.
    $query1 = "SELECT vendor_nm, addr_1, addr_2, city FROM pr_vendor_list  ";
    $result1    =   mysql_query($query1);
    $rows       =   array();
    while( $row1 = mysql_fetch_assoc($result1) )
    {
    $rows[] = $row1;
    }
    // create JSON object
    echo json_encode($rows);
    ?>

  • 创建一个javascript文件，将使用json文件来获取您的数据，称为sample_script.js

  • 创建index.php文件，显示你的结果

    •     $(function() {
      $.getJSON("sample_json.php", function(data) {
        var obj = {
          width: 700,
          height: 400,
          title: "Grid From JSON data",
          flexHeight: true
        };
        obj.colModel = [{
          title: "Rank",
          width: 100,
          dataType: "integer",
          dataIndex: "rank"
        }, {
          title: "Company",
          width: 200,
          dataType: "string",
          dataIndex: "company"
        }, {
          title: "Revenues ($ millions)",
          width: 150,
          dataType: "float",
          dataIndex: "revenues"
        }, {
          title: "Profits ($ millions)",
          width: 150,
          dataType: "float",
          dataIndex: "profits"
        }];
        obj.dataModel = {
          data: data,
          sorting: "local",
          paging: "local",
          dataType: "JSON",
          method: "GET",
          curPage: 1,
          rPP: 20,
          sortIndx: 0,
          sortDir: "up",
          rPPOptions: [5, 10, 15, 100, 1000]
        };
        $("#grid_json").pqGrid(obj);
      });
    });
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
  <titleSAmple Gird</title>
    <!--jQuery DEPENDENCIES-->
    <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/themes/base/jquery-ui.css" />
    <!-- FOR PQGRID -->
    <link rel="stylesheet" href="../css/pqgrid.min.css" />
    <link rel="stylesheet" href="../css/pqgrid_office.css" />
    <!--jQuery DEPENDENCIES-->
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/jquery-ui.min.js"></script>
    <!-- FOR PQGRID -->
    <script type="text/javascript" src="../js/touch-punch.js"></script>
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.1/jquery-ui.min.js"></script>
    <script src="../js/pqgrid.min.js"></script>
    <script src="../js/sample_script.js"></script>
</head>
<body>
  <center>
    <div id="grid_json" style="margin:100px;"></div>
    <!-- renders the grid here -->
</body>
</html>

    这对我有用。如果您有任何问题，请告诉我。

    相关文章：
    最新更新
    www.56WenDa.com 2012-2023 | php问答网 | php学习