drop down menu to sort query results on a php page

Question

I have a simple list-type php page, which lists items according to a mysql query, like:

mysql_select_db($database_connBHN, $connBHN);
$query_rsMarket = "SELECT * FROM my_items WHERE active=1 ORDER BY name asc";
$rsMarket = mysql_query($query_rsMarket, $connBHN) or die(mysql_error());
$row_rsMarket = mysql_fetch_assoc($rsMarket);
$totalRows_rsMarket = mysql_num_rows($rsMarket);

then the page list these items and their description in separate tables.

Initially this page lists these items in alphabetical order. Now I would like to put a drop down box on the top of the page, where the user could choose another two or three more sorting options, like date, or itemId, etc, which values are stored in the database.

How could I solve this in a simple way, without leaving that page? (i.e. I do not want to create separate pages for each different result set)

Ben D · Accepted Answer · 2012-02-19 23:51:57Z

1

No, it's easier to keep this as a single script and just allow for the sorting variable to be switched. For security's sake, it's best to limit the user input to a per-defined set of options in the PHP script:

$sort_options = array('name asc','name desc','dateadded asc','dateadded desc');
if(!isset($_GET['field'])){
   $_GET['field'] = 'name';
}
if (!isset($_GET['order'])){
   $_GET['order'] = 'asc';
}

$full_query_sort = $_GET['field'].' '.$_GET['order'];
if (!in_array($full_query_sort,$sort_options)){
   die('invalid selection');
}

mysql_select_db($database_connBHN, $connBHN);
$query_rsMarket = "SELECT * FROM my_items WHERE active=1 ORDER BY ".$full_query_sort;
$rsMarket = mysql_query($query_rsMarket, $connBHN) or die(mysql_error());
$row_rsMarket = mysql_fetch_assoc($rsMarket);
$totalRows_rsMarket = mysql_num_rows($rsMarket);

Now you can just have the order set with _GET variables: http://example.com/page.php?field=name&order=desc etc. This can be set with javascript (or on form submission) using dropdowns:

<select id='field_select' 
   name='field' 
   onchange="window.location='?field='+this.value+'&order='+document.getElementById('order_select').value;">
      <option value='name' <?php if(!isset($_GET['field']) || $_GET['field']=='name'){echo "selected";} ?>>Sort by Name</option>
      <option value='dateadded' <?php if(isset($_GET['field']) && $_GET['field']=='dateadded'){echo "selected";} ?>>Sort by Date Added</option>
</select>

<select id='order_select' 
   name='order' 
   onchange="window.location='?field='+document.getElementById('field_select').value+'&order='+this.value;">
      <option value='asc' <?php if(!isset($_GET['order']) || $_GET['order']=='asc'){echo "selected";} ?>>Ascending/option>
      <option value='desc' <?php if(isset($_GET['order']) && $_GET['order']=='desc'){echo "selected";} ?>>Decending</option>
</select>

edited Feb 19, 2012 at 23:51

answered Feb 19, 2012 at 18:46

Ben D

14.6k3 gold badges47 silver badges59 bronze badges

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

4 Comments

Yaelede Over a year ago

Absolutely fantastic! It works just like I wished. Thank you!

Yaelede Over a year ago

I have however noticed a small mistake: when I select the second (dateadded) sort option, it works, but the form on the page reverts back to the first (default) option, so practically it means that I cannot choose 'Sort by Date Added/Descending' option. Any idea?

Ben D Over a year ago

I modified the dropdown code so that it includes some php code that detected the currently selected field/order (or uses the defaults) and sets those to "selected" as appropriate

Yaelede Over a year ago

Thank you, now it reached perfection!

Collectives™ on Stack Overflow

drop down menu to sort query results on a php page

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related