MySQL auto_increment but with left zeros?
Moderator: General Moderators
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
MySQL auto_increment but with left zeros?
Right now the user id on my MySQL database auto_increments however it would be much more useful if it always had a set number of numbers, in example instead of '1' I would want to have the first id be '00000000001'. How do I do this? I'll listen to counter-arguments though implementing this would dramatically reduce later load on the server for me.
Re: MySQL auto_increment but with left zeros?
Why would that be more useful?
To make the column zero padded you have to change it from a numeric column to a character based one, which will make it impossible to use auto-increment, among other things.
Why not just zero pad it in the SQL statement so that it gets returned with the zeros you want?
Also, why do you need the zeros? An auto-increment key is usually a surrogate key, which means that the value of the column, in and of itself should mean nothing at all other than a reference to the rest of the data.
To make the column zero padded you have to change it from a numeric column to a character based one, which will make it impossible to use auto-increment, among other things.
Why not just zero pad it in the SQL statement so that it gets returned with the zeros you want?
Also, why do you need the zeros? An auto-increment key is usually a surrogate key, which means that the value of the column, in and of itself should mean nothing at all other than a reference to the rest of the data.
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: MySQL auto_increment but with left zeros?
Actually I ended up working around the issue so I have the best of both worlds in a manner of speaking. 
Re: MySQL auto_increment but with left zeros?
Would you mind sharing the details since I took the time to reply to your post? 
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: MySQL auto_increment but with left zeros?
In the face of being the black hole for attracting criticism from others sure; easing administrative management of uploaded content. In essence Instead of viewing files in detailed view from top to bottom: 1, 10, 11, 2, 3, 4, 5, 6, 7, 8, 9 I wanted to ensure the numbers were correctly ordered. When an image is uploaded for an avatar I amend it's new file name when I move it to $userid.'_'.$username.'_avatar'.$file_extension; instead of using some random meaningless junk.
There are some other benefits to this. First if you upload abusive images (say something unspeakably violent) it'll be much easier to track down (I would know the account that was used but would need to confirm it was owner or the account) and thus quicker to settle the issue. It also would reduce the size of the database by only having to store the file extension (I think at this moment in time) versus a full URL (the random junk). I could convert and thus restrict file extensions however sometimes 256 colors just doesn't cut it.
Considering it's my first generation coding of a PHP/MySQL membership driven site (well practice script really) I'm sure there are a ton of things that can be criticized though I'm interested in what won't be drastically difficult to implement at this time. For example I'll eventually get around to splitting the client and server code (as it currently is on my life website in a matter of speaking) in to two layers though like I said this is just for practice right now and I won't get around to that (in example) until I have the functionality working as desired. It's been a lot of fun and very rewarding to work on the stuff I have of late.
There are some other benefits to this. First if you upload abusive images (say something unspeakably violent) it'll be much easier to track down (I would know the account that was used but would need to confirm it was owner or the account) and thus quicker to settle the issue. It also would reduce the size of the database by only having to store the file extension (I think at this moment in time) versus a full URL (the random junk). I could convert and thus restrict file extensions however sometimes 256 colors just doesn't cut it.
Considering it's my first generation coding of a PHP/MySQL membership driven site (well practice script really) I'm sure there are a ton of things that can be criticized though I'm interested in what won't be drastically difficult to implement at this time. For example I'll eventually get around to splitting the client and server code (as it currently is on my life website in a matter of speaking) in to two layers though like I said this is just for practice right now and I won't get around to that (in example) until I have the functionality working as desired. It's been a lot of fun and very rewarding to work on the stuff I have of late.
Re: MySQL auto_increment but with left zeros?
Actually, that is not that bad of a reason to do what you did. I wont be the one to criticize (this time...
)
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: MySQL auto_increment but with left zeros?
Actually I could criticize my own decision or at least keep a few things in mind...
I could keep avatars in directory /upload/a and profile images in /upload/p. However even then there is the issue with the FTP only listing about 2100 files before it caps off. Something along those lines...I had to delete over the series of two or three days about 30,000+ individual email files (IMAP) that were collecting dust on my server (and taking up 700MB of space!) so I remember there is some sort of listing restriction.
One thing I suppose I could do is find the exact restriction and create subfolders. Let's say the restriction is 2,100 files. I could cap /upload/a/1 for users 1~999, upload/a/2 for users 1000~1999...etc. Well something along those lines.
In fact I think in the interest of long term planning this justifies creating a new thread!
I could keep avatars in directory /upload/a and profile images in /upload/p. However even then there is the issue with the FTP only listing about 2100 files before it caps off. Something along those lines...I had to delete over the series of two or three days about 30,000+ individual email files (IMAP) that were collecting dust on my server (and taking up 700MB of space!) so I remember there is some sort of listing restriction.
One thing I suppose I could do is find the exact restriction and create subfolders. Let's say the restriction is 2,100 files. I could cap /upload/a/1 for users 1~999, upload/a/2 for users 1000~1999...etc. Well something along those lines.
In fact I think in the interest of long term planning this justifies creating a new thread!
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact: