Issue in creating datatable with codeigniter

Question

I am trying to create Datatable with CodeIgniter using these files.

In data.php(controller) which I've renamed to datatable.php I added "$this->getTable();" in function index() and only defined $aColumns and $sTable according to my need. No other changes are made.

I run the code using this URL : "localhost/codeigniter/index.php/datatable" I am new to this so still not removed the index.php and I don't use base_url so I accordingly made changes in index.php while loading scripts and css. Also I've changed the sAjaxSource to datatable/getTable and class name to Datatable as I've changed the file name.

The main problem is the execution is not entering this foreach loop. The echo statement is not executed.

foreach($rResult->result_array() as $aRow)
    {
        echo '123';
        $row = array();

        foreach($aColumns as $col)
        {
            $row[] = $aRow[$col];
        }

        $output['aaData'][] = $row;
    }

And I get the output as follows:

{"sEcho":0,"iTotalRecords":32,"iTotalDisplayRecords":"32","aaData":[]}

The aaData should display the 32 records in JSON format but it is not doing so. I am not getting where am I wrong ?

Appended: getTable() function:

public function getTable()
{
    $aColumns = array('student_id', 'exam_id', 'subject_id', 'marks_achieved');

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

    // DB table to use
    $sTable = 'marks';
    //

    $iDisplayStart = $this->input->get_post('iDisplayStart', true);
    $iDisplayLength = $this->input->get_post('iDisplayLength', true);
    $iSortCol_0 = $this->input->get_post('iSortCol_0', true);
    $iSortingCols = $this->input->get_post('iSortingCols', true);
    $sSearch = $this->input->get_post('sSearch', true);
    $sEcho = $this->input->get_post('sEcho', true);

    // Paging
    if(isset($iDisplayStart) && $iDisplayLength != '-1')
    {
        $this->db->limit($this->db->escape_str($iDisplayLength), $this->db->escape_str($iDisplayStart));
    }

    // Ordering
    if(isset($iSortCol_0))
    {
        for($i=0; $i<intval($iSortingCols); $i++)
        {
            $iSortCol = $this->input->get_post('iSortCol_'.$i, true);
            $bSortable = $this->input->get_post('bSortable_'.intval($iSortCol), true);
            $sSortDir = $this->input->get_post('sSortDir_'.$i, true);

            if($bSortable == 'true')
            {
                $this->db->order_by($aColumns[intval($this->db->escape_str($iSortCol))], $this->db->escape_str($sSortDir));
            }
        }
    }


    if(isset($sSearch) && !empty($sSearch))
    {
        for($i=0; $i<count($aColumns); $i++)
        {
            $bSearchable = $this->input->get_post('bSearchable_'.$i, true);

            // Individual column filtering
            if(isset($bSearchable) && $bSearchable == 'true')
            {
                $this->db->or_like($aColumns[$i], $this->db->escape_like_str($sSearch));
            }
        }
    }

    // Select Data
    $this->db->select('SQL_CALC_FOUND_ROWS '.str_replace(' , ', ' ', implode(', ', $aColumns)), false);
    $rResult = $this->db->get($sTable);

    // Data set length after filtering
    $this->db->select('FOUND_ROWS() AS found_rows');
    $iFilteredTotal = $this->db->get()->row()->found_rows;

    // Total data set length
    $iTotal = $this->db->count_all($sTable);

    // Output
    $output = array(
        'sEcho' => intval($sEcho),
        'iTotalRecords' => $iTotal,
        'iTotalDisplayRecords' => $iFilteredTotal,
        'aaData' => array()
    );
    foreach($rResult->result_array() as $aRow)
    {
        echo '123';
        $row = array();

        foreach($aColumns as $col)
        {
            $row[] = $aRow[$col];
        }

        $output['aaData'][] = $row;
    }
    echo json_encode($output);
}

Regarding SQL code, I've added the records manually in phpmyadmin not using queries.

Any issues in the SQL code ?

