Sorting by column headers

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
teege84
Forum Newbie
Posts: 4
Joined: Sat Jul 28, 2007 6:44 am

Sorting by column headers

Post by teege84 »

Hi all,

I have been playing around with PHP/MYSQL for a few weeks mastering the basics, however I now need to sort a table of data by column headers in ASC or DESC order depending on the column clicked, I have had a look around but cannot find a simple site that teaches me how to do it from scratch. If anyone knows of a website or the topic on this forum (I have had a look) please post.

Thanks.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Just keep it simple. Create links that send a get variable for the column and the order that you want, and then process the request. Here's a nice little example:

Code: Select all

// Create the link
echo '<a href="?orderby=date&';
echo isset($_GET['asc']) ? 'desc' : 'asc';
echo '">Date</a>';

// Validate the GET variables
$orderby = 'date';
$direction = 'asc';

if(isset($_GET['orderby']))
{
    switch($_GET['orderby'])
    {
        case 'date':
            $orderby = $_GET['orderby'];
    }
}

if(isset($_GET['desc']))
{
    $direction = 'desc';
}

// Order by the link
$query = '... whatever .... ORDER BY ' . $orderby . ' ' . $direction . ';';
It's fairly simple. I may write a tutorial for it eventually.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

Nice clean snippet! :)
teege84
Forum Newbie
Posts: 4
Joined: Sat Jul 28, 2007 6:44 am

Post by teege84 »

Hiya I have the code displaying the link, and the querystring in the tool bar but it does not seem to be sorting the data.

Code: Select all

echo '<a href="?orderby=client_surname&'; 
echo isset($_GET['asc']) ? 'desc' : 'asc'; 
echo '">Surname</a>';
// Validate the GET variables $orderby = 'client_surname'; $direction 
       'asc'; if(isset($_GET['orderby'])) { switch($_GET['orderby']) { case 'client_surname': 
      $orderby = $_GET['orderby']; } } if(isset($_GET['desc'])) { $direction = 
      'desc'; } // Order by the link $query = 'SELECT * FROM clients ORDER BY ' . 
      $orderby . ' ' . $direction . ';';
Kind Regards.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Put all your order by parameters in a switch so you don't have to worry about filtering them..

Code: Select all

if (isset($_REQUEST['order_by']))
{
    switch(trim($_REQUEST['order_by']))
    {
        case 'blah':
            $order_by = 'blah';
            break;
        case 'blahblah':
            $order_by = 'blahblah';
            break;
        default:
            $order_by = 'default';
    }

} else {
    $order_by = 'default';
}

$db->query("select * from table order by $order_by");
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

astions wrote:Put all your order by parameters in a switch so you don't have to worry about filtering them..

Code: Select all

if (isset($_REQUEST['order_by']))
{
    switch(trim($_REQUEST['order_by']))
    {
        case 'blah':
            $order_by = 'blah';
            break;
        case 'blahblah':
            $order_by = 'blahblah';
            break;
        default:
            $order_by = 'default';
    }

} else {
    $order_by = 'default';
}

$db->query("select * from table order by $order_by");
Condensed switch statement:

Code: Select all

switch(trim($_REQUEST['order_by']))
    {
        case 'blah':
        case 'blahblah':
            $order_by = trim($_REQUEST['order_by']);
            break;
        default:
            $order_by = 'default';
    }
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

astions wrote:Put all your order by parameters in a switch so you don't have to worry about filtering them..
Slighty off topic.., however I would probably stick all the sortable column names into an array instead of a switch.

Code: Select all

$sortable = array('id', 'username', 'date');
if (isset($_GET['sort']) && in_array($_GET['sort'], $sortable)) {
   $sort = $_GET['sort'];
}
else {
   $sort = 'id';
}
Seems much cleaner
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Jcart wrote:
astions wrote:Put all your order by parameters in a switch so you don't have to worry about filtering them..
Slighty off topic.., however I would probably stick all the sortable column names into an array instead of a switch.

Code: Select all

$sortable = array('id', 'username', 'date');
if (isset($_GET['sort']) && in_array($_GET['sort'], $sortable)) {
   $sort = $_GET['sort'];
}
else {
   $sort = 'id';
}
Seems much cleaner
That's something I do for flexibility in classes (as a switch statement would be too set-in-stone), but otherwise, a switch statement would be more efficient. Insignificantly so, but easy for newer programmers to understand. Besides, it isn't until they see a switch statement cascading that they truly understand the way the switch...case statements work.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

superdezign wrote:

Code: Select all

$order_by = trim($_REQUEST['order_by']);
The whole point was to keep request data out of the query. Array or switch, same thing.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

astions wrote:
superdezign wrote:

Code: Select all

$order_by = trim($_REQUEST['order_by']);
The whole point was to keep request data out of the query. Array or switch, same thing.
Either way, it'd produce the same result.
teege84
Forum Newbie
Posts: 4
Joined: Sat Jul 28, 2007 6:44 am

Post by teege84 »

Hi all, thanks for the help.

I'm slowly getting to understand it however the following code:

Code: Select all

echo '<a href="?orderby=client_surname&'; 
echo isset($_GET['asc']) ? 'desc' : 'asc'; 
echo '">Surname</a>';
$orderby = 'client_surname'; $direction 
       'asc'; if (isset($_REQUEST['orderby'])) 
{ 
switch(trim($_REQUEST['orderby'])) 
    { 
        case 'surname': 
         
            $order_by = trim($_REQUEST['orderby']); 
            break; 
        default: 
            $order_by = 'default'; 
    }

} else { 
    $order_by = 'default'; 
} 

$db->query("select * from clients order by $orderby");
Is producing - Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING on

Code: Select all

'asc'; if (isset($_REQUEST['orderby']))
I have done a search and realise its something to do with a missing, or extra ';'

Kind regards.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Properly format your code, and then look at it.

Code: Select all

echo '<a href="?orderby=client_surname&';
echo isset($_GET['asc']) ? 'desc' : 'asc';
echo '">Surname</a>';
$orderby = 'client_surname';
$direction 'asc';
if (isset($_REQUEST['orderby']))
{
    switch(trim($_REQUEST['orderby']))
    {
See anything strange?

BTW, the different examples were all meant to stand on their own, not be merged together.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I'm pretty sure there was a topic on this very subject linked from Useful Posts....
Post Reply