This is an archived forum and the content is probably no longer relevant, but is provided here for posterity.
The active forums are here.
July 15, 2010 2:51am
Subscribe [119]#16 / Oct 01, 2010 1:39pm
First of all great code! Saved me a lot of time.
Found a small bug…
In the get_ordering() function I changed:
if ($this->input->post(“iSortCol_0”)) {
TO
if ($this->input->post(“iSortCol_0”) != NULL) {
If the integer value of iSortCol_0 is 0 this line will evaluate FALSE and therefore the first column will not sort properly.
#17 / Oct 04, 2010 5:47am
thank you for your feedback. changed the code already. btw, i’m planning to make this into a library when i do get the time off and add join functions as well. i kind of eventually found out it lacked this feature as the need for it arose in my previous project.
#18 / Oct 04, 2010 5:57am
Thanks for all the work you put in so far. To make this a Library is an excellent idea.
Can I please request a feature? The possibility of adding a custom filter.
So the final query would be like this:
WHERE (<INSERT AJAX DATATABLE FILTERS HERE>) AND (<INSERT CUSTOM FILTER HERE>)
I made the changes to the code already. All additions are in bold.
public function generate($table, $columns, $index, $cfilter)
{
$sLimit = $this->get_paging();
$sOrder = $this->get_ordering($columns);
$sWhere = $this->get_filtering($columns);
$sCustomWhere = $this->get_customfiltering($sWhere, $cfilter);
$rResult = $this->get_display_data($table, $columns, $sWhere, $sCustomWhere, $sOrder, $sLimit);
$rResultFilterTotal = $this->get_data_set_length();
$aResultFilterTotal = $rResultFilterTotal->result_array();
$iFilteredTotal = $aResultFilterTotal[0]["FOUND_ROWS()"];
$rResultTotal = $this->get_total_data_set_length($table, $index, $sWhere);
$aResultTotal = $rResultTotal->result_array();
$iTotal = $aResultTotal[0]["COUNT($index)"];
return $this->produce_output($columns, $iTotal, $iFilteredTotal, $rResult);
}
protected function get_customfiltering($sWhere, $cfilter){
if($cfilter==""){return "";}
if($sWhere==”“){$sCustomWhere = "WHERE (";}
else{$sCustomWhere =" AND (";}
$sCustomWhere.=$cfilter.”) “;
return $sCustomWhere;
}
protected function get_display_data($table, $columns, $sWhere, $sCustomWhere, $sOrder, $sLimit)
{
$sql = "SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $columns) . "
FROM $table
$sWhere
$sCustomWhere
$sOrder
$sLimit
";
return $this->db->query($sql);
}
#19 / Oct 05, 2010 5:14am
hi emorling,
the query that you ask for i resolved it. I hope this code that i attached could solve your doubt.
<?php
class Datatables_model_query extends Model
{
public function __construct()
{
parent::__construct();
}
public function generate($table, $columns, $index, $joins, $where, $search, $groupby)
{
$sLimit = $this->get_paging();
$sOrder = $this->get_ordering($columns);
$sWhere = $this->get_filtering($columns,$where,$search);
$rResult = $this->get_display_data($table, $columns, $sWhere, $sOrder, $sLimit, $joins, $groupby);
$rResultFilterTotal = $this->get_data_set_length();
$aResultFilterTotal = $rResultFilterTotal->result_array();
$iFilteredTotal = $aResultFilterTotal[0]["FOUND_ROWS()"];
$rResultTotal = $this->get_total_data_set_length($table, $index, $sWhere, $joins, $where, $groupby, $columns);
if ($groupby=="")
{
$aResultTotal = $rResultTotal->result_array();
$iTotal = $aResultTotal[0]["COUNT($index)"];
}
else {
$iTotal = strval($rResultTotal);
}
$DatoSalida = $this->produce_output($columns, $iTotal, $iFilteredTotal, $rResult);
return $DatoSalida;
}
protected function get_paging()
{
$sLimit = "";
if($this->input->post("iDisplayStart") && $this->input->post("iDisplayLength") != "-1")
$sLimit = "LIMIT " . $this->input->post("iDisplayStart") . ", " . $this->input->post("iDisplayLength");
else
{
$sLimit = "LIMIT " . "0" . ", " . $this->input->post("iDisplayLength");
}
return $sLimit;
}
protected function get_ordering($columns)
{
$sOrder = "";
if($this->input->post("iSortCol_0"))
{
$sOrder = "ORDER BY ";
for($i = 0; $i < intval($this->input->post("iSortingCols")); $i++)
$sOrder .= $columns[intval($this->input->post("iSortCol_" . $i))] . " " . $this->input->post("sSortDir_" . $i) . ", ";
$sOrder = substr_replace($sOrder, "", -2);
}
return $sOrder;
}
protected function get_filtering($columns, $where, $search)
{
$sWhere="";
$TieneParentesis=0;
if ($where!="")
{
$sWhere = "WHERE ".$where;
}
if($this->input->post("sSearch") != "")
{
if ($sWhere!="")
{
$sWhere.=" AND (";
$TieneParentesis=1;
}
for($i = 0; $i < count($columns); $i++)
$sWhere .= $columns[$i] . " LIKE '%" . $this->input->post("sSearch") . "%' OR ";
$sWhere = substr_replace($sWhere, "", -3);
if ($TieneParentesis==1)
$sWhere.=")";
}
return $sWhere;
}
protected function get_display_data($table, $columns, $sWhere, $sOrder, $sLimit, $joins, $groupby)
{
$Consulta = " SELECT SQL_CALC_FOUND_ROWS " . implode(", ", $columns) . " FROM $table $joins $sWhere $groupby $sOrder $sLimit ";
$DatoSalida = $this->db->query($Consulta);
return $DatoSalida;
}
protected function get_data_set_length()
{
$DatoSalida = $this->db->query("SELECT FOUND_ROWS()");
return $DatoSalida;
}
protected function get_total_data_set_length($table, $index, $sWhere,$joins, $where, $groupby, $columns)
{
if ($groupby=="")
{
$Consulta = "SELECT COUNT(" . $index . ") FROM $table $joins $sWhere ";
$DatoSalida = $this->db->query($Consulta);
}
else
{
$ConsultaSql = "SELECT " . implode(", ", $columns) . " FROM $table $joins $sWhere $groupby ";
$Consulta = $this->db->query($ConsultaSql);
$DatoSalida = $Consulta->num_rows();
}
return $DatoSalida;
}
protected function produce_output($columns, $iTotal, $iFilteredTotal, $rResult)
{
$aaData = array();
foreach($rResult->result_array() as $row_key => $row_val)
{
foreach($row_val as $col_key => $col_val)
{
if($row_val[$col_key] == "version")
$aaData[$row_key][$col_key] = ($aaData[$row_key][$col_key] == 0)? "-" : $col_val;
else
{
switch($row_val[$col_key])
{
default: $aaData[$row_key][] = $col_val; break;
}
}
}
}
$sOutput = array
(
"sEcho" => intval($this->input->post("sEcho")),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"aaData" => $aaData
);
return json_encode($sOutput);
}
}
?>#20 / Oct 05, 2010 5:21am
Thanks! You are the best
#21 / Oct 05, 2010 5:54am
Great post and contributions.
I am at the point where have to build lots of reports and this looks like the best and easy way to accomplish it. Just found it today and I’m so happy. So I can’t wait to see the joins development.
Thanks, nick
#22 / Oct 05, 2010 6:23am
Hi ruizpi,
There is a small mistake on get_filtering. The search is not working. Here is the working updated version.
protected function get_filtering($columns, $where, $search)
{
$sWhere="";
$TieneParentesis=0;
if ($where!="")
{
$sWhere .=$where;
}
if($this->input->post("sSearch") != "")
{
if ($sWhere!="")
{
$sWhere.=" AND (";
$TieneParentesis=1;
}
for($i = 0; $i < count($columns); $i++)
$sWhere .= $columns[$i] . " LIKE '%" . $this->input->post("sSearch") . "%' OR ";
$sWhere = substr_replace($sWhere, "", -3);
if ($TieneParentesis==1)
$sWhere.=")";
}
if ($sWhere <>"")
$sWhere = "WHERE " . $sWhere;
return $sWhere;
}#23 / Oct 05, 2010 7:51am
hi nicusors,
I try to test your modification but when i use a group by select it doesn’t work. Test it.
#24 / Oct 05, 2010 8:22am
At this point my code that i share with you works fine with querys. I try to filter for 1 column like some examples that create some boxes at the bottom of the table. It doesn’t work with server side processing like my code do. Can anybody help me?
#25 / Oct 12, 2010 5:58am
hey guys, i updated the first post and added an initial draft for the “library” version… i haven’t tested it out yet so i was hoping some kind soul can do the testing for me 😛 i haven’t added joins on this one yet as im still pretty much preoccupied… i did add a zip download too if you want…
do send me feedbacks on this…
cheers
#26 / Oct 23, 2010 12:39am
I used your code and with a regular request the json file seems fine however when using a post request the following error is generated
An Error Was Encountered.
The action you have requested is not allowed.It’s my bedtime now. I’ll dive into this tomorrow or maybe someone has a solution for me when I wake up :D
#27 / Oct 23, 2010 6:58am
that’s a CI2 problem right? perhaps you could find your answer here: http://ellislab.com/forums/viewthread/163976/#786244
#28 / Oct 23, 2010 12:14pm
that’s a CI2 problem right? perhaps you could find your answer here: http://ellislab.com/forums/viewthread/163976/#786244
Thanks that was the problem…
Is there anything already that takes care of queries with joins?
#29 / Oct 23, 2010 12:40pm
that’s a CI2 problem right? perhaps you could find your answer here: http://ellislab.com/forums/viewthread/163976/#786244
Thanks that was the problem…
Is there anything already that takes care of queries with joins?
I found the joins version already on
http://ellislab.com/forums/viewthread/164729/
#30 / Nov 04, 2010 2:17am
great works cryogenix,
i’ve tried your solution integrating CI with Datatables using server side processing. however i cant display pagination. It seems like your datatables library produce table with some bugs with your css.
Im using two_button pagination, but NEXT and PREVIOUS button didnot appear. I’ve imported css file correctly.
Any idea for me to debug this?