I'm building a forum using Code Igniter framework. I'm trying to implement Datatables server side processing. I know how to use datatables and JQuery for actions such as sorting and searching, but my database table has the potential to grow to thousands and pulling all the rows at once will not make users very happy, so I'm trying to combine both server side (Code Igniter) and client side (AJAX) processing in order to use limits and offsets. So I dug into the web and found an awesome tutorial. This is what I have:
Model: Posts_model.php
<?php
var $table = 'general_posts';
var $column_order = array(null, 'title','body','username','time','category'); //set column field database for datatable orderable
var $column_search = array('title','body','username','time','category'); //set column field database for datatable searchable
var $order = array('id' => 'desc'); // default descending order
private function get_posts_query() {
$this->db->from($this->table);
$i = 0;
foreach ($this->column_search as $item)
{
if($_POST['search']['value'])
{
if($i===0) // first loop
{
$this->db->group_start();
$this->db->like($item, $_POST['search']['value']);
} else {
$this->db->or_like($item, $_POST['search']['value']);
}
if(count($this->column_search) - 1 == $i) //last loop
$this->db->group_end();
}
$i++;
}
if(isset($_POST['order'])) {
$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
} else if(isset($this->order)) {
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
function get_gen_posts($category) {
$this->get_posts_query();
if($_POST['length'] != -1)
$this->db->limit($_POST['length'], $_POST['start']);
$this->db->where(array('category' => $category, 'display' => 'true'));
$query = $this->db->get();
return $query->result();
}
function count_filtered_gen_posts($category) {
$this->get_posts_query();
$this->db->where(array('category' => $category, 'display' => 'true'));
$query = $this->db->get();
return $query->num_rows();
}
public function count_all($category) {
$this->db->where(array('category' => $category, 'display' => 'true'));
$this->db->from($this->table);
return $this->db->count_all_results();
}
} ?>
Controller: Posts.php
<?php
public function __construct() {
parent::__construct();
$this->load->model('posts_model','general_posts');
}
public function posts($category) {
$data['category'] = $category;
$this->load->view('posts', $data);
}
public function posts_ajax($category)
{
$list = $this->general_posts->get_gen_posts($category);
$data = array();
foreach ($list as $post) {
$row = array();
$row[] = $post->title;
$row[] = $post->body;
$row[] = $post->category;
$row[] = $post->poster;
$row[] = $post->time;
$data[] = $row;
}
$output = array(
"draw" => $_POST['draw'],
"recordsTotal" => $this->general_posts->count_all($category),
"recordsFiltered" => $this->general_posts->count_filtered_gen_posts($category),
"data" => $data,
);
//output to json format
echo json_encode($output);
}
} ?>
View: posts.php
<table id="table" class="table table-no-border" cellspacing="0" width="100%" style="text-align: left">
<thead>
<tr>
<th></th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<script type="text/javascript">
var table;
$(document).ready(function() {
//datatables
table = $('#table').DataTable({
"paging": true,
"pageLength" : 10,
"lengthChange": true,
"searching": true,
"info": false,
"autoWidth": true,
"ordering": false,
"stateSave": true,
"processing": true,
"serverSide": true,
"order": [], //Initial no order.
// Load data for the table's content from an Ajax source
"ajax": {
"url": "<?php echo base_url('posts/posts_ajax/'.$category)?>",
"dataType": "json",
"type": "POST",
"data":{ '<?php echo $this->security->get_csrf_token_name(); ?>' : '<?php echo $this->security->get_csrf_hash(); ?>' }
},
//Set column definition initialisation properties.
"columnDefs": [
{
"targets": [ 0 ], //first column / numbering column
"orderable": false, //set not orderable
},
],
});
});
</script>
So far, everything works as expected, but it does not entirely suit my needs. Look at these lines:
foreach ($list as $post) {
$row = array();
$row[] = $post->title;
$row[] = $post->body;
$row[] = $post->category;
$row[] = $post->poster;
$row[] = $post->time;
$data[] = $row;
}
I'd like the results of each row to display in ONE COLUMN, not in 5 colums. This is because I intend to use Bootstrap panel to display the result of each row, and customise it to suit my needs. I want something like this for each result:
Post Title: bla bla bla
The body goes here
Posted in: category name
Posted by: name of poster
Time: 9th Sep, 2017 at 10: 30PM
I would like to control how each field is displayed, with stylings and formatings, such as converting the time field into something more human-readable (eg 9th Sep, 2017 at 10: 30PM). The problem is, since the loop is created inside the controller (rather than in view, which I'm used to), I do not know how to go about doing this. I know if I can get the loop between the table body tags ( here ), I can do what I want, but I don't think AJAX will appreciate it if I did (I tried it, 'he' didn't). This is my first dabble into AJAX.
EDIT: I am wondering if there is a way to use the contents of the post_ajax function inside view so that I can put the foreach loop inside the body tag of table. So I need help. Please help! Sorry it's so long...
render. Here's the first link I found: stackoverflow.com/questions/40529987/jquery-datatable-how-to-use-render-function-get-data-from-another-column