Ordering mysql results

[Joe Molloy]

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,

Joe

[Jerry Gitomer]

Re: Ordering mysql results

Joe Molloy wrote:

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,
>
Joe
>
>

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.

HTH
Jerry

[Joe Molloy]

Re: Ordering mysql results

If only it were....

This is purely a presentational issue.

Joe

"Jerry Gitomer" <jgitomer@veriz on.netwrote in message
news:d1NIg.7950 $Xl5.3340@trndd c06...

Joe Molloy wrote:

>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,
>>
>Joe

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.
>
HTH
Jerry

[Erwin Moller]

Re: Ordering mysql results

Joe Molloy wrote:

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,
>
Joe

Hi Joe,

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

Regards,
Erwin Moller

[gbbulldog@googlemail.com]

Re: Ordering mysql results

Erwin Moller wrote:

Joe Molloy wrote:
>

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,

Joe

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

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 :)

[Joe Molloy]

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
day.

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.

Joe


"Joe Molloy" <molloy_joe@hot mail.comwrote in message
news:RSLIg.1321 4$j7.326835@new s.indigo.ie...

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,
>
Joe
>