Order By

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
daebat
Forum Commoner
Posts: 47
Joined: Mon May 11, 2009 10:16 am

Order By

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Order By

Post by pickle »

Add another field, let your designer change those values, then order by that column in your query.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
daebat
Forum Commoner
Posts: 47
Joined: Mon May 11, 2009 10:16 am

Re: Order By

Post 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?
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Order By

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
daebat
Forum Commoner
Posts: 47
Joined: Mon May 11, 2009 10:16 am

Re: Order By

Post 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.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Order By

Post 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
8O 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
daebat
Forum Commoner
Posts: 47
Joined: Mon May 11, 2009 10:16 am

Re: Order By

Post 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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Order By

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
daebat
Forum Commoner
Posts: 47
Joined: Mon May 11, 2009 10:16 am

Re: Order By

Post 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
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Order By

Post 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".
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
daebat
Forum Commoner
Posts: 47
Joined: Mon May 11, 2009 10:16 am

Re: Order By

Post by daebat »

Awesome... I appreciate the help! :D
Post Reply