Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sort php table by column name - help!!

Posted on 2006-10-25
9
Medium Priority
?
264 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
7 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

578 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