jTable jQuery插件为什么我的MySQL存储过程失败了? [英] jTable jQuery plugin why is my MySQL stored procedure failing?

问题描述

我正在尝试在 jTable 中实现过滤器.这是 jTable筛选示例.

I am trying to implement a filter in jTable. Here is the jTable Filtering Demo.

我使用了演示提供的jQuery代码，成功地将POST数据发送到listAction php脚本.我访问此数据并将其分配给$ filter变量.我还在字符串的前后添加了两个通配符，以告诉MySQL执行全面搜索而不是索引搜索.

I used the jQuery code supplied by the demo to sucessfully send POST data to the listAction php script. I access this data and assign it to the $filter variable. I also add two wildcards before and after the string to tell MySQL to perform a comprehensive search rather then an index search.

    $filter = '%';
    $filter .= $_POST['name'];
    $filter .= '%';
    $startIndex = $_GET['jtStartIndex'];
    $pageSize = $_GET['jtPageSize'];
    $sorting = $_GET['jtSorting'];

然后，我调用存储过程并传递每个变量.我删除了实际的数据库和过程名称，但我确认它们是正确的.

I then call the stored procedure and pass in each variable. I removed actual database and procedure name, but I verified they are correct.

$mysqli->query("USE data;");
$res = $mysqli->query("CALL proced($startIndex, $pageSize, $filter);");

$ res在这里返回false.

$res returns false here.

调用存储过程肯定可以正常工作，因为我还有其他功能可以通过这种方式工作.我还有其他存储过程可以正常返回0到9条记录.这是当前的存储过程代码:

Calling Stored Procedures DEFINITELY works as I have other functionalities working this way. I have other Stored Procedures working that simply return 0 to 9 records correctly. Here is the current Stored Procedure code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `proced`(startIndex INTEGER, pageSize INTEGER, filter VARCHAR(255))
BEGIN
    SELECT * FROM reports 
    WHERE `new`=0 AND ReportingName LIKE filter
    ORDER BY idReports ASC 
    LIMIT startIndex, pageSize;
END

在MySQL工作台中传递值有效，查询返回正确的行:

Passing in values in MySQL workbench works and the query returns the correct rows:

call proced(
    0,
    10,
    'Art%'
);

但是jTable失败，并显示与服务器通信时发生错误."

But jTable fails with "An error occured while communicating to the server."

有人能指出我正确的方向吗?

Can anyone point me in the right direction?

推荐答案

通过从MySQL找回错误来解决此问题:

Got this working by getting an error back from MySQL:

if(!$res){
    ChromePhp::log($mysqli->error);
    die('error ['. $mysqli->error . ']');

}

错误:

Unknown column 'Art' in 'field list'

解决方案(从链接复制):

请尝试对"Art"使用不同的引号，因为标识符引号是反引号(`").否则，MySQL会认为"您指向名为"Art"的列.

Try using different quotes for "Art" as the identifier quote character is the backtick ("`"). Otherwise MySQL "thinks" that you point to a column named "Art".

另请参阅MySQL 5文档

See also MySQL 5 Documentation

因此只需在传递的String参数中添加单引号即可:

So simply by adding single quotes to the passed in String parameter works:

CALL procedure('$filter');

这篇关于jTable jQuery插件为什么我的MySQL存储过程失败了?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！