Serverside code using join tables
Serverside code using join tables
Hi,
I made an enhancement that makes it possible to use join tables. It works great, including sorting, searching and filtering. The join can be defined in variable $sJoins and there is some code to identify the proper fieldnames when using 'select othertable.fieldname as fieldname'. If someone is interested I can post it here.
Regards,
Rubyan
I made an enhancement that makes it possible to use join tables. It works great, including sorting, searching and filtering. The join can be defined in variable $sJoins and there is some code to identify the proper fieldnames when using 'select othertable.fieldname as fieldname'. If someone is interested I can post it here.
Regards,
Rubyan
This discussion has been closed.
Replies
I'm looking for a way to use join in datatables. Would very much appreciate if you could post this here.
Thanks in advance!
Rgds,
Ced
However when I try to add a second INNER JOIN I get an error. I think it has something to do with needing to state the table and column in the aColumns array such as 'table1.table_id' When I try to add the table to any of those columns it errors. I'm guessing the way it explodes the array. I'll play with it some more and see if I can figure it out. If so I'll report back.
-Twitch
[code]SELECT
A.*,
B.option AS element_4_value,
C.option AS element_22_value
FROM
(ap_form_3 AS A
LEFT JOIN ap_element_options AS B
ON A.element_4=B.option_id AND B.element_id=4 AND B.form_id=3)
LEFT JOIN ap_element_options AS C
ON A.element_22=C.option_id AND C.element_id=22 AND C.form_id=3";[/code]
you can specify the database field name in datatables by setting the sName on each column. I believe you will be able to set "sName": "a.id" and such, or manage to set up the aColumns using fully qualified table.field values. personally I prefer to build the aColumns from the sColumns passed into the server script by datatables. see http://tote-magote.blogspot.com/2011/08/serverprocessingphp-for-datatables.html just under heading "The code"
using "AS" in aColumns is a little more complicated. when I have needed "AS" in my queries, I've written code to detect specific fields. There are a couple places you need to be careful, such as not using "AS" clauses in sWhere, when you have sSearch_i values, or the ORDER BY clause.
In the code below you can find the new $sJoin variable. Hope I can help someone with this code.
[code]
function server_processing(){
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/
$aColumns = array(
'regina_dslams.id', // 0
'regie_prio', // 1 etc
'regie_dslam',
'title', //'regie_dslam_type_id',
'regie_enummer',
'regie_uitgifte_bsse', //DATE_FORMAT(regie_uitgifte_bsse,"%m-%d")',
'regie_commissioning',
'regie_tti',
'regie_opm',
'iptv_status.name AS iptv_status', //'regie_status_iptv',
'wba_status.name AS wba_status', //regie_status_wba',
'regina_dslams.created AS created',
'regina_dslams.updated AS updated',
'u.username AS username'
);
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "regina_dslams.id";
/* DB table to use */
$sTable = "regina_dslams";
// Joins
$sJoin = 'LEFT JOIN users u ON u.id = regina_dslams.user_id ';
$sJoin .= 'LEFT JOIN regina_dslam_types ON regina_dslam_types.id = regina_dslams.regie_dslam_type_id ';
$sJoin .= 'LEFT JOIN regina_statuses iptv_status ON iptv_status.id = regina_dslams.regie_status_iptv_id ';
$sJoin .= 'LEFT JOIN regina_statuses wba_status ON wba_status.id = regina_dslams.regie_status_wba_id ';
// get the database credentials from the configfile
$database = new DATABASE_CONFIG;
$db = get_class_vars(get_class($database));
/* MySQL connection */
$gaSql['user'] = $db['default']['login'];
$gaSql['password'] = $db['default']['password'];
$gaSql['db'] = $db['default']['database'];
$gaSql['server'] = $db['default']['host'];
// for html links
App::import('Helper', 'Html');
$html = new HtmlHelper();
// get or post
$_METHOD = $_POST;
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );
mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );
$sLimit = "";
if ( isset( $_METHOD['iDisplayStart'] ) && $_METHOD['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_METHOD['iDisplayStart'] ).", ".
mysql_real_escape_string( $_METHOD['iDisplayLength'] );
}
if ( isset( $_METHOD['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array(),
"sQuery" => $sDataQuery
);
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $itruncate( $aRow[ $arr[2] ], 20);
}
else if ( $aColumns[$i] == "version" )
{
/* Special output formatting for 'version' column */
$row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
else if ( $aColumns[$i] != ' ' )
{
/* General output */
if($aColumns[$i]=="regina_dslams.id") {
$row[] = $html->link('edit', '/regina_dslams/edit/'.$aRow[ 'id' ] );
} else {
$row[] = $this->truncate($aRow[ $aColumns[$i] ], 20); // truncate long results
}
}
}
$output['aaData'][] = $row;
}
echo json_encode( $output );
exit();
}
[/code]
SELECT leads.lead_id, leads.date_time, clients.username, courses.course_type, courses.location_name, CONCAT(first_name,' ',surname), leads.first_name, leads.surname, leads.email, leads.phone, CONCAT(country,'/',area), leads.enquiry, leads.checked_by_admin, leads.ip_address
FROM courses
LEFT JOIN leads ON leads.lead_get_course_id = courses.course_id
LEFT JOIN clients ON clients.client_id = courses.course_get_client_id
WHERE checked_by_admin = 'No'
I have a problem with the script server-processing.
He displays the following error:
- Undefined index: in sEcho on line 175
- Notice: Undefined index: in type_charge.id on line 194
- Notice: Undefined index: in societe.id on line 194
Please I need help
[code]
<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array(
'nom_prenom',
'type_charge.id',
'societe.id',
'societe.libelle',
'famille.code',
'famille.libelle',
'num_facture',
'num_cheque',
'montants',
'charge.libelle',
'mode_de_prelevement',
'annuler'
);
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "charge.id_charge";
/* DB table to use */
$sTable = "charge";
// Joins
$sJoin = 'LEFT JOIN famille ON famille.code = charge.code_famille ';
$sJoin .= 'LEFT JOIN utilisateur ON utilisateur.id = charge.id_utilisateur ';
$sJoin .= 'LEFT JOIN type_charge ON type_charge.id = famille.id_type ';
$sJoin .= 'LEFT JOIN mode_de_prelevement ON mode_de_prelevement.id = charge.mode_de_prelevement ';
$sJoin .= 'LEFT JOIN societe ON societe.id = charge.id_societe ';
/* Database connection information */
$gaSql['user'] = "root";
$gaSql['password'] = "root";
$gaSql['db'] = "charge";
$gaSql['server'] = "localhost";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* MySQL connection
*/
$gaSql['link'] = mysql_connect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );
mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}
/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i
[/code]
$row[] = $this->truncate( $aRow[ $arr[2] ], 20);
"$this->truncate"
[quote]$sJoin = ' LEFT JOIN data on data.phone=calls.phone ';[/quote] it gets stuck. The weird thing is that I'm not even including any columns from the data table yet. Here are the lines I changed:
The columns:
[code]
$aColumns = array('calls.phone', 'calls.date', 'roaming', 'SUM(call_duration)', 'overseas' );
[/code]
Table:
[code]
$sTable = "calls";
[/code]
The join addition:
[code]
$sJoin = ' LEFT JOIN data on data.phone=calls.phone';
[/code]
And the query:
[code]
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sJoin
$sWhere
$sGroup
$sOrder
$sLimit
";
[/code]
Before adding the JOIN portion, the script worked perfectly, and the only thing I modified were the lines above. I checked my code with that of rubyan above and the only difference was that I don't have the lines parsing the "AS" clauses. I don't use any so that can't be it either.
Any help would be much appreciated. Thanks in advance.
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/
/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( 'cc_call.id', 'cc_call.username', 'cc_reseller_call.resellerid', 'cc_reseller_call.id_call');
/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "cc_call.id";
/* DB table to use */
$sTable = "cc_call";
//for join
$sJoin = "inner JOIN cc_reseller_call on cc_call.id =cc_reseller_call.id_call";
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/
/*
* Local functions
*/
function fatal_error ( $sErrorMessage = '' )
{
header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
die( $sErrorMessage );
}
/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
intval( $_GET['iDisplayLength'] );
}
/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i