Multiple databases

mmcw

I am using 1.9.4 with serverside database access.

[code]

$query = "SELECT ccCubeCart_customer.customer_id, status, cart_order_id, time, title, firstName, lastName, ccCubeCart_order_sum.campaign, ccCubeCart_customer.country, postcode_d, country_d, noOrders, language, comments_customers, ip, prod_total, shipMethod, tracking_no, gateway, comments, shipMethod_code, ccCubeCart_customer.email FROM ccCubeCart_order_sum INNER JOIN ccCubeCart_customer ORDER BY time DESC";
$results = $db->select($query);

[/code]

how to add this to the example from:

http://datatables.net/release-datatables/examples/data_sources/server_side.html

???
The data will be taken from three databases!

[code]
$aColumns = array( '????' );

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "cart_order_id";

/* DB table to use */
$sTable = "ccCubeCart_order_sum";

$sWhere = "";


[/code]


Greetings Michel

girishmr

Here is the server side script using joins. I assume multiple databases as multiple databases (MySql, instance 1, instance 2 etc) and not multiple tables using joins.)

But still....

I have a connection class which handles the connections and I have extended it. Instead you can remove the "extend" keyword and add "require_once('your_conx_class/file.php").

Note: This uses PDO to connect to the database and assumes MySQL DB. Implementation is given below this script. Please make sure you do not have singleton connection class so that you can connect to different databases. This as of now is not connecting to multiple DB's but can be modified as per your needs.

[code]

<?php

class DataTable extends Connection
{

protected $_sTable;
protected $_aColumns = array();
protected $_sJoin = '';
protected $_sWhere = '';
protected $_sGroupBy = '';
protected $_sIndexColumn = '';
protected $_iFilteredTotal;
protected $_iTotal;
protected $_sResult;
protected static $_dbh;

public function __construct($sTable, array $aColumns, $sJoin = '', $sWhere = '', $sGroupBy = '', $sIndexColumn = '') {
//This is PDO object for database connection.
if(!isset(self::$_dbh)) self::$_dbh = self::getConInstance();

$this->_sTable = $sTable;
$this->_aColumns = $aColumns;
if($sJoin != '') $this->_sJoin = $sJoin;
if($sWhere != '') $this->_sWhere = $sWhere;
if($sGroupBy != '') $this->_sGroupBy = $sGroupBy;
if($sIndexColumn != '') $this->_sIndexColumn = $sIndexColumn;
$this->sQuery();
}

protected function sLimit() {
if ( isset( $_POST['iDisplayStart'] ) && $_POST['iDisplayLength'] != '-1' ) {
$sLimit = ' LIMIT ' . intval($_POST['iDisplayStart']) . ', ' . intval($_POST['iDisplayLength']);
} else {
$sLimit = '';
}
return $sLimit;
}

protected function sOrder() {
$sOrder = '';
if ( isset( $_POST['iSortCol_0'] ) ) {
$sOrder = ' ORDER BY ';
for ( $i=0 ; $i_aColumns[ intval( $_POST['iSortCol_'.$i] ) ].' '.$_POST['sSortDir_'.$i] .', ';
}
}
$sOrder = substr_replace( $sOrder, '', -2 );
if ( $sOrder == ' ORDER BY' ) {
$sOrder = '';
}
}
return $sOrder;
}

protected function sWhere() {
$sWhere = '';
if ( $_POST['sSearch'] != '' ) {
$sWhere = ' WHERE (';
for ( $i=0 ; $i_aColumns) ; $i++ ) {
if ( $_POST['bSearchable_'.$i] == "true" ) {
$sWhere .= $this->_aColumns[$i]." LIKE '%". $_POST['sSearch'] ."%' OR ";
}
}
$sWhere = substr_replace( $sWhere, "", -3 );
$sWhere .= ')';
}
/* Conditions */
if($this->_sWhere != '') {
if($sWhere != '') {
$sWhere .= ' AND '.$this->_sWhere;
} else {
$sWhere .= ' WHERE '.$this->_sWhere;
}
}
/* Individual column filtering */
for ( $i=0 ; $i_aColumns) ; $i++ ) {
if ( $_POST['bSearchable_'.$i] == "true" && $_POST['sSearch_'.$i] != '' ) {
if ( $sWhere == '' ) {
$sWhere = 'WHERE ';
} else {
$sWhere .= ' AND ';
}
$sWhere .= $this->_aColumns[$i]." LIKE '%".$_POST['sSearch_'.$i]."%' ";
}
}
return $sWhere;
}

protected function sTables(){
if(is_array($this->sTable)){
$this->s_Tables = implode(", ", $this->sTable);
} else {
$this->s_Tables = $this->sTable;
}
return sFrom;
}

protected function sQuery() {
$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $this->_aColumns)).' FROM '.
$this->_sTable.
$this->_sJoin.
$this->sWhere().
$this->_sGroupBy.
$this->sOrder().
$this->sLimit();

$statement = self::$_dbh->query($sQuery);
$this->_sResult = $statement->fetchAll(PDO::FETCH_NUM);
/* Data set length after filtering */
$sQuery = 'SELECT FOUND_ROWS()';
$statement = self::$_dbh->query($sQuery);
$aResultFilterTotal = $statement->fetchAll(PDO::FETCH_NUM);
$this->_iFilteredTotal = $aResultFilterTotal[0][0];
/* Total data set length */
$sQuery = 'SELECT COUNT('.$this->_sIndexColumn.') FROM '.$this->_sTable;
$statement = self::$_dbh->query($sQuery);
$aResultTotal = $statement->fetchAll(PDO::FETCH_NUM);
$this->_iTotal = $aResultTotal[0][0];
return $this;
}

public function aaData() {
$output = array(
"sEcho" => intval($_POST['sEcho']),
"iTotalRecords" => $this->_iTotal,
"iTotalDisplayRecords" => $this->_iFilteredTotal,
"aaData" => array()
);
return $output;
}

public function sResult() {
return $this->_sResult;
}
}

?>
[/code]

This is how you implement it.

[code]
$sTable = '';
$dbColumns = array(
"table.col1", "table.col2", "table.col3" ...
);

$sIndexColumn = 'your_id_col';
$sJoin = " your_join ";
$sJoin .= " your_another_join";
$sJoin .= " your_yet_anpother_join";

$sWhere = '';
$sGroupBy = '';

$oDataTable = new DataTable($sTable, $dbColumns, $sJoin, $sWhere, $sGroupBy, $sIndexColumn);

$data = $oDataTable->aaData();
$sResult = $oDataTable->sResult();

foreach($sResult as $key => $value) {

$data['aaData'][$key] = $value;
}

print(json_encode($data));

[/code]

Hope this helps.