Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sort php table by column name - help!!

Posted on 2006-10-25
9
Medium Priority
?
260 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
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.
Suggested Courses

610 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