数据表+ PHP:在多个表上进行服务器端处理 [英] Datatables + PHP: Server-Side Processing on Multiple Tables
问题描述
如何获取数据表服务器端处理脚本以使用自定义查询?我需要从多个表中选择列，并让数据表呈现它们.
How can I get Datatables Server-Side Processing script to work with a custom query? I need to select columns from multiple tables and have Datatables render them.
Datatables.net的PHP服务器端处理(SSP)总结如下: https://datatables.net/examples/server_side/simple.html
Datatables.net's Server-Side Processing (SSP) with PHP is summarized here: https://datatables.net/examples/server_side/simple.html
我发现了这个 SO问题，但是原始海报从未提供他的解决方案.我没有足够的声誉要求他提供更多细节.
I found this SO question, but the original poster never provided his solution. I don't have sufficient reputation to ask him to provide more detail.
这是我未使用Datatable的SSP的原始SQL
Here is my raw SQL without using Datatable's SSP
SELECT tbl_houses.style, tbl_houses.roomCount, tbl_residents.firstName, tbl_residents.lastName
FROM tbl_houses, tbl_residents
WHERE tbl_houses.houseID = tbl_residents.residentID
/*
* # Equivalent query using JOIN suggested by @KumarRakesh
* # Note: JOIN ... ON is a synonym for INNER JOIN ... ON
* # Using JOIN conforms to syntax spec'd by ANSI-92 https://stackoverflow.com/a/894855/946957
*
* SELECT tbl_houses.style, tbl_houses.roomCount, tbl_residents.firstName, tbl_residents.lastName
* FROM tbl_houses
* JOIN tbl_residents ON tbl_houses.houseID = tbl_residents.residentID
*/
如何使用SSP获取数据表以运行上述查询?
How can I get Datatables to run queries off the above using SSP?
它显示为 server_processing.php 仅接受1个表，不接受自定义过滤(即WHERE子句).
It appears server_processing.php only accepts 1 table and no custom filtering (i.e., WHERE clauses).
// DB table to use
$table = 'datatables_demo';
// Table's primary key
$primaryKey = 'id';
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/
require( 'ssp.class.php' );
echo json_encode(
SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);
但是， ssp.class. php 确实支持使用WHERE进行过滤.我在想我需要修改ssp.class.php以便在我的WHERE子句中强制使用
However, ssp.class.php does support filtering using WHERE. I'm thinking I need to modify ssp.class.php to force in my WHERE clause
更新
找到了解决方案.有空的时候会发帖.
Found a solution. Will post when I have free time.
推荐答案
TL; DR:我最终使用了对原始数据表ssp.class.php的修改，称为
TL;DR: I ended up using a modification of the original Datatables ssp.class.php called ssp.php implemented by Emran Ul Hadi: https://github.com/emran/ssp
他的修改接受JOIN，WHERE，GROUP BY和列别名.尽管该文件已经一年多没有更新了，但仍可与DataTables 1.12.x一起使用.我对他的版本进行了一些修改，以增强其健壮性，并通过更清晰的示例改进了文档.
His modification accepts JOIN, WHERE, GROUP BY and column aliases. Although the file hasn't been updated in over a year, it still works with DataTables 1.12.x. I made some modifications to his version that increases its robustness and improves the documentation with clearer examples.
当我有更多时间时，将在此处发布我的mod/更新.最终，我希望提出请求，以将我的更新保存到他的存储库中.
Will post my mods/updates here when I have a bit more time. Eventually I hope to put in a pull-request to get my updates into his repository.
这篇关于数据表+ PHP:在多个表上进行服务器端处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
