Welcome Guest, Not a member yet? Register   Sign In
Sorting Database columns using codeigniter
#1

[eluser]jason97673[/eluser]
So I am new to useing codeigniter and there isnt as much documentation on this as there is just plain PHP without a framework.

Well I am making a simple site (for now) that has many fields and is grabbing the records from a database. I want to be able to click on the column headers and sort them. I want to beable to have them be clicked and have it sorted DESC or ASC.

Using regular PHP I can do this but not using the codeigniter framework. Ill show you the site. http://sykotic-designz.org

When you visit the site it is a simple table with many fields and for now only 2 records. I want to beable to click name and have it sort from A-Z(then if possible have it sort the opposite direction if clicked again). Or if you click CMP, ATT, CMP % etc, have those sorted from higher to lower(then the opposite direction if possible when clicked again).

My code right now in the view is
Code:
<table border="1">
   <tr>
       <td>Name</td><td>CMP</td><td>ATT</td><td>PCT</td><td>YDS</td><td>YPA</td><td>TDs</td><td>TD%</td><td>INTs</td><td>INT%</td><td>RAT</td>
    </tr>
&lt;? foreach($query->result() as $row): ?&gt;

   <tr>
      <td>&lt;?=$row->lastname?&gt;, &lt;?=$row->firstname?&gt;</td><td>&lt;?=$row->cmp?&gt;</td><td>&lt;?=$row->att?&gt;</td><td>&lt;?=$row->cmppct?&gt;%</td>
        <td>&lt;?=$row->yds?&gt;</td><td>&lt;?=$row->ypa?&gt;</td><td>&lt;?=$row->td?&gt;</td><td>&lt;?=$row->tdpct?&gt;%</td><td>&lt;?=$row->ints?&gt;</td><td>&lt;?=$row->intpct?&gt;%</td><td>&lt;?=$row->rating?&gt;</td>
    </tr>

&lt;? endforeach; ?&gt;

The code in the controller is
Code:
class FirstDown extends Controller {
  
   function FirstDown()
   {
      parent::Controller();
   }
  
   function index()
   {
      $data['title'] = "First Down Statistics";
      $this->db->orderby("lastname", "asc");
      $data['query'] = $this->db->get('firstdown');
      $this->load->view('firstdown', $data);
   }
}

Thanks for any help.
#2

[eluser]Asinox[/eluser]
jason97673 u need Javascript for sorting.

http://www.google.com.do/search?rlz=1C1G...javascript
#3

[eluser]jason97673[/eluser]
Well I am using data from a database, I dont believe database data can be sorted using javascript. The values arent written in stone, they are in the DB, otherwise I think javascript ould do it but I dont think javascript works here?
#4

[eluser]Asinox[/eluser]
the only way to sort in database is with order_by()
#5

[eluser]jason97673[/eluser]
Yes I have no problems sorting, but I am trying to get the headers to be links that when clicked sort by that specific field in the database.
#6

[eluser]Asinox[/eluser]
try this plugin of jquery... very nice dude

http://tablesorter.com/docs/

Smile
#7

[eluser]Nicholai[/eluser]
What about something like this:

Code:
function index($sort = NULL , $direction = NULL)
   {
      $data['title'] = "First Down Statistics";
      $this->db->orderby($sort, $direction);
      $data['query'] = $this->db->get('firstdown');
      $this->load->view('firstdown', $data);
   }


