Allowing users to sort MySQL results via simple dropdown

Question

I have created a very simple CRUD that queries my database for Hotel details and displays them within an HTML table.

By default my mysql query returns the Hotels in the order they were created. I have come up with a few different sql statements to return results by distance or A-Z and I would like the user to select which they prefer.

How can I create a simple dropdown with for example "Name" and "Distance", which when selected will reload the page using the correct sort query using PHP?

Is this a mysql question? You may want to retag this question. — Tom, Nov 26 '12 at 17:38
Sounds like you have all the pieces you need...just add an 'onchange' or a 'sort' button to the drop down, and requery the page with the field name. — lynks, Nov 26 '12 at 17:45
It's definitely PHP, see the tags. This isn't a DOM question. He's asking about the query. — Madara's Ghost, Nov 26 '12 at 17:51

score 1 · Accepted Answer · answered Nov 26 '12 at 17:50

Your basic structure is going to look like;

HTML;

<SELECT id="sort_select" onchange="javascript:re_sort();">
    //options
</SELECT>

Javascript function;

function re_sort() {
    //make ajax call using sort_select value
    //refresh table contents
}

PHP;

if($_GET['sort_field'] == 'fish') {
    //execute fish sort sql
}
else if($_GET['sort_field'] == 'goat') {
    //execute goat sort sql
}
//return response

Do Not use the $_GET value directly in the sql query, instead use the structure above.

score 0 · Answer 2 · edited May 23 '17 at 11:48

Sounds simple enough. You add an ORDER BY clause populated by the value from the dropdown.

Fair Warning!

Direct user input is highly dangerous! And prepared statements cannot be used on an ORDER BY value. You'll need very aggressive whitelisting, make sure the value you receive is one of pre-defined values, which are fetched from somewhere on the server (the database schema, or hardcoded into the PHP code).

Never let user input arrive your query unchecked and unsanitized!

More information about SQL Injection.

score 0 · Answer 3 · answered Nov 26 '12 at 17:51

You will need to use JavaScript that handles the change event and reload event. However, you will want to handle disabled JavaScript browsers too. You can do that by maybe showing a sumbit button and hiding it with the JavaScript.

As far as PHP there are several ways to approach it, but here is an idea. Store the sorting values into an array. Then have the index be a number, just so SQL injection will not take place if done right as we do not want to allow pure SQL statements. When the user changes submit it.

In the code have a switch and append the appropriate SQL statement to it based on the number input.

Allowing users to sort MySQL results via simple dropdown

3 Answers3

Fair Warning!

Never let user input arrive your query unchecked and unsanitized!