Serverside code using join tables

rubyan

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

cedlimed

Hi Rubyan,

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

twitch

Would love to see how you did it as well, rubyan. I can do one join by simply adding $sJoin = "INNER JOIN table2 ON (table1.table_id = table2.table_id)" and then putting $sJoin variable under FROM $sTable

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

Driver

And what if my sql query is more complicated? I have a problem with A.*, which returns all columns from database, but not from array ($aColumns).

[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]

fbas

In datatables, you need to specify the number of columns (for your anyway). I suppose if the number of columns in a.* + b.options + c.options is the same, there's no issue, but would it hurt to enumerate the columns in a (i.e. set up an aColumns array or just write out all the fields in your SQL)?

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.

rubyan

Hi all, thanks for your interest in my code. I use the CakePHP framework, it speeds up the development while keeping the code organized in MVC.

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]

midjam

would this code be able to handle a query like this?

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'

ridjal974

Hi,

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]

juamcar

Hola el problema me sale Fatal error: Using $this when not in object context in

$row[] = $this->truncate( $aRow[ $arr[2] ], 20);

"$this->truncate"

smalone

I know this post is a little late, but I'm having an issue adding the JOIN to my query. It seems to just lock up. Without the join I can load about 400k+ rows in less than a second. When I add the line
[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.

anilmcmt

///-------------------------------------------------------------------------------------------------------------------
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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

anilmcmt

here this code return the null

anilmcmt

why please solution this problems