Need help with number values

[Zie]

I made a very basic (for now) shoutbox in PHP over the weekend. I am trying to make the 'shouts' appear in a certain order. I tried giving them an id, which increases by 1 every shout, but the values of the numbers get mixed up after it hits value 10. If it makes any difference, I am using SQL to store all of the data, not text files. I've gone into the SQL database and edited the number values to have some zeros infront of the numbers (001, 002, ... 010, ... 100, etc). There has to be another way that this can be solved by instead of having to manually edit the numbers in SQL myself. I also tried to make PHP automatically add the zeros, but that doesn't work either, it just comes out as 1 instead of 001. I know its possible to have just 1, 2, etc by looking at invision power board's member id's.Thanks in advance if any of you can help me, it is much appreciated!

[Synook]

but the values of the numbers get mixed up after it hits value 10

Why? Have you tried making the id field PRIMARY KEY AUTO_INCREMENT?

[Zie]

Why? Have you tried making the id field PRIMARY KEY AUTO_INCREMENT?

No, I just started getting into SQL. Thank you for making me aware of that. I tried it and it worked Thank you very much (and for the speedy reply!)

[Nanaki]

One other thing that I have found handy in cases like this is to store some extra data, it's so neat to alter this kind of queries when the system have been used for a while.shoutbox (this is the db table)id (auto_inc to keep track of the order)name (who dunnit)text (whatever they wanted to say)date (when was this said?)If the name is the id from a user table (i assume users must log in, and that they then get some kind of unik id in the system) you can credd productive profiles, keep track on how many times they have posted stuff and so on.The date (I alway throws in the date) will enable you to organize the data by time. Example: only fetch the post from the last moth (or what ever). There is a debate on how to store dates. I will not go into that, but I use the php code $date(U); to get the time in unix timestamp. That is at least easy to retrieve.your SQL will be mostly the same, if you have something likeSELECT * FROM shotbox ORDER BY id desc LIMIT 10; now, you only need to edit this one line in 5 months when the boss decides to have something else.SELECT * FROM shoutbox WHERE date > $Xdays; // the last x days, if you use unix time stamp in the db, make the variable the time in unix timestamp in phpor from only one userSELECT * FROM shoutbox WHERE name = 10; // assuming 10 can be linked the the famous user table you can put "zie" in there as well if you go for that approach.

[boen_robot]

Not only you need to store IDs as auto increments, but when you sort by it, you need to ensure the values are sorted as numbers. Otherwise1591020is instead sorted as11020509because of the different rules for sorting numbers and strings.I'm not sure how you can make this ensurance though.

[justsomeguy]

It will be sorted numerically if the column is a number data type like int or float. If the column is a character data type like varchar then it will sort lexographically.