Then you can pass the sort field and direction back to the controller again to reload the page with sorted results.
Code:
&lt;?php echo anchor('FirstDown/index/Name/asc','Name) ?&gt;

I'm a new guy and speculating a bit without testing, and you'll need some logic to figure out ascending vs descending, but maybe it will get you closer to what you want.
#8

[eluser]CroNiX[/eluser]
Really a javascript solution would be best here. There are many that do this and it makes no difference where the data comes from. If its in an html table, it can be sorted easily...
#9

[eluser]ramabodhi[/eluser]
i have been working diligently on getting this to work properly in one of my apps, and I've almost got it done, heres what i have so far hopefully some of the more experienced people can build on it:


This is what your controller should look like, minus all the validation and any other special stuff you need for your specific app
Code:
class Search extends Controller
{
    function Search()
    {
        parent::Controller;
        $this->load->model('search_model');
    }
    
    function Index()
    {
        $this->load->view('search_form');
    }
    function Basic($keyword = $this->input->post('keyword'), $sort_field = 'default_field', $sort_order = 'default_order' )
    {
        //Using form input to determine what fields to search in the table with $keyword
        $section = $this->input->post('section');
        //Start prepping the query
        foreach($section as $key => $tbl_field)
        {
            //For first field generate 'like' statement, the rest get 'or_like'
            if($key == 0) {$this->db->like($tbl_field, $keyword); }
            if($key > 0) { $this->db->or_like($tbl_field, $keyword); }
        }
        //Perform the query, and set the results as an array
        $query = $this->db->get('table_name');
        $result = $query->result_array;
        //Sort the Array
        $result = $this->search_model->orderBy($result, $sort_field, $sort_order);
        $data['result'] = (object)$result;  //I like to work with objects in my views
        //Load the view with the sorted search results
        $data['keyword']=$keyword;        //
        $data['sort_field']=$sort_field;  // send these to the view for sorting links
        $data['sort_order']=$sort_order   //
        $this->load->view('search_results', $data);
   }
}

Here is the Search Model
Code:
class Search_Model extends Model
{
function Search_Model()
{
    parent::Model;
}
    
function orderBy($results, $tbl_field, $order)
{    
    if($order=='asc'){
        $code = "return strnatcmp(\$a['$tbl_field'], \$b['$tbl_field']);";
        usort($results, create_function('$a,$b', $code));
        return $results; }
    elseif($order=='desc'){
        $code = "return strnatcmp(\$b['$tbl_field'], \$a['$tbl_field']);";
        usort($results, create_function('$a,$b', $code));
        return $results; }
    else return $results;
}

//This function will be used in the view, to reverse sort order upon clicking same field twice
function fieldTest($field, $curr_field, $curr_order)
{
    function orderSwap($order)
    {
        if($order == 'asc') { return 'desc'; }
        else { return 'asc'; }
    }
    if($field == $curr_field) { return orderSwap($curr_order); }
    else { return $curr_order; }
}

Then your results will be something like this
Code:
&lt;?php

<h1>Search Results</h1>
<table id="search_results">
  <tr>
    <th>&lt;?=anchor('search/basic/'.$keyword.'/title/'.$this->search_model->fieldTest('title', $sort_field, $sort_order), 'Title')?&gt;</th>
    <th>&lt;?=anchor('search/basic/'.$keyword.'/description/'.$this->search_model->fieldTest('description', $sort_field, $sort_order), 'Description')?&gt;</th>
    <th>&lt;?=anchor('search/basic'.$keyword.'/post_date/'.$this->search_model->fieldTest('post_date', $sort_field, $sort_order), 'Date')?&gt;</th>
  </tr>
&lt;?php foreach($result as $row): ?&gt;
  <tr>
    <td>$row->title</td>
    <td>$row->description</td>
    <td>$row->post_date</td>
  </tr>
&lt;?php endforeach; ?&gt;
</table>

The beauty of this is that your sorting isn't restricted to the database, you can tweak and adjust your results as you please before running them through the sort function.

Now i've got one issue, on some of my pages all of my css is disappearing when i add the 5th uri string (the sort order).. everything sorts fine, but i lose my css..

any input on the whole scheme, and any advice on solving the css problem would be appreciate
#10

[eluser]ramabodhi[/eluser]
thanks to Daniel Moore I have solved the uri/css problem (was using relative path)

Now i can verify that this method of sorting database results with links in the table headers works.

I will try to put this all into a library in the next couple days.




Theme © iAndrew 2016 - Forum software by © MyBB