Sort php table by column name - help!!

I have a php generated table and want to be able to sort the data (asc/desc) by clicking on the column title. In the followling script only the first 4 columns (iname, itype, cname and mlvl) behave correctly. Can anyone see what I have done wrong. Many thanks.


 <html>

<head>

<title>Sort Table</title>

</head>

<body>





<?php

$host = '64.38.4.74:3306' ;
$user = 'Richard' ;
$pass = 'fido' ;
$db = 'eq2' ;

$link = mysql_connect ($host,$user,$pass) or die ( mysql_error ());
mysql_select_db ($db)or die ( mysql_error ());

// Sorting by selection.  
$key_column = isset($_GET['sort_by']) ? intval($_GET['sort_by']) : 1;

$db_columns_list = array(1 => "iname", 2 => "itype", 3 => "cname", 4 => "mlvl", 5 => "strength", 6 => "stamina", 7 => "agility",
                  8 => "wisdom", 9 => "intelligence", 10 => "health", 11 => "power", 12 => "heat_resist", 13 => "cold_resist",
                  14 => "magic_resist", 15 => "mental_resist", 16 => "divine_resist", 17 => "disease_resist", 18 => "poison_resist",
                  19 => "slash_resist", 20 => "piercing_resist", 21 => "crush_resist", 22 => "flowing_thought", 23 => "mitagation",
                  24 => "defence", 25 => "disruption", 26 => "ministration", 27 => "ordination", 28 => "subjugation", 29 => "focus",
                  30 => "proc", 31 => "added");

$sort_by = $db_columns_list[$key_column];

// Selecting order.
$order_by_str = (isset($_GET['order_by']) && intval($_GET['order_by']) == 1) ? "ASC" : "DESC";

// Convert the order_by to integer so as to navigate.
$order_by = (strtolower(trim($order_by_str)) == "asc") ? -1 : 1;

// Default ordering.
if(!isset($_GET['sort_by']) && !isset($_GET['order_by']))
{
     $sort_by      = "cname";
     $order_by_str = "asc";    
}

if(isset($_GET['']))
{
    $query = "SELECT * FROM equipment where cname ='" . mysql_real_escape_string($selected_genre) . "' ORDER BY $order_by_str";
}
else
{
     $query = "SELECT * FROM equipment ORDER BY $sort_by $order_by_str";
}

$result = mysql_query($query) or die("Sql error: " . mysql_error());

$num = mysql_num_rows($result);

mysql_close();

?>

<p align="center"><table cellpadding="3" border="1" cellspacing="3">
     <tr>
        <td><b><a href="?sort_by=1&order_by=<?php echo $order_by; ?>">Item Name:</a></b></td>
        <td><b><a href="?sort_by=2&order_by=<?php echo $order_by; ?>">Item Type:</a></b></td>
        <td><b><a href="?sort_by=3&order_by=<?php echo $order_by; ?>">Character Name:</a></b></td>
        <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Level:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Str:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Sta:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Agi:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Wis:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Int:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Health:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Power:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Heat:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Cold:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Magic:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Mental:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Divine:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Disease:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Poison:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Slash:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Piercing:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Crush:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">FT:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Mit:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Defence:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Disruption:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Ministration:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Ordination:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Subjugation:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Focus:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Proc/Effect:</a></b></td>
            <td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Date Added:</a></b></td>
     </tr>


<?php

