PHP sort ASC and DESC from database

Hello Experts!

I have a list in my database with several columns. And I want to sort them in PHP like this:

http://www..................com/users.php?sort=username

$sort = $_GET['sort'];
$sql = "SELECT * FROM users ORDER by ".$sort." DESC";
$result = mysql_query($sql) or die(mysql_error());

That works fine. But if the user clicks twice at a header for a column it should switch between ASC and DESC sort.

I'm going to implement this to a lot of files so I dont really want to change the original PHP code. I prefer to sort the data directly from the database query.

One way could be to define a variable

$int = 1;

and for every time the user sort a column:

$int++;

The problem is that this variable will disappear every page refresh.

Someone got any similar solution?

As simple as possible =)

Thank you!
LVL 3
GodDoesntExistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brad2575Commented:
you are using $_GET['sort']; to get the field to sort by, I am assuming this is a form variable?

If so you can create a hidden form variable that holds the ASC or DESC value.  then when they click the header change the value on the form PRIOR to submitting the form/refreshing the page.

Then just get the value from the hidden form variable the same way you are the "sort" field.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hube02Commented:
I would think that the easiest would be to set a session variable that keeps track of ASC/DESC somehow. Not sure exactly how your script works. But with a session, then you could do what you suggest, or something similar. I would likely do something like:

<?php
  if (!isset($_SESSION['sort']) || $_SESSION['sort'] == 'DESC') {
    $_SESSION['sort'] = 'ASC';
  } else {
    $_SESSION['sort'] = 'DESC';
  }

  $sql = "SELECT * FROM users ORDER by ".$sort." ".$_SESSION['sort'];
0
GodDoesntExistAuthor Commented:
Thank you!

Actually I'm not using the GET from a form, its from a link.

You both gave me good solutions.

I'm considering which one to use.

I will split the points your you!

Thanks again!
0
aisebastianCommented:
another way would be to send the ordering mode also by GET variables. The MySQL Query will have the value of the variable $_GET['sorttype'] as an ordering mode, and you will need to change this value when you click the table header.

Notice that you have do this check of the ordering type before you echo the table head, because you need to include it in the link of the header column:

[...] href="http://www..................com/users.php?sort=username&sorttype='. $newSortType .'" [...]

this way, when you load the page, the entries in your table will be sorted in the way specified by $_GET['sorttype'] and when you click the table header, the entries will be sorted in the other way.
// default type
$newSortType = 'DESC';
 
// change the type of ordering for including in the url of table header
if (isset($_GET['sorttype']))
{
    if ('ASC' == $_GET['sorttype'])
    {
        $newSortType = 'DESC';
    }else
    {
        $newSortType = 'ASC';
    }
}
 
// sql query will be
$sql = "SELECT * FROM `users` ORDER by ".$sort." ".$_GET['sorttype'];

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.