Welcome Guest, Not a member yet? Register   Sign In
Ignited DataTables
#11

[eluser]ruizpi[/eluser]
hi, cryogenix. I discovered the problem that i have and i comment you, because it can happen to all. The code was correct. The problem was that my table in database have some fields with null value and the datatables version does´nt let null values. I changed the version of datatables and i discovered that my code worked well. it suppose a great surprising. Now i try to use querys instead of tables. Have you got any reference about it?.
#12

[eluser]cryogenix[/eluser]
I'm not really sure what reference you are talking about but I did update the model regarding manipulation of data results and adding of other columns. I hope that can help you and others as well..
#13

[eluser]ruizpi[/eluser]
The reference that i was talking is about how use a server side php for codeigniter using querys instead of tables. I modified a code that i founded and now i have codeigniter working with server side tables and querys.
#14

[eluser]emorling[/eluser]
If you get stuck with json_encode() returning null values, due to non-utf characters, then simply change this row.

FROM:

$aaData[$row_key][] = $col_val;


TO:


$aaData[$row_key][] = utf8_encode($col_val);
#15

[eluser]emorling[/eluser]
If you get stuck with json_encode() returning null values, due to non-utf characters, then simply change this row.

FROM:

$aaData[$row_key][] = $col_val;


TO:


$aaData[$row_key][] = utf8_encode($col_val);
#16

[eluser]Unknown[/eluser]
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

[eluser]cryogenix[/eluser]
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

[eluser]emorling[/eluser]
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

[eluser]ruizpi[/eluser]
hi emorling,

the query that you ask for i resolved it. I hope this code that i attached could solve your doubt.

Code:
&lt;?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);
    }
  }
?&gt;
#20

[eluser]emorling[/eluser]
Thanks! You are the best




Theme © iAndrew 2016 - Forum software by © MyBB