Mysql sorting problem....

hello,

Having abit of trouble with a site im working on. Basically the site displays a load of products and i want to allow the customers to be able to view the products either alphabetically, by lowest price or by highest price. This is easy to do with the menu page that displays all the items but i want a previous and next option on each seperate product page.

The problem is that when there are two items with the same price, it gets caught in a loop when im arranging them by either higher or lowest price.

heres my current code:

Code: Select all

	switch ($sortby)
	{
	case "hprice":						
	 $sqlprev = "select * from deyron where dis != 1 and ppm91 > '$netinfo[ppm91]' and name != '$name'  order by ppm91 asc";
	 $sqlnext = "select * from deyron where dis != 1 and ppm91 < '$netinfo[ppm91]' and name != '$name'  order by ppm91 desc";
	break;
	
	case "lprice":
	 $sqlprev = "select * from deyron where dis != 1 and ppm91 < '$netinfo[ppm91]' and name != '$name' order by ppm91 desc";
	 $sqlnext = "select * from deyron where dis != 1 and ppm91 > '$netinfo[ppm91]' and name != '$name' order by ppm91 asc";
	 break;
	
         default:
	 $sqlprev = "select * from deyron where dis != 1 and name < '$netinfo[name]' and name != '$name'  order by name desc";
	 $sqlnext = "select * from deyron where dis != 1 and name > '$netinfo[name]' and name != '$name'  order by name asc";
	}

which will produce a query like this:

Code: Select all

select * from deyron where dis != 1 and ppm91 > '3.15' and name != 'Venus' order by ppm91 asc

= display the item 'melody'

and the next item like this:

Code: Select all

select * from deyron where dis != 1 and ppm91 > '3.15' and name != 'Melody' order by ppm91 asc

= display the item 'venus'

and then on the next page:

Code: Select all

select * from deyron where dis != 1 and ppm91 > '3.15' and name != 'Venus' order by ppm91 asc

= display the item 'melody'


now if there are 2 or more items that are 3.15 it will alternate between each of them on each page as the next highest / lowest item.

I thought of sorting the results alphabetically and then if the price is the same, search for the alphabetically higher/lower next item (depending on the direction) with the same price, if not, dont worry about sorting by name cos that will screw things up. Im not sure how to stick an IF statement in the middle of the query and if this can be done.

maybe i can stick some php in the middle of 2 queries but i wonder if there is an easier way?

cheers all.

If you given several column names in your order clause, then MySQL will sort by the first column named ... but if that column contains two identical value when it's comparing two records, then it will move on and sort by the second named column, and so on. Thus

Code: Select all

order by ppm91 desc name desc

Did you know that you can leave out the "asc" for ascending, by the way

Ok Cheers, Il give it a try!