Forum Moderators: coopster
I have a list of products that I need to set the order for. Right now they are ordered by price, but my clients wants to be able to order them himself - set certain items to be the first second third and so on. I was curious what the best way to do this is. And how to design it so its easy to change the order.
The site is designed in PHP and MS SQL.
Thanks :)
Wes
Your SQL query will looks like:
"...... order by my_order"
To build the dropdown menu, you should count all the items at this table. In this way you will now the max number for a loop:
for($i = '1'; $i < $rows; $i++)
{
echo "<option value=\"$i\">$i</option>\n";
}
Can you give a few more details here? Is your client looking to highlight a few given products, or really create an custom order for the entire list? And how long is the list?
Your SQL query will looks like:
"...... order by my_order"
This is the idea, but may have the undesired effect of sorting records with null or zero values to the top of the list. (You can get around that by making the default value an arbitrary high number, but that's messy.) It may be easier to have higher values appear earlier, like this:
"ORDER BY Custom_Priority DESC"
My client is actually looking to do both. He wants to highlight certain items and sort the rest to his preference. I thought about having a column for the order and then order by that column. I'm just trying to think of an easy way to make easy to change the order later on.
I'm just trying to think of an easy way to make easy to change the order later on.
Well, that sounds like it could be as easy as sorting a deck of cards. In other words, it's going to be tedious, no matter what you do.
What's worse, "custom sorting" a database really seems like fighting against the whole concept of databases. Typically, you'd want your users to be able to sort by price, product, manufacturer, etc., to find what they need, instead of an arbitrary order that might not make sense to the user.
OK, lecture over. Here's how I would implement a custom sort order:
1.- With PHP, fetch the FieldID, Label, and Custom Sort fields
- Also the total number of records to be custom-sorted
2.- Put these values into radio buttons form elements, each field would be a row and the columns would be 1st, 2nd, 3rd, up to the total number of records. Make sure the current sort order is reflected in the radio buttons.
3.- Use JavaScript to make sure no two fields have the same sort order value.
For instance, if you click to make the 3rd element appear first, the 1st element would switch to appear 3rd.
Alternatively, in this instance, you could bump the necessary elements down one spot: the 1st item would move to 2nd, the 2nd to 3rd.
OR, leave this out entirely. If two records indicate the same sort value, it will be arbitrary which appears before the other, but besides for that the sort will still work.
4. Submit the form back to the server to set the new sort order.
Sorry if that was way too much, and please let me know if any of this isn't clear.
The best way to accomplish this task is to make an administrative control panel for your client and then make a form that reads the order from the MsSQL database and allow him to change the numbers in the form and press Submit.
After he presses submit, have it run a PHP script to run some MySQL UPDATE commands and store the new order.
This will work.
