MySQL data from multiple tables

mccarville

I am very new to DataTables and I am quite frankly amazed at all of it's capabilitites.

I am trying to create a table that is getting it's data from MySql. Everything works great if I select from a single table in my database, but I really need to select multiple columns from mulitiple tables with WHERE clauses. In SQL I used:

[code]SELECT filename, week, dept, course, lp_dept.description, lp_weeks.year, lp_weeks.desc FROM lp_plans, lp_dept, lp_weeks WHERE username = '" .$_SESSION['MM_Username']. "' AND lp_plans.dept=lp_dept.id AND lp_plans.week=lp_weeks.id ORDER BY `timestamp` DESC"[/code]

For DataTables I am working from the Data Sources > Server Side Processing example, and was trying to do something like this:

[code]$aColumns = array( 'lp_weeks.year', 'lp_weeks.desc', 'lp_dept.description', 'lp_plans.course', 'lp_plans.filename');
$sIndexColumn = "lp_plans.ID";
$sTable = "lp_plans, lp_dept, lp_weeks";
$sWhere = "lp_plans.dept=lp_dept.ID AND lp_plans.week=lp_weeks.ID";[/code]


I honestly don't now if I am even on the right track for what I am trying to do. If anyone has some experience and can throw me a bone I would truly appreciate it!!

nm_alex

YOu could use a view, cf. http://dev.mysql.com/doc/refman/5.1/de/create-view.html

Or you use explicit JOIN syntax, column aliases and adjust $aColumns. For JOIN syntax, see http://dev.mysql.com/doc/refman/5.1/de/join.html

Some code sniplets - of course not tested:
[code]
SELECT lp_plans.filename as filename, lp_dept.description as desctiption
FORM lp_plans p
LEFT JOIN lp_dept d ON p.dept = d.id
WHERE username = $_SESSION['MM_Username']

in php code:
$aColumns = array( 'filename', 'desctiption');
[/code]

mccarville

Thanks a lot for the suggestion. What I am doing for now is using a php file to generate JSON code and using the sAjaxSource function. Works great but I am having trouble getting the individual column select filter to work with the JSON data source. Some code for MySql/PHP to JSON below. HINT: be wary of the hanging comma!

STANDARD PHP MYSQL SELECT
[code]mysql_select_db($database_con1, $con1);
$query_Recordset1 = "SELECT filename, week, dept, course, lp_dept.description, lp_weeks.year, lp_weeks.desc FROM lp_plans, lp_dept, lp_weeks WHERE username = '" .$UN. "' AND lp_plans.dept=lp_dept.id AND lp_plans.week=lp_weeks.id ORDER BY `timestamp` DESC";
$Recordset1 = mysql_query($query_Recordset1, $con1) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);[/code]

CONVERT TO JSON
[code]{ "aaData": [
<?php if ($totalRows_Recordset1 > 0) { // Show if recordset not empty ?>
<?php
$firsttime = false;
do {
if ($firsttime) { echo ", "; } ?>
["<?php echo $row_Recordset1['year'];?>",
"<?php echo $row_Recordset1['desc']; ?>",
"<?php echo $row_Recordset1['description']; ?>",
"<?php echo $row_Recordset1['course']; ?>",
"Open"
]
<?php $firsttime = true;}
while ($row_Recordset1 = mysql_fetch_assoc($Recordset1)); ?>
<?php } // Show if recordset not empty ?>
]}[/code]

The if inside the do puts a comma in front of every row except the first one, thus removing the comma at the end of the last record.

jadymitchell

Yes, it appears that the select filter will not work with an ajax source. It seems to me that one would have to wait for the table to be rendered with the ajax before creating the selects. I suspect the way to do this involves fnGetNodes, but I haven't a clue as to how to implement that.

jadymitchell

I can at least partially answer my question. To get the select filter working with ajax source, you can use the code in the example, just put it in fnInitComplete like so:
[code]
oTable = $('#example').dataTable( {
[...]
"fnInitComplete": function() {
$("thead th.addSelects").each( function ( i ) {
this.innerHTML = fnCreateSelect( oTable.fnGetColumnData(i) );
} )
}
[/code]