数据表服务器端处理INNER JOIN或多个表 [英] Datatables server-side processing INNER JOIN or multiple tables
问题描述
下面是我的代码,没有服务器端处理,但是由于它会产生很多行,并且会大大增长,因此我需要使用服务器端处理.
Below is my code without server-side processing but as it produces many rows and will grow considerably I need to use server-side processing.
数据库表(不包括所有字段,但这些是我想要的字段,并且是INNER JOIN的字段):
DB Tables (not all fields included but these are the ones i want and the fields to INNER JOIN):
course_modules - id (pk), course(course.id), module(module.id), added(UNIXTIMESTAMP)
course - id (pk), category(course_categories.id), fullname(text)
course_categories- id (pk), name(text)
modules- id (pk), name(text)
我希望能够将UNIXTIMESTAMP显示为date('d/m/Y');
I want to be able to display the UNIXTIMESTAMP as date('d/m/Y');
这项工作对INNER JOIN有用吗
Would this work for the INNER JOIN
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
INNER JOIN modules ON $sTable.module = modules.id
INNER JOIN course ON $sTable.course = course.id
INNER JOIN course_categories ON course.category = course_categories.id
$sWhere
$sOrder
$sLimit
";
未经服务器端处理的原始php版本:
Original php version without server-side processing:
$mods = get_records_sql("SELECT * FROM {$CFG->prefix}course_modules");
foreach ($mods as $mod)
{
$thecourse = get_record('course','id',$mod->course);
$themodule = get_record('modules','id',$mod->module);
$thecat = get_record('course_categories','id',$thecourse->category);
echo '<tr>';
echo '<td>'.$thecat->name.'</td>';
echo '<td>'.$thecourse->fullname.'</td>';
echo '<td>'.$themodule->name.'</td>';
echo '<td>';
echo date('d/m/Y', $mod->added);
echo'</td>';
echo '</tr>';
}
我的服务器端处理可以很好地处理我的数据表,但没有链接到其他表.该表仅返回ID,如上所述,我希望能够从另一个表中获取值.
I have server-side processing woking fine with my datatables but without linking to the other tables. This table only returns IDs and I want to be able to get the value from another table as demonstrated above.
我需要使用内部联接吗?如果是这样,我如何与server_processing.php脚本合并: 我使用了这个: http://datatables.net/release-datatables/examples/data_sources/server_side.html
Do I need to use an innner join? If so how can I incorporate with server_processing.php script: I used this one: http://datatables.net/release-datatables/examples/data_sources/server_side.html
或者我可以将上述方法合并到server_processing.php中.
Or can I incorporate my above method within server_processing.php.
任何指导将不胜感激. 预先感谢.
Any guidance would be greatly appreciated. Thanks in advance.
推荐答案
该问题的答案: 问题是2列名称相同,所以我在SQL查询中使用了别名. 也没有使用INNER JOIN.
Answer to the question: Problem was had 2 column names the same so i used alises in the SQL query. Also did not use INNER JOIN.
使用别名和列名的Columns数组:
Columns array using the alias names and column names:
$aColumns = array( 'catname', 'fullname', 'modulename', 'added');
4个表的SQL查询:
The SQL query for the 4 tables:
$sQuery = "
SELECT mdl_course.fullname, mdl_modules.name AS modulename, mdl_course_categories.name AS catname, mdl_course_modules.added
FROM mdl_course_modules, mdl_modules, mdl_course, mdl_course_categories
WHERE mdl_course_modules.module = mdl_modules.id AND mdl_course_modules.course = mdl_course.id AND mdl_course.category = mdl_course_categories.id
$sOrder
$sLimit
";
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
这篇关于数据表服务器端处理INNER JOIN或多个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!