Geek Answers Handbook

Processing server data Datatables INNER JOIN or multiple tables

Below is my code without server-side processing, but since it creates many lines and grows significantly, I need to use server-side processing.

DB tables (not all fields are included, but these are the ones I want and the INNER JOIN fields):

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)

I want to show UNIXTIMESTAMP as a date ('d / m / Y');

Will this work for 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 ";

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>'; }

I have server side processing woking with my data, but without binding to other tables. This table returns only identifiers, and I want to get the value from another table, as shown above.

Do I need to use an internal join? If so, how can I include a script with server_processing.PHP: I used this: http://datatables.net/release-datatables/examples/data_sources/server_side.html

Or I can include my method above in server_processing.php.

Everyone is welcome any guide. Thanks in advance.

+2
source share
2 answers

Answer the question: The problem consisted of two column names, so I used alises in the SQL query. Also did not use INNER JOIN.

an array of columns with alias names and column names:

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

SQL query for 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());
+3
source

Yes, I think you should use the inner join (but I need more details about the tables to write the exact query), and then you should change sql here

  $sQuery = " SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))." FROM $sTable $sWhere $sOrder $sLimit "; $rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

EDIT - to register a request, execute

 $filename = __DIR__.DIRECTORY_SEPARATOR."logging.txt"; file_put_contents($filename, $sQuery, FILE_APPEND);

you should have a logging.txt file in the same script directory

+2
source

More articles:


All Articles