Sorting by column headers
Moderator: General Moderators
Sorting by column headers
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.
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.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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:
It's fairly simple. I may write a tutorial for it eventually.
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 . ';';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.
Kind Regards.
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 . ';';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");- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Condensed switch statement: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");
Code: Select all
switch(trim($_REQUEST['order_by']))
{
case 'blah':
case 'blahblah':
$order_by = trim($_REQUEST['order_by']);
break;
default:
$order_by = 'default';
}- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Slighty off topic.., however I would probably stick all the sortable column names into an array instead of a switch.astions wrote:Put all your order by parameters in a switch so you don't have to worry about filtering them..
Code: Select all
$sortable = array('id', 'username', 'date');
if (isset($_GET['sort']) && in_array($_GET['sort'], $sortable)) {
$sort = $_GET['sort'];
}
else {
$sort = 'id';
}- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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.Jcart wrote:Slighty off topic.., however I would probably stick all the sortable column names into an array instead of a switch.astions wrote:Put all your order by parameters in a switch so you don't have to worry about filtering them..Seems much cleanerCode: Select all
$sortable = array('id', 'username', 'date'); if (isset($_GET['sort']) && in_array($_GET['sort'], $sortable)) { $sort = $_GET['sort']; } else { $sort = 'id'; }
The whole point was to keep request data out of the query. Array or switch, same thing.superdezign wrote:Code: Select all
$order_by = trim($_REQUEST['order_by']);
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Either way, it'd produce the same result.astions wrote:The whole point was to keep request data out of the query. Array or switch, same thing.superdezign wrote:Code: Select all
$order_by = trim($_REQUEST['order_by']);
Hi all, thanks for the help.
I'm slowly getting to understand it however the following code:
Is producing - Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING on
I have done a search and realise its something to do with a missing, or extra ';'
Kind regards.
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");Code: Select all
'asc'; if (isset($_REQUEST['orderby']))Kind regards.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Properly format your code, and then look at it.
See anything strange?
BTW, the different examples were all meant to stand on their own, not be merged together.
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']))
{BTW, the different examples were all meant to stand on their own, not be merged together.