Set sort order of MySQL query using dropdown options

Question

I want to allow site users to change the sort order of results that are returned by an existing query. The query is currently

$searchList = 'select distinct pa.products_id, pd.products_name,
               p.products_model
               FROM ' . TABLE_PRODUCTS_ATTRIBUTES . ' pa
               left join ' . TABLE_PRODUCTS_DESCRIPTION . ' pd on (pa.products_id = pd.products_id)
               left join ' . TABLE_PRODUCTS . ' p on (pa.products_id = p.products_id)
               WHERE pd.language_id = ' . $language_id . '
               order by products_model';

I've added the following code to generate the dropdown

<select id = "SortBy">
<option value ="">Please Choose<br>    
<option value ="products_model;">Model (asc)<br>
<option value ="products_model DESC;">Model (desc)<br>
<option value ="products_name;">Name (asc)<br>
<option value ="products_name DESC;">Name (desc)<br>
<option value ="products_id;">Product ID (asc)<br>
<option value ="products_id DESC;">Product ID (desc)
</select>

and updated the query to

$searchList = 'select distinct pa.products_id, pd.products_name,
               p.products_model
               FROM ' . TABLE_PRODUCTS_ATTRIBUTES . ' pa
               left join ' . TABLE_PRODUCTS_DESCRIPTION . ' pd on (pa.products_id = pd.products_id)
               left join ' . TABLE_PRODUCTS . ' p on (pa.products_id = p.products_id)
               WHERE pd.language_id = ' . $language_id . '
               order by ".mysql_real_escape_string($sort)."';

After the select options and before the sql query I've added

$sort=$_POST["SortBy"]

which i believe should contain the selection made once it's submitted.

This is where I'm a little stuck. I want the user to be able to select their option from the dropdown and have it auto submit and store the choice so that the query which follows uses their selection but I don't know how to do this. I'm assuming some type of jQuery, but to be honest I'm lost when it comes to coding in this.

Following comments from Jeff I have changed the select list to this

<select id = "SortBy" name="SortBy>
<option value ="">Please Choose<br>    
<option value ="model+">Model (asc)<br>
<option value ="model-">Model (desc)<br>
<option value ="name+">Name (asc)<br>
<option value ="name-">Name (desc)<br>
<option value ="prodid+">Product ID (asc)<br>
<option value ="prodid-">Product ID (desc)
</select>

and added the following code

    switch ($_SESSION ['sba_sort_order']) {
    case 'model+' :
        $order_by = ' products_model; ';
        break;
    case 'model-' :
        $order_by = ' products_model DESC; ';
        break;
    case 'name+' :
        $order_by = ' products_name; ';
        break;
    case 'name-' :
        $order_by = ' products_name DESC; ';
        break;
    case 'prodid+' :
        $order_by = ' products_id; ';
        break;
    case 'prodid-' :
        $order_by = ' products_id DESC; ';
        break;

Still lost on how to get this to trigger automatically and store the result for the sql query on selection of a dropdown though.

Answer 1

First of all if you want some kind of "auto submit" you need to handle the onChange event, so your select could look like the following:

<select id = "SortBy" name="SortBy" onchange="submitForm()">

jQuery style

$("#SortBy" ).change(function() {
   //submit the form
});

Of course all kinds of toolkits like Dojo, jQuery and other (you mentioned jQuery - IMO it's always good to use any of them, at least for the cross-browser support) will help you with this.

Also, think about how do you want to post the data to your server: asynchronously or not.

Have a look at MVC and try to implement it this way. Create a controller which will handle the request (it may also validate it, some prefer validate on the model level), create a model which will save the chosen option if you want it persistent and prepare a view to interact with the user. It may be just "Your choice was successfuly saved", but it really depends on how you actually want it.