CODEWITHSUNDEEP
sql-order-bylimit
jonnypixel
jonnypixel

Reputation: 327

ORDER BY price ASC / DESC problems

I just cant seem to work out why my ASC / DESC ORDER BY is not doing what i want it to.

If i click the price column heading it goes and orders the entire table data even beyond the LIMIT on the page? how do i fix this?

<?php
$c=$_GET['c'];
$s=$_GET['s'];
$d=$_GET['d'];
$l=$_GET['l'];
if($d==1) { $DIRECTION='ASC'; } elseif($d==0) { $DIRECTION='DESC'; } else { $DIRECTION='DESC'; }
if($l>51) { $l=50; } else { $l=$_GET['l']; }
$sql=mysql_query("SELECT id,title,price FROM listings WHERE status IN(0,1,4,5) ORDER BY $s $DIRECTION LIMIT $c,$l"); 
?>

here is what the url looks like

c=10&s=price&d=1&l=25

So the above url shows me 25 entries from the 10th row in Ascending data, However if i click the price column heading link i would change ASC to DESC and it works but it goes and gets the lowest price or highest price from the very ends of the table data and ignores the LIMIT 10,25?

How does it work?

Thanks in advance to anyone that can help Jonny

Upvotes: 0

Views: 1629

Answers (1)

ronalchn
ronalchn

Reputation: 12335

They do work. That is the way LIMIT and ORDER BY is designed to work.

That is, the database always sorts the data, before applying the LIMIT clause. If you want to sort the data after LIMIT is applied, you have two options:

  • Sort it using PHP. This is probably a relatively easy solution, and may be better if you want to avoid the database doing so much unnecessary work. However, it could be a bit fiddly if you use the result as a data-stream instead of by reading everything into an array, before using it.

  • Use a subquery. In this case, you force the LIMIT clause to be applied before sorting it:

    SELECT * FROM (SELECT id,title,price FROM listings WHERE status IN(0,1,4,5) LIMIT $c,$l) ORDER BY $s $DIRECTION

Upvotes: 0

Related Questions