Ordering mysql results

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi Group,

I have a task and I'm interested to see what ideas people have here for the  
best way of tackling it.

I have a table of products.  Each of the products is associated with a  
certain section which is achieved with a foreign key (the section id) linked  
to the sections table.

Now the problem is that when the products are listed in each section the  
order of their display is important - in other words, products should appear  
in a definite order when they are displayed - this order is not alphabetical  
or numerical so a simple ORDER BY clause won't work.  It is just the  
preference of the site owner.

I was thinking that there are 2 ways of achieving this.

The first would be to add a timestiamp column so that I could order the list  
by the most recently updated product so that the site owner could manipulate  
the list into the order they want but that's not very user friendly.

The second would be to have the site owner assign a numerical preference  
manually so that products could be ordered by that numerical preference  
column but that becomes pretty unfriendly with more than 10 products.

I'm sure I'm not the first developer to have this kind of issue to address  
and I'd be grateful to hear how other people have tackled it.

Looking forward to hearing your ideas,


Re: Ordering mysql results

Joe Molloy wrote:
Quoted text here. Click to load it
Based on your description of the problem I would use the first  
approach if the site owner frequently changes the desired  
sequence.  I would use the second approach if the desired  
sequence is fairly stable.

But, before implementing either I would ask the site owner if  
the order is based on some value that can be specified in an  
ORDER BY clause, for example units sold or value of sales.


Re: Ordering mysql results

If only it were....

This is purely a presentational issue.


Quoted text here. Click to load it

Re: Ordering mysql results

Joe Molloy wrote:

Quoted text here. Click to load it

Hi Joe,

In such cases I always add a column named 'displayposition' with a number.
Give the owner an easy way to modify these number, ORDER BY on  
displayposition, and voila.
That is your second solution. :-)

Erwin Moller

Re: Ordering mysql results

Erwin Moller wrote:
Quoted text here. Click to load it

I did it the hardcore way for my CMS. Table of categories has an order
column. Table of products, (linked to the category column by foreign
key), also has it's own order column. Both the categories and products
can be moved up and down using a summary page.
I took the extra-complicated route of allowing the categories to be
multi-tiered. Yeah, that bad boy took a good day and a bit to figure
out :)

Re: Ordering mysql results

Well I bit the bullet and did it the second way - actually it worked out  
alright as the rankings won't change too often and I used some javascript to  
make it a bit easier to operate.  Some AJAX drag and drop rows would be the  
most intuitive approach here I reckon but that will have to wait for another  

For now I present the user with a list of the product names in each sector  
and a correpsonding select list for each product with rankisgs from 1 to the  
number of products. The user is instructed that the lower the ranking the  
closer to the top of the display list the product will appear. As they go  
through the list and select values I remove that value from all the other  
select lists to prevent duplicate rankings  As soon as they rank the first  
product I display a button that lets them clear all the runkings.  if they  
click it then all the ranking select lists are reset to display all possible  
rankings for each product and they can start the process again.  In the case  
were products were previously assigned rankings these values are preselected  
in the lists when the page loads. If they make changes and then decide they  
want to reset I have a javasctip funtion that simply requests the page again  
from the server.  Finally there's a button which commits all changes to the  
database after checking that each product has been assigned a rank

That should keep them happy.

Thanks to all who made suggestions.


Quoted text here. Click to load it

Site Timeline