Codded Codded - 11 months ago 197
jQuery Question

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.

DB Tables (not all fields included but these are the ones i want and the fields to INNER JOIN):

course_modules - id (pk), course(, module(, added(UNIXTIMESTAMP)
course - id (pk), category(, fullname(text)
course_categories- id (pk), name(text)
modules- id (pk), name(text)

I want to be able to display the UNIXTIMESTAMP as date('d/m/Y');

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 =
INNER JOIN course ON $sTable.course =
INNER JOIN course_categories ON course.category =

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 '</tr>';

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.

Do I need to use an innner join? If so how can I incorporate with server_processing.php script:
I used this one:

Or can I incorporate my above method within server_processing.php.

Any guidance would be greatly appreciated.
Thanks in advance.

Answer Source

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 array using the alias names and column names:

$aColumns = array( 'catname', 'fullname', 'modulename', 'added');

The SQL query for the 4 tables:

$sQuery = "
    SELECT mdl_course.fullname, AS modulename, AS catname, mdl_course_modules.added
    FROM  mdl_course_modules, mdl_modules, mdl_course, mdl_course_categories
    WHERE mdl_course_modules.module = AND mdl_course_modules.course = AND mdl_course.category =
$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());