?
Solved

Sort php table by column name - help!!

Posted on 2006-10-25
9
Medium Priority
?
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
WordPress Tutorial 4: Recommended Plugins

Now that you have WordPress installed, understand the interface, and know how to install new parts, let’s take a look at our recommended plugins.

 
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 1400 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

741 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