...

View Full Version : Allow User to Sort MYSQL Result



madmatter23
09-06-2007, 12:45 AM
I'm just looking for ideas for the best way to allow a user to sort a list of results from a MYSQL query.

I have a system working at the moment, but it's fairly messy and inefficient, so I'd like to know if anyone has any better ideas.

Right now, my page allows users to view entries from the last day, week, month, year, or simply all. I use variable $organize, which is passed through get.

If the user clicks
<a href="localhost/showresults/day">

then mod_rewrite tells the page that $organize = "day";
and I do some messy coding like this:



$dayago = date('Y-m-d H-i:s', mktime(0, 0, 0, date('m'), date('d')-1, date('Y')));
$weekago = date('Y-m-d H-i:s', mktime(0, 0, 0, date('m'), date('d')-7, date('Y')));
$monthago = date('Y-m-d H-i:s', mktime(0, 0, 0, date('m')-1 , date('d'), date('Y')));
$yearago = date('Y-m-d H-i:s', mktime(0, 0, 0, date('m') , date('d'), date('Y')-1));

$organize = $_GET["organize"];

//creates sort menu so that user can choose sorting method
//this also makes the current selection appear in bold

echo "<a href=\"localhost/showresult/day\">";
if ($organize == 'day') {
echo "<font class=\"selected\"> Day </font>";
$query = "SELECT user, etc, dateSubmitted FROM table WHERE (dateSubmitted > '$dayago') ORDER BY dateSubmitted DESC";
}
else {echo " Day ";}
echo "</a> - <a href=\"localhost/showresult/week\">";
if ($organize == 'week' || $organize == "" || !isset($organize)) {
echo "<font class=\"selected\"> Week </font>";
$query = "SELECT user, etc, dateSubmitted FROM table WHERE (dateSubmitted > '$weekago') ORDER BY dateSubmitted DESC";
}
else {echo " Week ";}
echo" </a> - <a href=\"localhost/showresult/month\">";
if ($organize == 'month') {
echo "<font class=\"selected\"> Month </font>";
$query = "SELECT user, etc, dateSubmitted FROM table WHERE (dateSubmitted > '$monthago') ORDER BY dateSubmitted DESC";
}
else {echo " Month ";}
echo"</a> - <a href=\"localhost/showresult/year\">";
if ($organize == 'year') {
echo "<font class=\"selected\"> Year </font>";
$query = "SELECT user, etc, dateSubmitted FROM table WHERE (dateSubmitted > '$yearago') ORDER BY dateSubmitted DESC";
}
else {echo " Year ";}
echo"</a> - <a href=\"localhost/showresult/all\">";
if ($organize == 'all') {
echo "<font class=\"selected\"> All </font>";
$query = "SELECT user, etc, dateSubmitted FROM table ORDER BY dateSubmitted DESC";
}
else {echo " All ";}
echo"</a> ";

$Result = mysql_query($query, $dbc);


I don't really like this code. I mostly dislike it because it's beginning to make my URLs very messy with the $_GET appendages. I suppose I could just use $_POST. I was also considering changing this to a drop down menu and having onChange submit the form.

Anyway, I'm just wondering if anyone has some input or clever ideas.

Thanks.

rafiki
09-06-2007, 12:53 AM
could use post and use ajax to retrieve the results from the onchange method

aedrin
09-06-2007, 04:28 PM
AJAX would complicate the matter a lot more.

You're not looking for a sorting method, but a filtering method.

You're also repeating a lot of code.



if (isset($_GET["organize"])) $organize = $_GET["organize"]; else $organize = '<insert default filtering here>';

$filter = ${$organize . 'ago'};

$query = "SELECT user, etc, dateSubmitted FROM table WHERE (dateSubmitted > '{$filter}') ORDER BY dateSubmitted DESC";


I excluded the HTML to make it easier to read.

This uses variable variables (http://us.php.net/language.variables.variable) (on the second line)

madmatter23
09-06-2007, 10:44 PM
Thanks for the help.

I'm looking at the 3 lines you posted, which definitely simply the code.
That really was repetitive, I don't know why I didn't think of something like this.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum