Merge two tables columns in single datatable

ashishpandey

Hello everyone,
I am using DataTables 1.10.10 min version,
With this version i am able to design a simple datatable which is displaying data from single table...
Now i am using the same query to design a single datatable which is having columns from multiple table (in my case its 2 table),
I have searched online but i didnt get perfect solution or example for my problem...
Here is the sql query which i am trying to execute...
<code>
SELECT stud_personal_details.GRNO, stud_personal_details.S_FormNo, stud_personal_details.Surname,
stud_personal_details.StudentName, stud_personal_details.FatherName, stud_personal_details.MotherName,
stud_personal_details.S_Division, stud_admission_details.S_FeesPaid, stud_admission_details.S_SFeesPaid, stud_admission_details.S_TFeesPaid, stud_admission_details.Status, stud_admission_details.S_subjectTaken
FROM stud_admission_details, stud_personal_details
WHERE stud_admission_details.GRNO = stud_personal_details.GRNO
AND Status != 2
ORDER BY stud_personal_details.S_Division , stud_personal_details.GRNO
</code>

Here is the datatable code (.php file) that i have written for the above query...
<code>
$aColumns = array( 'stud_personal_details.GRNO', 'stud_personal_details.S_FormNo', 'stud_personal_details.Surname',
'stud_personal_details.StudentName', 'stud_personal_details.FatherName', 'stud_personal_details.MotherName',
'stud_personal_details.S_Division',
'stud_admission_details.S_FeesPaid', 'stud_admission_details.S_SFeesPaid', 'stud_admission_details.S_TFeesPaid',
'stud_admission_details.Status', 'stud_admission_details.S_subjectTaken' );

// Indexed column (used for fast and accurate table cardinality)
$sIndexColumn = 'GRNO';

// DB table to use
$sTable = 'stud_admission_details, stud_personal_details';
//$sGroupBy = '';

// Database connection information
$gaSql['user'] = '';
$gaSql['password'] = '';
$gaSql['db'] = '';
$gaSql['server'] = '';
$gaSql['port'] = 3306; // 3306 is the default MySQL port

// Input method (use $_GET, $_POST or $_REQUEST)
$input =& $_GET;

/** * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/**
* Character set to use for the MySQL connection.
* MySQL will return all strings in this charset to PHP (if the data is stored correctly in the database).
*/
$gaSql['charset'] = 'utf8';

/**
* MySQL connection
*/
$db = new mysqli($gaSql['server'], $gaSql['user'], $gaSql['password'], $gaSql['db'], $gaSql['port']);
if (mysqli_connect_error()) {
die( 'Error connecting to MySQL server (' . mysqli_connect_errno() .') '. mysqli_connect_error() );
}

if (!$db->set_charset($gaSql['charset'])) {
die( 'Error loading character set "'.$gaSql['charset'].'": '.$db->error );
}

/**
* Paging
*/
$sLimit = "";
if ( isset( $input['iDisplayStart'] ) && $input['iDisplayLength'] != '-1' ) {
$sLimit = " LIMIT ".intval( $input['iDisplayStart'] ).", ".intval( $input['iDisplayLength'] );
}

/**
* Ordering
*/
$aOrderingRules = array( 'stud_admission_details.S_DIVISION', 'stud_admission_details.GRNO' );
if ( isset( $input['iSortCol_0'] ) ) {
$iSortingCols = intval( $input['iSortingCols'] );
for ( $i=0 ; $i<$iSortingCols ; $i++ ) {
if ( $input[ 'bSortable_'.intval($input['iSortCol_'.$i]) ] == 'true' ) {
$aOrderingRules[] =
"".$aColumns[ intval( $input['iSortCol_'.$i] ) ]." "
.($input['sSortDir_'.$i]==='asc' ? 'asc' : 'desc');
}
}
}

if (!empty($aOrderingRules)) {
$sOrder = " ORDER BY ".implode(", ", $aOrderingRules);
} else {
$sOrder = "";
}

/**
* Filtering
* NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$iColumnCount = count($aColumns);

if ( isset($input['sSearch']) && $input['sSearch'] != "" ) {
$aFilteringRules = array();
for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
if ( isset($input['bSearchable_'.$i]) && $input['bSearchable_'.$i] == 'true' ) {
$aFilteringRules[] = "".$aColumns[$i]." LIKE '%".$db->real_escape_string( $input['sSearch'] )."%'";
}
}
if (!empty($aFilteringRules)) {
$aFilteringRules = array('('.implode(" OR ", $aFilteringRules).')');
}
}

// Individual column filtering
for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
if ( isset($input['bSearchable_'.$i]) && $input['bSearchable_'.$i] == 'true' && $input['sSearch_'.$i] != '' ) {
$aFilteringRules[] = "".$aColumns[$i]." LIKE '%".$db->real_escape_string($input['sSearch_'.$i])."%'";
}
}

if (!empty($aFilteringRules)) {
$sWhere = " WHERE ".implode(" AND ", $aFilteringRules);
} else {
$sWhere = " WHERE stud_admission_details.GRNO = stud_personal_details.GRNO";
}

/**
* SQL queries
* Get data to display
*/
$aQueryColumns = array();
foreach ($aColumns as $col) {
if ($col != ' ') {
$aQueryColumns[] = $col;
}
}

$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".implode(", ", $aQueryColumns)."
FROM ".$sTable."".$sWhere.$sOrder.$sLimit;

$rResult = $db->query( $sQuery ) or die($db->error);

// Data set length after filtering
$sQuery = "SELECT FOUND_ROWS()";
$rResultFilterTotal = $db->query( $sQuery ) or die($db->error);
list($iFilteredTotal) = $rResultFilterTotal->fetch_row();

// Total data set length
$sQuery = "SELECT COUNT(".$sIndexColumn.") FROM ".$sTable."";
$rResultTotal = $db->query( $sQuery ) or die($db->error);
list($iTotal) = $rResultTotal->fetch_row();

/**
* Output
*/
$output = array(
//"sEcho" => intval($input['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => array(),
);

while ( $aRow = $rResult->fetch_assoc() ) {
$row = array();
for ( $i=0 ; $i<$iColumnCount ; $i++ ) {
if ( $aColumns[$i] == 'version' ) {
// Special output formatting for 'version' column
$row[] = ($aRow[ $aColumns[$i] ]=='0') ? '-' : $aRow[ $aColumns[$i] ];
} elseif ( $aColumns[$i] != ' ' ) {
// General output
$row[] = $aRow[ $aColumns[$i] ];
}
}
$output['aaData'][] = $row;
}

echo json_encode( $output );
</code>

I am getting error
```Table 'syjc1617.stud_admission_details, stud_personal_details' doesn't exist
But both table exists under the database...

Can someone please help me to design the datatable which shows columns from multiple table...

ashishpandey

Hello Everyone, Will anyone give answer to my question...

ashishpandey

It has been 4 months since i posted the question, at least someone might have solution...

allan

It sounds like there is an error in the SQL that your script is generating rather than a DataTables error.

it might be worth printing out the SQL that is being generated so you can then run that directly against the database using phpMyAdmin or similar. That will help to identify what is missing - possibly a join statement.

Allan