Need help allowing user to sort

Anon

Hello, I am very new to php and mysql and I have a small problem allowing the user to click a link to sort a column. I have seen this in some site and my try does not work i.e.

if (empty($sort)) { $sort="Birthstamp desc"; } //This is my default sort

$result = mysql_query("SELECT * FROM used Order by $sort limit $offset,$limit",$conn); // I am also using next and prev

print "<a href=\"$PHP_SELF?sort=+Stock_no+ASC\">Stock No.</a>"; // This is a sample of one of my links

It works but not well. I would like the link to change to desc if it is asc and the reverse

can anybody help me.

closer

if you had different vars for the order (asc,desc) and the cols (Stock_no, Brithstamp) you could easly test for the value of the order. ie...

if ($order != "ASC")
print "<a href=\"$PHP_SELF?sort=Stock_no&order=ASC\">Stock No.</a>";
if ($order != "DESC")
print "<a href=\"$PHP_SELF?sort=Stock_no&order=DESC\">Stock No.</a>";

That sounds right to me anyway.

Anon

Thank you for the code. I tried it and it printed link twice plus it did not change the sort order. So, I am going to show you the entire code for this page. I only linked the first column for a sample.

<div>[<a href="results.php">Show
All</a>] [<a href="sresults.php?Category=Cars">Cars</a>]
[<a href="sresults.php?Category=Trucks">Trucks</a>]
[<a href="sresults.php?Category=Vans">Vans</a>]
[<a href="sresults.php?Category=Sport Utility">Sport
Utility</a>] [<a href="sresults.php?Category=Commercial">Commercial</a>]
[<a href="sresults.php?Category=Mechanic Special">Mechanic
Special</a>] </div>

<?php

$color1 = "#ffffff";
$color2 = "#d0d0d0";
$rowcolor = $color1;

include ("config.inc.php");

$limit=10;
$r = mysql_query("SELECT * FROM used");
if (empty($offset)) {
$offset=1;
}

if (empty($sort)) { $sort="Birthstamp DESC"; }

$numrows=mysql_num_rows($r);

$result = mysql_query("SELECT * FROM used Order by $sort limit $offset,$limit",$conn);

while ($data=mysql_fetch_array($result)) {

if ($myrow = mysql_fetch_array($result)) {

echo "<table border=0 cellspacing=0>\n";

echo "<tr bgcolor=\"00cccc\"><td width=\"80\"><a href=\"results.php?sort=+Stock_no+ASC\">Stock No</a></td><td width=\"80\"> Year </td><td width=\"90\">Make</td><td width=\"100\">Model</td><td width=\"100\">Date Posted</td><td width=\"80\">Price </td><td> </td></tr>\n";

do {
printf( "<tr bgcolor=%s><td><a href=\"display.php?Stock_no=%s\">%s</a></td><td>%s</td><td>%s</td><td width=\"100\">%s</td><td> %s</td><td>%s</td><td width=\"80\"><a href=\"display.php?Stock_no=%s\"><img src=\"http://carsguam.com/usedcars/img/%s.gif\" border=\"0\"></a> </td></tr>\n", $rowcolor, $myrow[4], $myrow[4], $myrow[3], $myrow[5], $myrow[6], $myrow[2], $myrow[7], $myrow[4], $myrow[4]);
if ($rowcolor == $color1) {$rowcolor = $color2;}
elseif ($rowcolor == $color2) {$rowcolor = $color1;}

} while ($myrow = mysql_fetch_array($result));

echo "</table>\n";
?>
 
<table border="0" width="50%">
<tr>
<td width="100%">

<?
if ($offset >= $limit) {
$prevoffset=$offset - $limit;
print "<a href=\"$PHP_SELF?offset=$prevoffset&sort=+Birthstamp+DESC\">PREV</a>   \n";
}

$pages=intval($numrows/$limit);

if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

for ($i=1;$i<=$pages;$i++) {
$newoffset=$limit*($i-1);
print "<a href=\"$PHP_SELF?offset=$newoffset&sort=+Birthstamp+DESC\">$i</a>   \n";
}

if (!( ( ($offset+$limit) / $limit) >= $pages) && $pages!=1) {
// not last page so give NEXT link
$newoffset=$offset+$limit;
print "<a href=\"$PHP_SELF?offset=$newoffset&sort=+Birthstamp+DESC\">NEXT</a>";

print " $numrows Results Found ";

?>
</td>
</tr>
</table>
<?
}

}
}
?>

Powered
by: <a target="_blank" href="http://iconguam.com">iCON</a> Corporation, Guam

</body>

Please see if you can help.

Neg9 wrote:

if you had different vars for the order (asc,desc) and the cols (Stock_no, Brithstamp) you could easly test for the value of the order. ie...

if ($order != "ASC")
print "<a href=\"$PHP_SELF?sort=Stock_no&order=ASC\">Stock No.</a>";
if ($order != "DESC")
print "<a href=\"$PHP_SELF?sort=Stock_no&order=DESC\">Stock No.</a>";

That sounds right to me anyway.

Anon

I got it now. You were right, I only needed to change your code a little. Like this:

if ($sort != "ASC")
$stk = "<a href=\"results.php?sort=ASC&order=Stock_no\">Stock No.</a>";
if ($sort != "DESC")
$stk = "<a href=\"results.php?sort=DESC&order=Stock_no\">Stock No.</a>";

if ($sort != "ASC")
$yr = "<a href=\"results.php?sort=ASC&order=Year\">Year</a>";
if ($sort != "DESC")
$yr = "<a href=\"results.php?sort=DESC&order=Year\">Year</a>";

if ($sort != "ASC")
$mk = "<a href=\"results.php?sort=ASC&order=Make\">Brand</a>";
if ($sort != "DESC")
$mk = "<a href=\"results.php?sort=DESC&order=Make\">Brand</a>";

if ($sort != "ASC")
$md = "<a href=\"results.php?sort=ASC&order=Model\">Model</a>";
if ($sort != "DESC")
$md = "<a href=\"results.php?sort=DESC&order=Model\">Model</a>";

if ($sort != "ASC")
$prc = "<a href=\"results.php?sort=ASC&order=Price\">Price Range</a>";
if ($sort != "DESC")
$prc = "<a href=\"results.php?sort=DESC&order=Price\">Price Range</a>";

The $PHP_SELF was not working in Netscape so I changed it as well.

Thank you!