php - 为什么 JQWidget 在过滤时显示内部服务器错误 500？

Question

我使用 JQwidgets 通过过滤器显示来自 mysql 数据库的数据，但每当我尝试过滤数据时，它都会出现内部服务器错误。只是想知道可能是什么原因？是因为表连接吗？或任何其他原因:

数据.php

<?php
// Include the connect.php file
include ('connect.php');
// Connect to the database
// connection String
$mysqli = new mysqli($hostname, $username, $password, $database);
/* check connection */
if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$pagenum = $_GET['pagenum'];
$pagesize = $_GET['pagesize'];
$start = $pagenum * $pagesize;
$query = "SELECT SQL_CALC_FOUND_ROWS cc.name AS 'Category', c.fullname
AS 'Course', u.firstname AS 'Name' , u.lastname AS 'EmployeeID',
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'
ELSE gi.itemname 
END AS 'Activity', CASE WHEN gg.finalgrade IN (0.00, NULL) THEN 'Absent'
AND AS 'State' FROM mdl_course AS c JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid 
JOIN mdl_grade_grades AS gg ON gg.userid = u.id 
JOIN mdl_grade_items AS gi ON gi.id = gg.itemid AND gi.itemmodule = 'attendance' AND gi.courseid = c.id
JOIN mdl_course_categories AS cc ON cc.id = c.category LIMIT ?, ?";
$result = $mysqli->prepare($query);
$result->bind_param('ii', $start, $pagesize);
// filter data.
if (isset($_GET['filterscount']))
{
$filterscount = $_GET['filterscount'];
if ($filterscount > 0)
    {
    $where = " WHERE (";
    $tmpdatafield = "";
    $tmpfilteroperator = "";
    $valuesPrep = "";
    $values = [];
    for ($i = 0; $i < $filterscount; $i++)
        {
        // get the filter's value.
        $filtervalue = $_GET["filtervalue" . $i];
        // get the filter's condition.
        $filtercondition = $_GET["filtercondition" . $i];
        // get the filter's column.
        $filterdatafield = $_GET["filterdatafield" . $i];
        // get the filter's operator.
        $filteroperator = $_GET["filteroperator" . $i];
        if ($tmpdatafield == "")
            {
            $tmpdatafield = $filterdatafield;
            }
          else if ($tmpdatafield <> $filterdatafield)
            {
            $where.= ")AND(";
            }
          else if ($tmpdatafield == $filterdatafield)
            {
            if ($tmpfilteroperator == 0)
                {
                $where.= " AND ";
                }
              else $where.= " OR ";
            }
        // build the "WHERE" clause depending on the filter's condition, value and datafield.
        switch ($filtercondition)
            {
        case "CONTAINS":
            $condition = " LIKE ";
            $value = "%{$filtervalue}%";
            break;

        case "DOES_NOT_CONTAIN":
            $condition = " NOT LIKE ";
            $value = "%{$filtervalue}%";
            break;

        case "EQUAL":
            $condition = " = ";
            $value = $filtervalue;
            break;

        case "NOT_EQUAL":
            $condition = " <> ";
            $value = $filtervalue;
            break;

        case "STARTS_WITH":
            $condition = " LIKE ";
            $value = "{$filtervalue}%";
            break;

        case "ENDS_WITH":
            $condition = " LIKE ";
            $value = "%{$filtervalue}";
            break;

        case "NULL":
            $condition = " IS NULL ";
            $value = "%{$filtervalue}%";
            break;

        case "NOT_NULL":
            $condition = " IS NOT NULL ";
            $value = "%{$filtervalue}%";
            break;
            }
        $where.= " " . $filterdatafield . $condition . "? ";
        $valuesPrep = $valuesPrep . "s";
        $values[] = & $value;
        if ($i == $filterscount - 1)
            {
            $where.= ")";
            }
        $tmpfilteroperator = $filteroperator;
        $tmpdatafield = $filterdatafield;
        }
    $valuesPrep = $valuesPrep . "ii";
    $values[] = & $start;
    $values[] = & $pagesize;
    // build the query.
    $query = "SELECT SQL_CALC_FOUND_ROWS cc.name AS 'Category', c.fullname AS 'Course', u.firstname AS 'Name' , u.lastname AS 'EmployeeID',
CASE WHEN gi.itemtype = 'Course'
THEN c.fullname + ' Course Total'  
ELSE gi.itemname 
END AS 'Activity', CASE WHEN gg.finalgrade IN (0.00, NULL) THEN 'Absent' END AS 'State' FROM mdl_course AS c JOIN mdl_context AS ctx ON c.id = ctx.instanceid 
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id 
JOIN mdl_user AS u ON u.id = ra.userid 
JOIN mdl_grade_grades AS gg ON gg.userid = u.id 
JOIN mdl_grade_items AS gi ON gi.id = gg.itemid AND gi.itemmodule = 'attendance' AND gi.courseid = c.id 
JOIN mdl_course_categories AS cc ON cc.id = c.category" . $where . " LIMIT ?, ?";
$result = $mysqli->prepare($query);
call_user_func_array(array( $result,"bind_param") , array_merge(array(
        $valuesPrep ) , $values));  }} 
