[SOLVED] How can I tell which way my database is sorting?

[piznac]

OK I did some searching on this and between being lazy and not really seeing an answer here I am

 

Ive got some mysql results,.. and I have wrote a simple script to sort them by column headers. Little link and url var to tell the sql which way to sort. My problem is how to I go about assigning ASC or DESC? Now that seemed real simple to me,. but then the question came up. How is it sorted in the first place?

 

If I could find out how it is sorted then it would be fairly simple to do an if statement to change the var in the ORDER BY clause. Anyone know how to tell which way the results are sorted to begin with?

[trq]

Anyone know how to tell which way the results are sorted to begin with?

 

Sorry, but your question is a little unclear. The results are not sorted to begin with. You can see how your results will come out if not run with an ORDER BY statement by simply using...

 

SELECT fld FROM tbl;

 

Then... to sort by a specific field use....

 

SELECT fld FROM tbl ORDER BY fld DESC;

[piznac]

Ok but if I wanted to change the ASC or DESC by a URL variable. How would I go about that.

[trq]

<?php

  if (isset($_GET['order'])) {
    $order = mysql_real_escape_string($_GET['order']);
    $sql = "SELECT fld FROM tbl ORDER BY $order;";
  }

?>

[piznac]

Ok yes,. I have that. Now what if I wanted to also sort by ASC or DESC. Like if it is sorted by column A upon clicking the link by that column and by ASC. Then when clicking the link again it would be sorted by the column and by DESC.

 

<?php

  if (isset($_GET['order'])) {
    $order = mysql_real_escape_string($_GET['order']);
    $sort = mysql_real_escape_string($_GET['sort']);
    $sql = "SELECT fld FROM tbl ORDER BY $order $sort";
  }

?>

 

Sort being either ASC or DESC depending on how many times the link is clicked or the sort in the first place. I hope this makes sense and sorry if Im over confusing it.

[trq]

Something like?

 

<?php

  if (isset($_GET['order'])) {
    $order = mysql_real_escape_string($_GET['order']);
    $sql = "SELECT fld FROM tbl ORDER BY $order $sort";

    if ($order == 'desc') {
      $order = 'asc';
    } else {
      $order = 'desc';
    }
    echo "<a href='?order=$order'>sort $order</a>";

  }

?>

[piznac]

Thats funny,. becuase thats exactly what I came up with. I just figured it had to be more complicated then that lol. I'll continue with that and see what I get. Thanks for the help Thorpe

[trq]

I just figured it had to be more complicated then that lol.

 

The simplest solution is usually the best.

[piznac]

Ok now Im really confused. How would I define my variable then? Like $sort which would come from the link:

 

<a href=\"$_SERVER[php_SELF]?order=or_date&n=$SESSION[MM_Username]&$sort=ASC\">Order Date$pos</a>

 

So like this the results will always be DESC because the $sort var is always ASC. See what Im saying?

[trq]

How would I define my variable then?

 

Dont hard code it into the link. You can actually run this script, it will echo your query so you can see whats going on.

 

<?php

  if (isset($_GET['order'])) {
    $order = mysql_real_escape_string($_GET['order']);
  } else {
    $order = 'desc'; // set a default.
  }

  $sql = "SELECT fld FROM tbl ORDER BY $order";
  // just here for debugging, remove later.
  echo $sql."<br />";

  // run your query here.

  // form your link.
  if ($order == 'desc') {
    $order = 'asc';
  } else {
    $order = 'desc';
  }
  echo "<a href='?order=$order'>sort $order</a>";

?>

[piznac]

Ahh the perverbial lightbulb comes on ,.. ok I think I get it now. Again thanks.

 

Im not going to post the finished code as there is way to much that has nothing to do with this. But yeah this works!