Sorting Database columns using codeigniter

[jason97673]

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

<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>
<? foreach($query->result() as $row): ?>

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

<? endforeach; ?>

 

The code in the controller is

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.

[boo_lolly]

hey bud. I'm a little new to codeigniter as well, but I'm very familiar with PHP, so I was able to sort of understand the common tasks quickly.

 

There is a way to do this. You can do this by using the controller to locate the URI segments. for example, if you wanted to sort by name, you could have the url represent this by something like: http://sykotic-designz.org/index.php/firstdown/sort/name/asc. Then you can use uri-segments in the controller file to determine how the order links will work. (FYI: I wrote the entire URL including the name of your application as it was written in your controller file. I know that you have a router so it doesn't display the app name in the URL, but this doesn't effect the code below...)

 

This is not checked code. but something like this should work.

 

class FirstDown extends Controller {
   
   function FirstDown()
   {
      parent::Controller();
   }
   
   function index()
   {
      $data['title'] = "First Down Statistics";
      $this->db->where('id', $this->uri->segment(3));
      
      $data['query'] = $this->db->get('firstdown');
      
      if ($this->uri->segment(2) == 'sort' && $this->uri->segment(3) && $this->uri->segment(4)) {
        switch ($this->uri->segment(3)) {
            case 'name':
                $this->db->orderby("lastname", $this->uri->segment(4));
                break;
            case 'smp':
                $this->db->orderby("smp", $this->uri->segment(4));
                break;
            case 'att':
                $this->db->orderby("att", $this->uri->segment(4));
                break;
            case 'pct':
                $this->db->orderby("pct", $this->uri->segment(4));
                break;
            case 'yds':
                $this->db->orderby("yds", $this->uri->segment(4));
                break;
            case 'ypa':
                $this->db->orderby("ypa", $this->uri->segment(4));
                break;
            // case 'etc, etc etc etc...
        }
      
      $data['sort_column'] = $this->uri->segment(3);
      $data['sort_order'] = $this->uri->segment(4);
      
      }
      
      $data['query'] = $this->db->get('firstdown');
      $this->load->view('firstdown', $data);
   }
}

 

 

now, as for the links in your view... this would raise the question "How do I make them clickable to reverse the order, if it is already sorted in a particular order?" Which means, if I clicked YPA and sorted it, then how would it know that if i clicked YPA again, that it would sort it in the reverse order? Well, one way to do it is to send the current sorted column and order with the $data array. And, in your view, while you're printing the column titles, you can have an if statement to determine which order it should be sorted by if the user clicks the link. kinda like this:

 

<table border="1">
   <tr>
       <td><a href="<?php print (($sort_column == 'name' && $sort_order == 'asc') ? ('firstdown/sort/name/desc/') : ('firstdown/sort/name/asc/'); ?>">Name</a></td>
       <td><a href="<?php print (($sort_column == 'cmp' && $sort_order == 'asc') ? ('firstdown/sort/cmp/desc/') : ('firstdown/sort/cmp/asc/'); ?>">CMP</a></td>
       <td><a href="<?php print (($sort_column == 'att' && $sort_order == 'asc') ? ('firstdown/sort/att/desc/') : ('firstdown/sort/att/asc/'); ?>">ATT</a></td>
       <td><a href="<?php print (($sort_column == 'pct' && $sort_order == 'asc') ? ('firstdown/sort/pct/desc/') : ('firstdown/sort/pct/asc/'); ?>">PCT</a></td>
       <td><a href="<?php print (($sort_column == 'yds' && $sort_order == 'asc') ? ('firstdown/sort/yds/desc/') : ('firstdown/sort/yds/asc/'); ?>">YDS</a></td>
       <td><a href="<?php print (($sort_column == 'ypa' && $sort_order == 'asc') ? ('firstdown/sort/ypa/desc/') : ('firstdown/sort/ypa/asc/'); ?>">YPA</a></td>
    </tr>
etc, etc, etc, etc....

<? foreach($query->result() as $row): ?>

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

<? endforeach; ?>

 

 

I realize this isn't the most articulate explaination, but I hope you get the bulk of the idea. Like I said, I'm pretty new to codeigniter, but not new to frameworks... I've tried them all and I find codeigniter to be the best overall. Let me know if you have any more trouble or questions. I'd be glad to help you through this until you get it solid.

 

 

 

 

