Order rows by clicking column name

Question

I have to order the showed list by clicking on the column-name. I have started with inspiration from this thread, but don't know how to continue with it.

This is my code:

<?php
$result = mysqli_query($mysqli,"SELECT rid, pid, firstname, lastname, email FROM temp_members_db WHERE pid='$pid1' ");

echo "<br><table><tr class='tabletop'><th><a href='mypage.php?sort=type'>Namn</a></th><th><a href='mypage.php?sort=type'>E-mail</a></th><th><a href='mypage.php?sort=type'>Resultat</a></th><th>Ta bort kandidat</th></tr>";
while($row = mysqli_fetch_array($result))
{

echo "<tr><td><strong>
<form action='respondent2.php' method='GET'>
 <input type='hidden' name='rid' value='".$row['rid']."'>
 <input type='hidden' name='firstname' value='".$row['firstname']."'> 
<input type='submit' class='resname' name='submit' value='".$row['firstname']." ".$row['lastname']."'>
 </form>
 </strong></td> 
 <td>".$row['email']."</td> 
 <td><strong><span style=\"color: $color\">".$result00."</span>%</strong></td>   
 <form action='deleterespondent2.php' method='post'>
 <input type='hidden' name='rid' value='".$row['rid']."'> 
<td> <input type='submit' class='mydel' value='Radera' onclick=\"return confirm('Show me!')\">
 </form>
</td></tr>";
}
echo "</table>";?>

As you see I have started by writing <a href="mypage.php?sort=type"> at every column name, without knowing what to write exactly instead of "type". What I want is to be able to click on the column header and sort the rows below based on the chosen column, for example Name. Then, of course, I want the belonging rows to follow each other, not only the chosen column to change order.

Oh. Did you try my code? And it didn't work? Any error messages? Maybe post your database and I can code you a live example. This is pretty simple stuff, if you still are having problems, let's get you sorted! — Eugene, Nov 01 '16 at 20:13

score 0 · Answer 1 · answered Oct 15 '16 at 12:31

Change your code to this:

if ($_GET['sort'] == 'namn')
{
    $order = " ORDER BY lastname";
}
elseif ($_GET['sort'] == 'email')
{
    $order = " ORDER BY email";
}

$result = mysqli_query($mysqli,"SELECT rid, pid, firstname, lastname, email FROM temp_members_db WHERE pid='$pid1' ".$order);

echo "<br><table><tr class='tabletop'><th><a href='mypage.php?sort=namn'>Namn</a></th><th><a href='mypage.php?sort=email'>E-mail</a></th><th>Resultat</th><th>Ta bort kandidat</th></tr>";

There is no point to defend ORDER BY clause from SQL injection, if WHERE is still wide open :) — Your Common Sense, Oct 15 '16 at 12:35

Eugene · Answer 2 · 2016-10-15T15:59:51.380

Please use parameterized queries:

$mysqli = new mysqli('host', 'user', 'pass', 'db', port);
if($result = $mysqli -> prepare("SELECT rid, pid, firstname, lastname, email FROM temp_members_db WHERE pid=?")) {
    $result -> bind_param("i", $pid1); // Assuming pid1 is an integer
    $result -> execute();
    $result -> store_result();
    $result -> bind_result($pid);
    while($result -> fetch()) {
      echo $pid; // etc etc
    }  
}

Reading for you:

Similar to Drew's suggestion:

<?php
$mysqli = new mysqli('host', 'user', 'pass', 'db', port);
$order_string = ""
// Assuming PHP >= 5.4, otherwise use array()
$sort_keywords = [
    "namn" => "lastname",
    "email" => "email",
];
if(isset($_GET['sort']) && array_key_exists($_GET['sort'], $sort_keywords)) {
    $order_string = " ORDER BY ".$sort_keywords[$_GET['sort']];
}
// Assume <head>, <body> etc etc here.
echo "<br><table><tr class='tabletop'><th><a href='mypage.php?sort=namn'>Namn</a></th><th><a href='mypage.php?sort=email'>E-mail</a></th><th>Resultat</th><th>Ta bort kandidat</th></tr>";

if($result = $mysqli -> prepare("SELECT rid, pid, firstname, lastname, email FROM temp_members_db WHERE pid=?".$order_string)) {
    $result -> bind_param("i", $pid1); // Assuming pid1 is an integer
    $result -> execute();
    $result -> store_result();
    $result -> bind_result($rid, $pid, $firstname, $lastname, $email);
    while($result -> fetch()) {
        // $color? $result00?
        echo "<tr><td><strong>
        <form action='respondent2.php' method='GET'>
         <input type='hidden' name='rid' value='".$rid."'>
         <input type='hidden' name='firstname' value='".$firstname."'> 
        <input type='submit' class='resname' name='submit' value='".$firstname." ".$lastname."'>
         </form>
         </strong></td> 
         <td>".$email."</td> 
         <td><strong><span style=\"color: $color\">".$result00."</span>%</strong></td>   
         <form action='deleterespondent2.php' method='post'>
         <input type='hidden' name='rid' value='".$rid."'> 
        <td> <input type='submit' class='mydel' value='Radera' onclick=\"return confirm('Show me!')\">
         </form>
        </td></tr>";
    }  
}

echo "</table>";
?>

Or (exactly the same):

<?php
$mysqli = new mysqli('host', 'user', 'pass', 'db', port);
$order_string = ""
// Assuming PHP >= 5.4, otherwise use array()
$sort_keywords = [
    "namn" => "lastname",
    "email" => "email",
];
if(isset($_GET['sort']) && array_key_exists($_GET['sort'], $sort_keywords)) {
    $order_string = " ORDER BY ".$sort_keywords[$_GET['sort']];
}
// Assume <head>, <body> etc etc here.
?>
<br><table><tr class='tabletop'><th><a href='mypage.php?sort=namn'>Namn</a></th><th><a href='mypage.php?sort=email'>E-mail</a></th><th>Resultat</th><th>Ta bort kandidat</th></tr>
<?php
if($result = $mysqli -> prepare("SELECT rid, pid, firstname, lastname, email FROM temp_members_db WHERE pid=?".$order_string)) {
    $result -> bind_param("i", $pid1); // Assuming pid1 is an integer
    $result -> execute();
    $result -> store_result();
    $result -> bind_result($rid, $pid, $firstname, $lastname, $email);
    while($result -> fetch()) {
        // $color? $result00?
        ?>
        <tr><td><strong>
        <form action='respondent2.php' method='GET'>
         <input type='hidden' name='rid' value='<?php echo $rid; ?>'>
         <input type='hidden' name='firstname' value='<?php echo $firstname; ?>'> 
        <input type='submit' class='resname' name='submit' value='<?php echo $firstname; ?> <?php echo $lastname; ?>'>
         </form>
         </strong></td> 
         <td><?php echo $email; ?></td> 
         <td><strong><span style="color: <?php echo $color; ?>"><?php echo $result00; ?></span>%</strong></td>   
         <form action='deleterespondent2.php' method='post'>
         <input type='hidden' name='rid' value='<?php echo $rid; ?>'> 
        <td> <input type='submit' class='mydel' value='Radera' onclick="return confirm('Show me!')">
         </form>
        </td></tr>
    <?php
    }  
}
?>
</table>

Also, probably best to define the sort array in the database and have them echoed as an attribute in the html table row header. I would personally just sort on the client side, which is actually sometimes quicker (ORDER BY can be slow), however, I am unaware of your intentions.

Have fun with the edits ;)

Order rows by clicking column name

2 Answers2