$result->execute();
/* bind result variables */
$result->bind_result($Category, $Course, $Name, $EmployeeID, $Activity, $State);
/* fetch values */
while ($result->fetch())
{
$orders[] = array(
    'Category' => $Category,
    'Course' => $Course,
    'Name' => $Name,
    'EmployeeID' => $EmployeeID,
    'Activity' => $Activity,
    'State' => $State
);
}
$result = $mysqli->prepare("SELECT FOUND_ROWS()");
$result->execute();
$result->bind_result($total_rows);
$result->fetch();
$data[] = array('TotalRows' => $total_rows,'Rows' => $orders);
echo json_encode($data);
/* close statement */
$result->close();
/* close connection */
$mysqli->close();
?>

index.php

<script type="text/javascript">
    $(document).ready(function () {
        // prepare the data

        var theme = 'metro';

        var source =
        {    datatype: "json",
             datafields: [
                 { name: 'Category', type: 'string'},
                 { name: 'Course', type: 'string'},
                 { name: 'Name', type: 'string'},
                 { name: 'EmployeeID', type: 'string'},
                 { name: 'Activity', type: 'string'},
                 { name: 'State', type: 'string'}
            ],
            url: 'data.php',
            cache: false,
            filter: function()
            {
                // update the grid and send a request to the server.
                $("#jqxgrid").jqxGrid('updatebounddata', 'filter');
            },
            root: 'Rows',
            beforeprocessing: function(data)
            {       
                source.totalrecords = data[0].TotalRows;                    
            }
        };      
        var dataadapter = new $.jqx.dataAdapter(source, {
                loadError: function(xhr, status, error)
                {
                    alert(error);
                }
            }
        );

        // initialize jqxGrid
        $("#jqxgrid").jqxGrid(
        {       
            source: dataadapter,
            theme: theme,
            width: '90%',
            pagesize: 100,
            filterable: true,
            autoheight: true,
            pageable: true,
            virtualmode: true,
            rendergridrows: function()
            {
                  return dataadapter.records;     
            },
            columns: [
                  { text: 'Category', datafield: 'Category', width: 100 },
                  { text: 'Course', datafield: 'Course', width: 200 },
                  { text: 'Name', datafield: 'Name', width: 200 },
                  { text: 'Employee ID', datafield: 'EmployeeID', width: 100 },
                  { text: 'Activity', datafield: 'Activity', width: 100 },
                  { text: 'State', datafield: 'State', width: 80 }
              ]
        });     
        $("#csvExport").jqxButton();
        $("#csvExport").click(function () {
            $("#jqxgrid").jqxGrid('exportdata', 'csv', 'jqxGrid');
        });

    });

</script>
</head>
<body class='default'>
<div id='jqxWidget'">
    <div id="jqxgrid"></div>
     <div style='margin-top: 20px;'>

            <br />
        </div>
        <div style='margin-left: 10px; float: left;'>
            <input type="button" value="Export to CSV" id='csvExport' />
        </div>
</div>

任何引用或帮助将不胜感激。

Answer 1

您的查询具有列别名。

SELECT SQL_CALC_FOUND_ROWS cc.name AS '类别', c.fullname AS '类(class)', u.firstname AS '姓名' , u.lastname AS 'EmployeeID' ...

jqwidgets 返回过滤器数据字段，它们是这些列的别名。主查询将无法理解“WHERE”语句中的别名，并会给出错误。

示例:假设我们过滤“类别”列。这将显示查询错误，因为“Category”对于Where子句中的查询来说是未知的

SELECT SQL_CALC_FOUND_ROWS cc.name AS '类别' ... FROM ...其中类别 = ...

但这会起作用:

SELECT SQL_CALC_FOUND_ROWS cc.name AS '类别' ... FROM ...WHERE cc.name = ...

尝试使用“HAVING”而不是“WHERE”来查看是否有效。

即在您的代码中进行此更改。改变

$where = " WHERE (";

到

$where = " HAVING (";

看看是否可以解决问题。

一般来说，这个查询会起作用

SELECT SQL_CALC_FOUND_ROWS cc.name AS 'Category' ... FROM ...
HAVING Category = ...