Page 1 of 1
Order By
Posted: Thu Mar 25, 2010 9:08 am
by daebat
I custom built a CMS in php and our graphic designer has the ability to upload several different formats of some of the stuff he works on. The tables are set up with ID as the key and currently are ordered by ID descending. I need to implement the functionality to order these but I don't think I can allow for him to change the ID right? Should I add another field and order by it or what? If I did this should I auto-increment?
Re: Order By
Posted: Thu Mar 25, 2010 9:54 am
by pickle
Add another field, let your designer change those values, then order by that column in your query.
Re: Order By
Posted: Thu Mar 25, 2010 9:58 am
by daebat
My problem is that I can't auto increment (as the ID is auto-incremented and the key) and how is he supposed to know what the next number he should order by with new submissions? There are over 500 current submissions so I will have to go in and manually update the tables so this has to be done right the first time. Should I look into using a date format?
Re: Order By
Posted: Thu Mar 25, 2010 12:41 pm
by pickle
Maybe I'm misunderstanding.
You said it was already ordered by ID descending. When you said you needed to order it, I assumed you meant a custom ordering that isn't necessarily by ID.
Re: Order By
Posted: Thu Mar 25, 2010 12:47 pm
by daebat
Sorry if that was confusing. At the moment everything is assigned an ID when it is submitted and autoincremented. I would like for the order number to be auto incremented as well but you can't do that in the same table. I really don't want to have to start joining tables but I think that's the only way to do this unless I use a date system. Your suggestion to assign an order number won't work simply because the designer will not know the last number used. There are lots of submissions and lots of categories to fall under.
Re: Order By
Posted: Thu Mar 25, 2010 1:14 pm
by mikosiko
daebat wrote:Sorry if that was confusing. At the moment everything is assigned an ID when it is submitted and autoincremented. I would like for the order number to be auto incremented as well but you can't do that in the same table. I really don't want to have to start joining tables but I think that's the only way to do this unless I use a date system. Your suggestion to assign an order number won't work simply because the designer will not know the last number used. There are lots of submissions and lots of categories to fall under.
like pickle's... my mind is also spinning.... could you please clarify what exactly are you trying to do?
At the moment everything is assigned an ID when it is submitted and autoincremented
great!!... one down
I would like for the order number to be auto incremented as well but you can't do that in the same table
are you saying that you want to order by a different field not the ID?... (the table IS orderer already for ID and you can change that order in your SELECT) ????? your objective need to be more clear here
I really don't want to have to start joining tables but I think that's the only way to do this unless I use a date system

no idea what are you trying to explain here.... "joining tables" .. "date system" in any system based on a database you JOIN tables to produce different results
Maybe you should post with a little more clarity your relevant database model (tables) and explain what are you trying to do... otherwise we are just guessing
Miko
Re: Order By
Posted: Thu Mar 25, 2010 1:24 pm
by daebat
I am currently ordering the posts by ID. This ID is the key for the table as well as being auto incremented.
I would like to figure out a way for my designer to change the order of the posts if he chooses to do so.
From what I have read there is no way to edit this from the CMS and should be done by adding an 'order' field.
If I add an 'order' field I cannot auto increment and instead he has to choose a number for the order.
I would like for the order value to auto increment as he submits but also be editable if he chooses to.
Re: Order By
Posted: Thu Mar 25, 2010 2:07 pm
by pickle
Make that new "position" field, and default it to 999999999. Then, in your query, order by the "position" column, then by the id. If he wants one product ahead of the other, he'll have to do some work to find the position of the other, and update the "position" column for the necessary products.
Re: Order By
Posted: Thu Mar 25, 2010 2:17 pm
by daebat
I didn't realize you could order by two fields. So let's say I have this example:
ID 1 ORDER 9999
ID 2 ORDER 9999
ID 3 ORDER 9999
ID 4 ORDER 9999
Let's say I want number three to show up as number two... wouldn't this happen?
ID 3 ORDER 9998
ID 1 ORDER 9999
ID 2 ORDER 9999
ID 4 ORDER 9999
Re: Order By
Posted: Thu Mar 25, 2010 2:24 pm
by pickle
Ya. Though it would probably be best to set #3 as an order value of 20, then something else as 10 (leaving 10 spaces between in case you want to put something else between them).
What I usually do when I have to order stuff is just put them all in a list like so:
Code: Select all
<ul id = "sortme">
<li>
<input type = "hidden" name="item[]" value = "ID of item" />
Name of Item
</li>
<li>
...
</ul>
, then attach jQueryUI's
sortable at it. When saving, the order of ids in $_POST['item'] define the "position".
Re: Order By
Posted: Thu Mar 25, 2010 3:32 pm
by daebat
Awesome... I appreciate the help!
