[SOLVED] Reordering a Table HELP!

[hungryOrb]

ok, a little dramatic for a simple problem, but I don't know the answer!

 

I simply need to reorder my table of printed MySQL rows, when a table header LINK is clicked like:

 

ID        Name        Type

1          Bert          Toast

3          Sal            Orange Juice

 

etc. So that if TYPE is clicked, the rows will organise themselves the other way around, because O beats T in the alphabetical order of things. At the moment, my headers are encased in <a> tags. I know that I could make a .php page for every single header, so that it organises as set, but wondering if there is an easier way!

 

Any ideas? TIA !

[Orio]

Each header should be a link to the same page, but in addition to ?sort=(header). I will give an example:

If your page is called page.php, the "Name" header should be a link to page.php?sort=Name. The Type header should be page.php?sort=Type.

Then, every time you want to display the table, you add a ORDER BY to your sql, by the column given. If nothing is given, sort by a default column like the ID. Also, you have always to keep in mind that this way of coding can cause sql-injection security holes, so use lots of validation and mysql_real_escape_string().

 

Orio.

[hungryOrb]

Hmm, trying your first suggestion, with this line:

 

<th><a href=\"viewalljobs.php?sort=Priority.\">Priority</a></th>

 

Clicking doesn't change anything

I assume then sort is a php function, and does it automatically detect the first in numerical or alphabetical characters and sort that way?

[roopurt18]

You'll also want to add an &order=the_order to your links so that you can sort in ascending or descending order.

[roopurt18]

Clicking should reload the page but it's up to you to get values out of the URL and modify your SQL query accordingly.  This is one of those things that isn't built into the language and you have to handle it yourself, manually, each and every time you wish to use it.

 

<?php

  $sort = isset($_GET['sort']) ? $_GET['sort'] : "";

  $sort = mysql_real_escape_string($sort);

  $order = isset($_GET['sort']) ? $_GET['sort'] : "ASC";

  $order = $order == "ASC" || $order == "DESC" ? $order : "";

 

  $sql = <<<MYQUERY

      SELECT * FROM table

      WHERE ...

MYQUERY;

 

  if(strlen($sort)){

    $sql .= " ORDER BY " . $sort;

    if(strlen($order)){

      $sql .= $order;

    }

  }

 

  echo $sql;

?>

[Orio]

Hmm, trying your first suggestion, with this line:

 

<th><a href=\"viewalljobs.php?sort=Priority.\">Priority</a></th>

 

Clicking doesn't change anything

I assume then sort is a php function, and does it automatically detect the first in numerical or alphabetical characters and sort that way?

 

 

No, it's not being automatically. You have to fetch the value passed thru $_GET and then add a "ORDER BY" clause to your sql with the value you passed.

As I already wrote: (with more details)

 

Then, every time you want to display the table, you add a ORDER BY to your sql, by the column given. If nothing is given, sort by a default column like the ID. Also, you have always to keep in mind that this way of coding can cause sql-injection security holes, so use lots of validation and mysql_real_escape_string().

 

 

Orio.

[hungryOrb]

Thanks guys, tho- What if that code is in a function library? If it is included, on main page, will it correspond accurately? eek.

[roopurt18]

OMG!  I forgot code tags.  /slap_self

 

Depending on how you wrote it, you could write a library function that would handle that for you.  The function would need as parameters:

 

$sql - the query without an ORDER BY clause (because the function is going to add it)

$sort_fld - the name of the sort field in $_GET

$order_fld - the name of the order field in $_GET

$fields - an array of the possible sort fields

$default - the default field to use if one can't be found in get

 

I think this should do it...

<?php
  function query_build_sort( $sql, $sort_fld, $order_fld, $fields, $default ){
    // Determine the sort field
    // $fields are the possible fields, $sort_fld is the name of the $_GET index
    if(isset($_GET[$sort_fld]) && in_array($_GET[$sort_fld], $fields)){
      $sort_fld = $_GET[$sort_fld]; // use the one in $_GET
    }else{
      $sort_fld = $default; // Not provided in $_GET, so use default
    }
    $order_fld = isset($_GET[$order_fld]) ? $_GET[$order_fld] : "ASC";
    $order_fld = $order_fld == "ASC" || $order_fld == "DESC" ? $order_fld : "ASC";

    return $sql . " ORDER BY " . $sort_fld . " " . $order_fld;
  }    
?>

[hungryOrb]

Thankyou Roopurt.

The server is playing up, something about not being synchronized with time ._o so, I'll work on it ASAP and post solved soon

thanks thanks ;]