Solved

Sort php table by column name - help!!

Posted on 2006-10-25
9
249 Views
Last Modified: 2013-12-12
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>
0
Comment
Question by:Blueoakcapital
  • 3
  • 2
  • 2
9 Comments
 
LVL 17

Expert Comment

by:akshah123
ID: 17805766
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
 

Author Comment

by:Blueoakcapital
ID: 17817674

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
 
LVL 17

Expert Comment

by:akshah123
ID: 17819868
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
Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:akshah123
ID: 17819932
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
 

Author Comment

by:Blueoakcapital
ID: 17820784
Thanks (password changed) !!

Still only sorts one way........
0
 
LVL 19

Expert Comment

by:VoteyDisciple
ID: 17914874
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
 
LVL 19

Accepted Solution

by:
VoteyDisciple earned 350 total points
ID: 17914889
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now