How to rank load results on car page

[atsidi]

This is kinda hard for me to explain. When an admin user adds a
new car to our "used car" pages, it will always just appear in the order
it was added. BUT, the admin user might really want the car to appear in
a certain order on the page, so that someone visiting our website might see
the newly added car at the top of the page, or second to the top. In other words,
the admin user (the guy who puts up our cars on the site) wants to be able to choose
what order the cars appear to visitors to our site. I guess you might say, he wants to
rank the order of each car he adds. Currently, the cars just appear in the order in which
they were added.

Here is the code that loads the page as it is (with no ranking option to our admin guy):
<CFQUERY DATASOURCE="#request.maindsn#" NAME="custCartQuery">
SELECT *
FROM custom_carts
ORDER BY id DESC
</CFQUERY>
Here is what the table actually looks like in sql server 2k:

id | file_name | description | thumb_name | name | position
1 a.jpg (....) a_thumb.jpg a_any 1
2 b.jpg (....) b_thumb.jpg b_any 2
3 c.jpg (....) c_thumb.jpg c_any 3

ANY help is greatly appreciated.
Thanks,
Trint

[Guest]

You're going to need a new column in your database named displayOrder which your administrator can set the numeric order of display. Then the query would have an ORDER BY displayOrder.

The problem is, how do you keep the displayOrder updated? If you have 4 cars, display order might be 1, 2, 3, 4. But if you add a fifth car and want it displayed as the second car, what then? 1, 2, 2, 3, 4?

You might consider having categories (top, middle, bottom, for example) and give each car a category. Your ORDER BY controls which categories come first, second and third. With in each category, you could have the most recent car displayed first, etc.

[atsidi]

I was thinking what you said (creating a new colmn for ranking).
If admin user has a text box that is for "display order" and puts the integer 3 on a new car when there are, lets say 10 cars on this page (which means 3 is already taken), how will they all change to meet new display order?
Thanks for your reply, we are on the right track.
Trint

[cf_dev2]

>How will they all change to meet new display order?

atsidi,

One way would be to reorder the rankings whenever a record is added or updated. The attached would eliminate duplicates and produce consecutive rank numbers .. if thats important. The downside is it updates all records in the table and probably needs a transaction. I'm sure there are better ways. But this might give you some ideas..

[dempster]

You need to have your admin user define pretty specifically how he or she would like to have the list sorted when it is displayed. I think jdeline's suggestion about groups makes sense, unless the user wants to specify the exact order for each car. WIthin groups, you could sort by a date added field to show the newest records on the top.

It sounds, though, like maybe the need is simply to show the latest additions first (that makes a bit of sense). In that case, just add a date field for when the record was added and then use ORDER By to sort on that in descending order.

[atsidi]

I spoke with admin. They want an up button and a down button with each "car row". Example: If up is selected with "car #4" it switches places with "car #3". If down is selected with "car #4", it switches places with "car#5". I think I could maybe figure the code out if I knew how to get the id # of the selected car.
Example: http://www.ourwebsite.com/infoPages/customCarts/index.cfm?cid4 <<this number "in this case 4".
Any help is MUCH appreciated.
Thanks,
Trint

[dempster]

So the admin wants some kind of function to set the order? How many cars are you talking about? This could make a difference in the interface you design.

If it was a relatively small number (say 10), you could list the cars and each car could have 10 radio buttons next to it, numbered 1 through 10. Every button 1 would have the same name (same "rank1") and the value would be the car ID. Same with button 2, etc. This way, you can only select one button 1, one button 2, etc. When you submit the form, you get the ID for each button number and update that car's record with the rank.

This would be clunky as the list gets longer. Another option would require javascript to list the cars and move them around in a list in order to set the rank. This can be tricky if you don't know javascript.

[philh]

There's a decent dynamic list reorder example at

http://www.siteexperts.com/tips/elements/ts32/page1.asp

However, be careful of how many items are in the list.

[atsidi]

Phil,
Thanks, I'm taking a look at this.
Trint