Dynamic sort by header in PHP

Question

I'm trying to create a dynamic header on Name which gives the user the ability to sort by ASC or DESC, the results is from the 'register'-table in MySQL. I have tried a couple of codings, but it hasn't given the correct result as of now. Hope anyone is able to help me :)

I have tried making another variable, but I couldn't manage to create the correct one.

<?php
    $sql = "SELECT * FROM register";
    if($sqlData = mysqli_query($db, $sql)) {
        if(mysqli_num_rows($sqlData) > 0) {
            echo "<table border ='1' bgcolor='#FFF' width='100%'>";
                echo "<tr>";
                    echo "<th><a href='overview.php?order=name'>Name</a></th>";
                    echo "<th>Score</th>";
                echo "</tr>";
            while($row = mysqli_fetch_array($sqlData)) {
                echo "<tr>";
                    echo "<td>" . $row['name'] . "</td>";
                    echo "<td>" . $row['score'] . "</td>";
                    echo "</tr>";
            }


            echo "</table>";
            mysqli_free_result($sqlData);
        } else{
            echo "No results in DB";
        }

    } else{
        echo "Error couldn't connect $sql. " . mysqli_error($db);
    }
    mysqli_close($db);
?>

Note: The object-oriented interface to mysqli is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete mysql_query interface. Before you get too invested in the procedural style it’s worth switching over. Example: $db = new mysqli(…) and $db->prepare("…") The procedural interface is an artifact from the PHP 4 era when mysqli API was introduced and should not be used in new code. — tadman
– tadman, Commented Mar 29, 2018 at 20:48
Note: Try and get out of the habit of declaring SQL statements in throw-away variables that are used only once. It's a lot easier to follow code where the query is supplied directly to the function, and there's no longer a chance of messing up and sending in $sql3 instead of the visually similar $sql8. — tadman
– tadman, Commented Mar 29, 2018 at 20:48
Why not do the column sorting on the client side through javascript? There are a ton of libraries to help with that so you don't have to roll your own table sorting code. Or am I misunderstanding the question. — JNevill
– JNevill, Commented Mar 29, 2018 at 20:54
The last thing you want to do is go back to the php server which goes back to mysql just to refetch results with the desired sorting every time someone clicks. That is needlessly expensive and, as a user, I would be very frustrated that the page reloads, and waits for a server response every time I click. Even if the reload came through an ajax response, it would be epensive to make your mysql server cough up the same results over and over again when you can just sort in javascript on the client side and be done with it. — JNevill
– JNevill, Commented Mar 29, 2018 at 21:01
Plus there are some great libraries out there that will make a really nice looking table and give it all sorts of abilities beyond just a html <table>. Like this one that works with bootstrap. — JNevill
– JNevill, Commented Mar 29, 2018 at 21:04

Evgeny Ruban · Accepted Answer · 2018-03-29 20:58:11Z

1

Hm, that's not a good idea to sort table using MySQL each time user clicked on the table's header. I suggest you to use javascript for it. Example

answered Mar 29, 2018 at 20:58

Evgeny Ruban

1,4551 gold badge17 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Dynamic sort by header in PHP

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related