CREATE TABLE IF NOT EXISTS `marks` (
`student_id` int(10) NOT NULL,
`exam_id` varchar(10) NOT NULL,
`subject_id` int(10) NOT NULL,
`marks_achieved` int(10) NOT NULL,
KEY `student_id` (`student_id`),
KEY `exam_id` (`exam_id`),
KEY `subject_id` (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I tested the code, and it works fine as is. What is the structure of your database? Did you notice that according to the function the columns are 'id', 'first_name' and 'last_name'? — Expedito, Dec 29 '12 at 13:24
I have kept my columns as follows : $aColumns = array('student_id', 'exam_id', 'subject_id', 'marks_achieved'); And table as follows : $sTable = 'marks'; — SilentAssassin, Dec 29 '12 at 15:10
I modified the table with the columns you specified, and I modified the controller as well. After running, the JSON output was perfect. Please edit your question to post your SQL code and the entire getTable() funciton. — Expedito, Dec 29 '12 at 15:35
With phpMyAdmin, you can click on 'export' to download the SQL file. Your controller looks almost exactly like mine. Try troubleshooting your variables. You can use print_r() in the getTable() function to show your query results. You can echo your last query with $this->db->last_query(); — Expedito, Dec 29 '12 at 16:14
I swapped your getTable() function for mine. You're echoing '123' in your loop, but besides that, your code works great. You must have some problem with your database. — Expedito, Dec 29 '12 at 16:55
Right after the comment "Data set length after filtering," enter "echo $this->db->last_query();" The database query should appear on your screen. Click n the SQL tab in phpMyAdmin and paste the query in the textbox and run it. What happens? — Expedito, Dec 29 '12 at 17:06
SELECT SQL_CALC_FOUND_ROWS student_id, exam_id, subject_id, marks_achieved FROM (`marks`) LIMIT 0 The LIMIT 0 is the problem, if I change it to 10 or so records are displayed. I have the same code as I've posted where is the mistake ? — SilentAssassin, Dec 29 '12 at 19:52
See the edited comments about changing line number 35. Run that and see what happens. I don't have much time right now to try it out, so I hope that does the trick. If not, I'll have to work on it tomorrow. — Expedito, Dec 29 '12 at 20:57
If you are using codeigniter, use Ignited datatable library. It will save lot of time. And it is easy too. — Sree, Jan 04 '13 at 04:17
Yea I saw that too. But I found this one analogous to the native PHP code of datatables.net server side processing example so used it.By the way I've ended up displaying the datatables w/o server side processing now. — SilentAssassin, Jan 04 '13 at 10:05

Expedito · Accepted Answer · 2012-12-29T20:56:55.317

Create a table like the following:

CREATE TABLE IF NOT EXISTS `marks` (
  `student_id` int(10) unsigned NOT NULL,
  `exam_id` int(10) unsigned NOT NULL,
  `subject_id` int(10) unsigned NOT NULL,
  `marks_achieved` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

INSERT INTO `marks` (`student_id`, `exam_id`, `subject_id`, `marks_achieved`) VALUES
(1, 210, 340, 'really good'),
(2, 220, 440, 'super');

Edit the getTables() function to specify the columns:

$aColumns = array('student_id', 'exam_id', 'subject_id', 'marks_achieved');

Change the name off the table, editing this line:

$sTable = 'marks';

Your table probably isn't exactly like this, but I had no problem getting this to work. Here's the JSON output that was echoed:

{"sEcho":0,"iTotalRecords":2,"iTotalDisplayRecords":"2","aaData":[["1","210","340","really good"],["2","220","440","super"]]}

Change the following:

if(isset($iDisplayStart) && $iDisplayLength != '-1')

to:

if(( ! empty($iDisplayStart)) && $iDisplayLength != '-1')

It should be on or around line 35.

Thanks a lot ! Problem solved. How was your code working the isset() function and mine isn't working ? — SilentAssassin, Dec 30 '12 at 06:27
You must be running the latest version of Codeigniter. According to this link, they changed how the limit function works: http://stackoverflow.com/questions/12365432/codeigniter-specifying-limit-to-null-not-working-in-latest-ci-version-2-1-2 — Expedito, Dec 30 '12 at 10:52
Yes the limit is still causing issues. When I made the edit you said there is a problem. On the first draw all records are displayed instead of 10 which I have defined. — SilentAssassin, Dec 30 '12 at 17:28

Issue in creating datatable with codeigniter

1 Answers1

Linked