Numbering problem

[fazzfarrell]

I have a database where the numbering starts at 48 and goes on 49, 50 etc.

When I view my data 49-99 display in the correct order, but i think it is seeing '100' as '1' as this appears as the first entry?

Never had this before and cant see why it is happening?

[trq]

What field type is the field in question?

[fazzfarrell]

its an auto increment int(11)

[trq]

Then there should'nt be a problem.

However, PRIMARY KEYS are not designed to be relied upon for sorting. What is it you are doing excactly?

[fazzfarrell]

I have this:

SELECT ID, DATE, USERNAME, TITLE, MESSAGE
FROM Message
UNION ALL
SELECT ReplyID, DATE, USERNAME, TITLE, MESSAGE
FROM MessageR
ORDER BY ID DESC

All works fine untill the 100th entry was made.

thanks

[trq]

Why are you ordering by ID and not DATE?

[fazzfarrell]

Tried by date, but as the page groups replys on the forum and each reply groups itself under the question it answered. At the moment it flows fine, but if I do it by date the replys go above the questions

[trq]

Sounds like you have some database design issues more than anything.

Anyway, odering by an autoincrement field is a bad idea. There is no guarantee they will stay in order, that is not there intended purpose (Im sure Ive said it before).

[fazzfarrell]

Ok if I dont use that auto increment field how do I go about numbering another field when someone makes an entry?

[craygo]

try

[code]SELECT ID, DATE, USERNAME, TITLE, MESSAGE
FROM Message
UNION ALL
SELECT ReplyID, DATE, USERNAME, TITLE, MESSAGE
FROM MessageR
ORDER BY Message.DATE DESC, MessageR.Date DESC[/code]

One other thing, Using UNION is probably not your best bet here. If someone answers a message that is really old it will not show correctly. I am guessing that you want to have the messages show, and under each message show replies. If that is the case you should link the 2 tables with a common field.

Message table can stay the same but you should add a field in MessageR called MessageID. This should contain the ID from the message table that way when you query it will link everything together.
[code]SELECT Message.DATE AS m_date, Message.USERNAME AS m_user, Message.TITLE AS m_title, Message.MESSAGE AS m_message,
MessageR.DATE AS r_date, MessageR.TITLE AS r_title, MessageR.MESSAGE AS r_message
FROM Message
LEFT JOIN MessageR ON Message.ID = MessageR.MessageID
ORDER BY Message.DATE DESC, MessageR.Date DESC[/code]

Now the tables are linked.

Ray

[fazzfarrell]

Thanks for that,

But still does not work?

[fazzfarrell]

I have set it up like this as i already had a field that held the ID number from the Message table.

[code]
SELECT Message.DATE AS m_date, Message.USERNAME AS m_user, Message.TITLE AS m_title, Message.MESSAGE AS m_message,
MessageR.DATE AS r_date, MessageR.TITLE AS r_title, MessageR.MESSAGE AS r_message
FROM Message
LEFT JOIN MessageR ON Message.ID = MessageR.MessageID
ORDER BY Message.DATE DESC, MessageR.Date DESC
[/code]

Problem is now that it is not showing all the mesages from both tables, it just repeats any entry with a reply twice?