Sorting Database Results in PHP

[TheRapidGroup]

I am working on putting a drop down menu on my page which allows the user to
change the order of which he/she wants to view the database results, for
example by date, price, etc...

I guess where I run into trouble would be the SQL query. I understand the
statement ORDER BY price ASD or DESC. But how can I rewritre my statement so
the user has a choice to change the order of things??

Any help would be greatly appreciated!

[Tom Muck]

"TheRapidGroup" <webforumsuser@macromedia.com> wrote in message
news:cvq5lp$bju$1@forums.macromedia.com...

> I am working on putting a drop down menu on my page which allows the user

to

> change the order of which he/she wants to view the database results, for
> example by date, price, etc...
>
> I guess where I run into trouble would be the SQL query. I understand

the

> statement ORDER BY price ASD or DESC. But how can I rewritre my statement

so

> the user has a choice to change the order of things??
>
> Any help would be greatly appreciated!

If you're using a drop-down menu, you could do something like this:

<?php
$sortorder = "ASC";
$sortfield = "Price";

if(isset($_POST["sortorder"])) {
$sortorder = $_POST["sortorder"];
}
if(isset($_POST["sortfield"])) {
$sortfield= $_POST["sortfield"];
}

then in your SQL statement:

"SELECT blah FROM mytable
ORDER BY $sortfield $sortorder"

Your dropdowns would look something like this:

<select name="sortorder">
<option value="ASC">Ascending</option>
<option value="DESC">Descending</option>
</select>
<select name="sortfield">
<option value="Price">Price</option>
<option value="field2">Field2</option>
etc.
</select>

I also have a commercial extension for sorting tables that you may find
suits your needs, as it automates things:
[url]http://www.tom-muck.com/extensions/help/sortrepeatregion/[/url]


--
-------------------------------------------
Tom Muck
co-author Dreamweaver MX 2004: The Complete Reference
[url]http://www.tom-muck.com/[/url]

Extending Knowledge, Daily
[url]http://www.CommunityMX.com/[/url]

[TheRapidGroup]

Thanks for replying, I made a few minor changes to the last few lines of code
only because I was getting "File Not Found" error... but I want to show you
what I have done thus far:

PHP
<?php
$sortorder = "ASC";
$sortfield = "Price";

if(isset($_POST["sortorder"])) {
$sortorder = $_POST["sortorder"];
}
if(isset($_POST["sortfield"])) {
$sortfield= $_POST["sortfield"];
}

SQL QUERY:
FROM clocks ORDER BY $sortfield $sortorder

MENUS
<select name="menu1" onChange="MM_jumpMenu('parent',this,0)">
<select name="sortorder">
<option value="?sortorder=ASC">Ascending</option>
<option value="?sortorder=DSC">Descending</option>
</select>
<select name="menu2" onChange="MM_jumpMenu('parent',this,0)">
<select name="sortfield">
<option value="?sortfield=price">Price</option>
<option value="?sortfield=item">Item</option>
</select>

Now when I select one of these two items, it would appear to be executing
except nothing on the page changes in terms of sorting.... can you see where I
am going wrong?

[Michael Fesser]

.oO(Tom Muck)

>$sortorder = "ASC";
>$sortfield = "Price";
>
>if(isset($_POST["sortorder"])) {
> $sortorder = $_POST["sortorder"];
>}
>if(isset($_POST["sortfield"])) {
> $sortfield= $_POST["sortfield"];
>}
>
>then in your SQL statement:
>
>"SELECT blah FROM mytable
>ORDER BY $sortfield $sortorder"

Ever heard of SQL injection?

Micha

[Tom Muck]

> Now when I select one of these two items, it would appear to be executing
> except nothing on the page changes in terms of sorting.... can you see

where I

> am going wrong?

You're using query strings instead of form variables, so you have to change
your POSTs to GETs. Also, Michael brings up the point of SQL injection,
which you should also concern yourself with, by using something like this
instead:

if(isset($_GET["sortorder"])) {
$sortorder = (!get_magic_quotes_gpc()) ?
addslashes($_GET["sortorder"]; ) : $_GET["sortorder"];;
}


Tom