Page 1 of 1
How to Reset An Auto_Increment Column on every 1st january?
Posted: Sun Dec 21, 2008 4:00 am
by azhan
Hey guys,
I'm Figuring out on how to reset the query number of Auto_Increment column when come to every 1st day of the year without effecting the previous data...Do I have to make a code that detects the date every before inserting any data?..then it will reset the number back to 1 and only insert the data?
DateKKKKKKK|KKNoKKK|KKData
30/12/2008 | 23345KK| ABC
01/01/2009 | 1KKKKKK| IJK //at this date the column "No." will automatically reset to 1
03/01/2009 | 2KKKKKK| OPQ
//detects current date first
$hour= ;
$date = date("d/m,current()-$hour");
if ( $date == '01/01') {
untill here i dont the code on reseting the column...... }
else {
$insert = "INSERT INTO table SET.......... "
}
is this idea ok?
any idea guys? thanks....
Azhan
Re: How to Reset An Auto_Increment Column on every 1st january?
Posted: Sun Dec 21, 2008 6:54 am
by jaoudestudios
You could do it that way, or if you wanted to be more efficient use an event in mysql or a cron if on linux. That way you dont have to check everytime you do an insert.
Why would you want to reset this number, are you afraid you will run out?
Re: How to Reset An Auto_Increment Column on every 1st january?
Posted: Sun Dec 21, 2008 12:07 pm
by califdon
The purpose of an auto_increment column is to uniquely identify every row in a table. They are meant to never be reset. If you have other tables that relate to this table, you will have serious problems if you try to reset the primary key. If it is not the primary key, you probably should not be using the auto_increment property. I'm not even sure that you
can reset an auto_increment column unless you Drop the column and Add it again, which would renumber
all rows. Read the manual to see what they say about "resetting" an auto_increment, and also to learn how you might be able to avoid this entirely by using the year (presumably you are storing this) and an auto_increment as a multiple-column index, then it will automatically start at 1 again whenever the year value changes:
http://dev.mysql.com/doc/refman/5.0/en/ ... ement.html
So you are probably misusing the auto_increment property, if you need to have new records begin at 1 again for the new year. That would produce duplicate values in the column, so it could not be used as the primary key, which is the basic purpose of auto_increment columns.
Re: How to Reset An Auto_Increment Column on every 1st january?
Posted: Mon Dec 22, 2008 6:37 am
by azhan
The purpose i wanted to use an auto_increment column is to insert an data with its serial number according to the next query, so users doesnt need to know which is the next serial query need to be insert since it involves untill 6 digits of serial number.
I find out new idea that is....first read the latest query ( by ID limit 0,1) and plus it by 1 or (++$serial;) which make it the next number of query.
Can be done safely right? without using the auto_increment column........
u guys were right...if i reset it by 1 again...it will be duplicated with the very first number inserted....and further error would occur once try to retrieve it back....
thanks guys! appreciate it!
Azhan
Re: How to Reset An Auto_Increment Column on every 1st january?
Posted: Mon Dec 22, 2008 12:16 pm
by califdon
Yes, that's the correct way to do it. Put the logic to determine the next number in your code for adding a new record, based on the highest previous number. The only potential problem is if you have multiple users who might be adding records at almost exactly the same moment. In that case it's possible for both requests to get the same "highest" number before one of them adds the new record. That would only happen for a very busy web site, and there are other techniques if this could happen on your site. Basically, you would need another table that has only one record, containing the next number to use, and each request would update that table when it requested the next number. But that's only needed for very high traffic applications.
Re: How to Reset An Auto_Increment Column on every 1st january?
Posted: Mon Dec 22, 2008 1:50 pm
by jaoudestudios
Plus for that you would probably what to use the InnoDB engine with transactions - you know...just incase!
Re: How to Reset An Auto_Increment Column on every 1st january?
Posted: Mon Dec 22, 2008 3:43 pm
by califdon
jaoudestudios wrote:Plus for that you would probably what to use the InnoDB engine with transactions - you know...just incase!
Yes, there are all sorts of considerations for a
really high activity application. Good point.