[jason97673]

Thanks, itll probably take me a couple hours just to understand and digest everything you said. But ill give it a try and let you know if it works 

[jason97673]

Alright im lookin at the code, and pretty much copying it right now then Ill try and understand it. But after I copied the code into the view, appears to be a syntax error "unexpected ';'" right at the first line of the new code you gave me:

 

<td><a href="<?php print (($sort_column == 'name' && $sort_order == 'asc') ? ('firstdown/sort/name/desc/') : ('firstdown/sort/name/asc/'); ?>">Name</a></td>

 

I checked it out and do not see a syntax error.

[boo_lolly]

sorry buddy I'm missing a closing parenthesis... this is what it'll look like:

 

<td><a href="<?php print (($sort_column == 'name' && $sort_order == 'asc') ? ('firstdown/sort/name/desc/') : ('firstdown/sort/name/asc/')); ?>">Name</a></td>

[jason97673]

Ok cool, I got that syntax error under control. But now something crazy is happening. Doesnt appear to be detecting the <a href=" part of the code right before the PHP which appears to be messing up the page.

[boo_lolly]

can you pot the html syntax that IS being displayed?

[jason97673]

Well viewing the source of the page after the PHP is processed, it views like this:

 

<table cellspacing="1" class="tablesorter">

<tr>
       <td><a href="<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">

<h4>A PHP Error was encountered</h4>

<p>Severity: Notice</p>
<p>Message:  Undefined variable: sort_column</p>
<p>Filename: views/firstdown.php</p>
<p>Line Number: 13</p>

</div>firstdown/sort/name/asc/">Name</a></td>
       <td><a href="<div style="border:1px solid #990000;padding-left:20px;margin:0 0 10px 0;">

<h4>A PHP Error was encountered</h4>

 

Which is kind of messed up because there isnt even any DIVs on the page. I think that the DIV came from the server adding the PHP error messages.

[boo_lolly]

I misunderstood what the problem was. I thought you weren't getting anymore code errors, but that the php wasn't printing the html properly so the page looked messed up. my bad.

 

OK I understand what's happening, and I think I know how to fix it. Here's what's happening. In your controller file, you're only sending the variables $sort_column and $sort_order if someone has clicked one of the links to sort the list... But if someone is visiting the page for the first time, and has not chosen to sort a column, then it doesn't send those variables.

 

so basically, http://sykotic-designz.org/index.php/firstdown/sort/name/asc should work... but http://sykotic-designz.org/index.php/firstdown/ will bring you an error. This means that we need to have a default value for $sort_column and $sort_order, just in case the person using the webpage hasn't yet chosen to sort the list in any particular order. We do this in the controller file. But, it's important that the default values are NOT name, cmp, att, pct, yds, etc etc... NOR can the default values be asc or desc. So we'll just set them to some string that does not apply to anything regarding the list like 'default1' and 'default2'. replace your controller with this:

 

 

<?php
class FirstDown extends Controller {
   
   function FirstDown()
   {
      parent::Controller();
   }
   
   function index()
   {
      $data['title'] = "First Down Statistics";
      $this->db->where('id', $this->uri->segment(3));
     
      if ($this->uri->segment(2) == 'sort' && $this->uri->segment(3) && $this->uri->segment(4)) {
        switch ($this->uri->segment(3)) {
            case 'name':
                $this->db->orderby("lastname", $this->uri->segment(4));
                break;
            case 'smp':
                $this->db->orderby("smp", $this->uri->segment(4));
                break;
            case 'att':
                $this->db->orderby("att", $this->uri->segment(4));
                break;
            case 'pct':
                $this->db->orderby("pct", $this->uri->segment(4));
                break;
            case 'yds':
                $this->db->orderby("yds", $this->uri->segment(4));
                break;
            case 'ypa':
                $this->db->orderby("ypa", $this->uri->segment(4));
                break;
            // case 'etc, etc etc etc...
        }
     
      }

      $data['sort_column'] = (($this->uri->segment(3)) ? ($this->uri->segment(3)) : ('default'));
      $data['sort_order'] = (($this->uri->segment(4)) ? ($this->uri->segment(4)) : ('default'));

      $data['query'] = $this->db->get('firstdown');
      $this->load->view('firstdown', $data);
   }
}
?>