Page 1 of 1
Sorting by column headers
Posted: Sat Jul 28, 2007 7:06 am
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.
Posted: Sat Jul 28, 2007 9:07 am
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.
Posted: Sat Jul 28, 2007 9:44 pm
by califdon
Nice clean snippet!

Posted: Sun Jul 29, 2007 11:13 am
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.
Posted: Sun Jul 29, 2007 4:21 pm
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");
Posted: Sun Jul 29, 2007 6:47 pm
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';
}
Posted: Sun Jul 29, 2007 6:53 pm
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
Posted: Sun Jul 29, 2007 7:09 pm
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.
Posted: Sun Jul 29, 2007 7:38 pm
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.
Posted: Sun Jul 29, 2007 7:52 pm
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.
Posted: Mon Jul 30, 2007 6:22 am
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.
Posted: Mon Jul 30, 2007 8:06 am
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.
Posted: Mon Jul 30, 2007 2:25 pm
by feyd
I'm pretty sure there was a topic on this very subject linked from Useful Posts....