如何使用 ssp.class.php 连接两个表

2022-01-01 00:00:00 datatables php mysql

我开始使用 用于 jQuery 的 DataTables Table 插件 并遇到了一些问题.我正在使用 此处 中的示例代码.

I started using DataTables Table plug-in for jQuery and got some problems. I am using example code from here.

我的 MySQL 表看起来像这样:

I have MySQL table witch looks like that:

id |姓名 |父亲身份

id | name | father_id

father_id 是同一个表中不同行的 id 值.所以如果我想知道父亲的名字，我必须在同一个表中搜索<​​code>WHERE id = Father_id.但是DataTable所做的只是按原样显示MySQL表的内容.

father_id is id value in same table only in different row. So if I want to know father name i have to search in same table WHERE id = father_id. But what DataTable does it just show the contents of MySQL table as it is.

在我的数据表中，我想显示这样的数据:

In my DataTable i want to show data like that:

id |姓名 |父亲姓名 |父亲身份

id | name | father_name | father_id

因此，当 DataTable 从 MySQL 表中获取数据时，但在创建表之前，我想更改列值，该值当时是 MySQL 中同一行中 father_id 的值.我也想通过使用特定的 father_id 搜索它来添加 father_name.

So when DataTable takes data from MySQL table, but before it creates table I want to change column value which at that time is value of father_id in the same row in MySQL. I want too add father_name by searching for it with particular father_id.

推荐答案

正如 PaulF 指出的那样，你需要使用JOIN 或子查询以从同一张表中检索父亲的名字.

As PaulF pointed out, you need to use JOIN or sub-query to retrieve father's name from the same table.

我假设您正在使用 ssp.class.php 根据您提到的示例在服务器端处理您的数据.

I assume you're using ssp.class.php to process your data on the server-side based on the example you've mentioned.

Class ssp.class.php 不支持连接和子查询，但有一个解决方法.诀窍是在 $table 定义中使用如下所示的子查询.将 table 替换为您在子查询中的实际表名.

Class ssp.class.php doesn't support joins and sub-queries, but there is a workaround. The trick is to use sub-query as shown below in $table definition. Replace table with your actual table name in the sub-query.

$table = <<<EOT
 (
    SELECT 
      a.id, 
      a.name, 
      a.father_id, 
      b.name AS father_name
    FROM table a
    LEFT JOIN table b ON a.father_id = b.id
 ) temp
EOT;

$primaryKey = 'id';

$columns = array(
   array( 'db' => 'id',          'dt' => 0 ),
   array( 'db' => 'name',        'dt' => 1 ),
   array( 'db' => 'father_id',   'dt' => 2 ),
   array( 'db' => 'father_name', 'dt' => 3 )
);

$sql_details = array(
   'user' => '',
   'pass' => '',
   'db'   => '',
   'host' => ''
);

require( 'ssp.class.php' );
echo json_encode(
   SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

您还需要编辑 ssp.class.php 并将 FROM `$table` 的所有实例替换为 FROM $table 以删除反引号.

You also need to edit ssp.class.php and replace all instances of FROM `$table` with FROM $table to remove backticks.

确保所有列名都是唯一的，否则使用 AS 分配别名.

Make sure all column names are unique otherwise use AS to assign an alias.

还有 github.com/emran/ssp 存储库，其中包含增强的 ssp.class.php 支持 JOIN.

There is also github.com/emran/ssp repository that contains enhanced ssp.class.php supporting JOINs.

参见 jQuery 数据表:在 ssp.class.php 中使用 WHERE、JOIN 和 GROUP BY 了解更多信息.

See jQuery DataTables: Using WHERE, JOIN and GROUP BY with ssp.class.php for more information.

相关文章