如何使用ssp.class.php加入两个表 [英] How to join two tables with ssp.class.php
问题描述
我开始使用数据表表插件jQuery ,并遇到一些问题。我正在使用此处的示例代码。
$ b $我有MySQL表女巫看起来像这样:
id |名称| parent_id
father_id
是 id
在同一个表中的值只在不同的行。所以如果我想知道父亲的名字,我必须在同一个表中搜索<code> WHERE id = father_id 。但是,DataTable只是显示MySQL表的内容。
在我的DataTable中,我想显示如下数据:
id |名称| father_name |所以当DataTable从MySQL表中获取数据,但在创建表之前,我想要更改当时值的列值的$ code> father_id 在MySQL的同一行。我想通过搜索特定的
father_id
来添加father_name
。解决方案正如 PaulF 指出的,您需要使用
JOIN
或子查询从同一个表中检索父亲的姓名。
我假设您使用
ssp.class.php
根据您提到的示例在服务器端处理您的数据。
类
ssp.class.php
不支持连接和子查询,但有一个解决方法。诀窍是使用子查询,如下所示$ table
定义。将表
替换为子查询中的实际表名。$ table =<< EOT
(
SELECT
a.id,
a.name,
a.father_id,
b。 name AS father_name
FROM table a
LEFT JOIN表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
以删除反引号。
还有一个 github.com/emran/ssp 存储库,其中包含增强的
ssp.class.php
支持JOIN。I started using DataTables Table plug-in for jQuery and got some problems. I am using example code from here.
I have MySQL table witch looks like that:
id | name | father_id
father_id
isid
value in same table only in different row. So if I want to know father name i have to search in same tableWHERE 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 | name | father_name | father_id
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 addfather_name
by searching for it with particularfather_id
.解决方案As PaulF pointed out, you need to use
JOIN
or sub-query to retrieve father's name from the same table.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
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. Replacetable
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 ) );
UPDATE
You also need to edit
ssp.class.php
and replace all instances ofFROM `$table`
withFROM $table
to remove backticks.There is also github.com/emran/ssp repository that contains enhanced
ssp.class.php
supporting JOINs.这篇关于如何使用ssp.class.php加入两个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!