$i=0;
while ($i < $num)
{
           $iname = mysql_result($result, $i, "iname");
           $itype = mysql_result($result, $i, "itype");
           $cname = mysql_result($result, $i, "cname");
           $mlvl = mysql_result($result, $i, "mlvl");
      $strength = mysql_result($result, $i, "strength");
           $stamina = mysql_result($result, $i, "stamina");
           $agility = mysql_result($result, $i, "agility");
           $wisdom = mysql_result($result, $i, "wisdom");
      $intelligence = mysql_result($result, $i, "intelligence");
           $health = mysql_result($result, $i, "health");
           $power = mysql_result($result, $i, "power");
           $heat_resist = mysql_result($result, $i, "heat_resist");
      $cold_resist = mysql_result($result, $i, "cold_resist");
           $magic_resist = mysql_result($result, $i, "magic_resist");
           $mental_resist = mysql_result($result, $i, "mental_resist");
           $divine_resist = mysql_result($result, $i, "divine_resist");
      $disease_resist = mysql_result($result, $i, "disease_resist");
           $poison_resist = mysql_result($result, $i, "poison_resist");
           $slash_resist = mysql_result($result, $i, "slash_resist");
           $piercing_resist = mysql_result($result, $i, "piercing_resist");
      $crush_resist = mysql_result($result, $i, "crush_resist");
           $flowing_thought = mysql_result($result, $i, "flowing_thought");
      $mitagation = mysql_result($result, $i, "mitagation");
           $defence = mysql_result($result, $i, "defence");
      $disruption = mysql_result($result, $i, "disruption");
           $ministration = mysql_result($result, $i, "ministration");
      $ordination = mysql_result($result, $i, "ordination");
           $subjugation = mysql_result($result, $i, "subjugation");
      $focus = mysql_result($result, $i, "focus");
           $proc = mysql_result($result, $i, "proc");
      $added = mysql_result($result, $i, "added");      
     
?>

 <tr>
           <td><?php echo $iname ?></td>
           <td><?php echo $itype ?></td>
           <td><?php echo $cname ?></td>
           <td><p align="center"><?php echo $mlvl ?></p></td>
      <td><p align="center"><?php echo $strength ?></p></td>
      <td><p align="center"><?php echo $stamina ?></p></td>
      <td><p align="center"><?php echo $agility ?></p></td>
      <td><p align="center"><?php echo $wisdom ?></p></td>
      <td><p align="center"><?php echo $intelligence ?></p></td>
      <td><p align="center"><?php echo $health ?></p></td>
      <td><p align="center"><?php echo $power ?></p></td>
      <td><p align="center"><?php echo $heat_resist ?></p></td>
      <td><p align="center"><?php echo $cold_resist ?></p></td>      
           <td><p align="center"><?php echo $magic_resist ?></p></td>
      <td><p align="center"><?php echo $mental_resist ?></p></td>
      <td><p align="center"><?php echo $divine_resist ?></p></td>
      <td><p align="center"><?php echo $disease_resist ?></p></td>
      <td><p align="center"><?php echo $poison_resist ?></p></td>
      <td><p align="center"><?php echo $slash_resist ?></p></td>
      <td><p align="center"><?php echo $piercing_resist ?></p></td>
      <td><p align="center"><?php echo $crush_resist ?></p></td>
      <td><p align="center"><?php echo $flowing_thought ?></p></td>
      <td><p align="center"><?php echo $mitagation ?></p></td>
      <td><p align="center"><?php echo $defence ?></p></td>
      <td><p align="center"><?php echo $disruption ?></p></td>
      <td><p align="center"><?php echo $ministration ?></p></td>
      <td><p align="center"><?php echo $ordination ?></p></td>
      <td><p align="center"><?php echo $subjugation ?></p></td>      
           <td><p align="center"><?php echo $focus ?></p></td>
      <td><p align="center"><?php echo $proc ?></p></td>
      <td><p align="center"><?php echo $added ?></p></td>
      </tr>

<?php

     $i++;
}
echo "</table></p>";


?>


</body>

</html>
BlueoakcapitalAsked:
Who is Participating?
 
VoteyDiscipleConnect With a Mentor Commented:
Oh, also: instead of that giant list of mysql_result($result, $i, 'column_name') ... how about:
$row = mysql_fetch_assoc($result);
extract($row);


Or, for that matter, don't bother with extract() and just access columns by $row['column_name']
0
 
akshah123Commented:
That's because in your code for the header values ...

<td><b><a href="?sort_by=4&order_by=<?php echo $order_by; ?>">Str:</a></b></td>

have sort_by=4 for every column for all the rest of the columns.  Please update sort_by= to a proper value for each column as in your array.  
0
 
BlueoakcapitalAuthor Commented:

OK, did that and I'm half way there. Each colunm now sorts on clicking the column title, the problem it only sorts one way. I need it to sort both asecending and decending on title click.(ie. Click once to sort one way, click again to sort column the other way..)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
akshah123Commented:
change

// Default ordering.
if(!isset($_GET['sort_by']) && !isset($_GET['order_by']))
{
     $sort_by      = "cname";
     $order_by_str = "asc";    
}

to just

if(!isset($_GET['sort_by']) && !isset($_GET['order_by']))
{
     $sort_by      = "cname";
}
0
 
akshah123Commented:
Also, in the future, please do not post your login information in this forum.  It is not required to debug your situation and is a huge security issue as any one can read your posts.

I STRONGLY SUGGEST THAT YOU CHANGE YOUR PASSWORD AT THIS POINT.



 
0
 
BlueoakcapitalAuthor Commented:
Thanks (password changed) !!

Still only sorts one way........
0
 
VoteyDiscipleCommented:
You need a mechanism to switch the sort order to DESC if it already IS ascending.  Each of your columns should look something like:

          <td><b><a href="?sort_by=4&amp;order_by=<?= $order_by; ?>&amp;sort_direction=<?= ($sort_by_index == 4) : $other_direction : 'ASC' ?>">Cold:</a></b></td>

Here you're explicitly adding a sort_direction to every query.  If the CURRENT sort column is the same as the column the user clicks, the direction becomes $other_direction (which we'll have to define in a moment); otherwise it will be ASC.

That means the code at the top should look something like:

$sort_by_index = $_GET['sort_by'];
$sort_direction = ($_GET['sort_direction'] == 'ASC') ? 'ASC' : 'DESC';
// (This explicit comparison is to avoid SQL injection)

$other_direction = ($sort_direction == 'ASC') ? 'DESC' : 'ASC;


With those fields, and the changes to each column, you should be ok.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.