Discuss auto increment in the PHP Development forum on Dev Shed. auto increment PHP Development forum discussing coding practices, tips on PHP, and other PHP-related topics. PHP is an open source scripting language that has taken the web development industry by storm.
Receive the tools necessary to be the rock star of your field. Our 12-month program teaches you the evolving world of multi-channel marketing as well as the complex issues and opportunities found in the industry.
ASP Free and Iron Speed Designer are giving away $5,500+ in FREE licenses. Iron Speed's RAD CASE toolset can save up to 80% of your coding time. One free license per week, one perpetual license per month! Download and Activate to enter!
Web development can be a daunting task, even for specialists. There is a lot of information to absorb and a lot of technologies to learn in order to manage a superior website. When trying to learn the ropes, developers need a reliable source to introduce new ideas that can be easily implemented. When working on large projects, even web veterans may run into a technology or an aspect of a technology that they are unfamiliar with.
Posts: 131
Time spent in forums: 7 h 10 m 59 sec
Reputation Power: 9
auto increment
hi
i have a number of tables which have an auto increment field. how can i set the format for the auto increment i.e. 04xxx where 04 is the year and the xxxx is a number starting at 0001. i have made my tables using phpmyadmin
Posts: 5,163
Time spent in forums: 6 Days 1 h 34 m 20 sec
Reputation Power: 790
You can't reliably do this with an auto-increment field. It is possible to set a value in and the field should increment to the next value. i.e. insert a record with an id of 40000 and the next record would be 40001. But if you have more than 9999 records inserted in a year then it will be wrong.
Best bet would be to add a colum that contains the year if you want to keep up with it for each record, or better yet use a timestamp. That way you can pull year, month, day, time, etc for each record according to need.
Posts: 49
Time spent in forums: 1 h 4 m 28 sec
Reputation Power: 9
several solutions are possible. Here are some that I may try:
1) test to see if the table is empty, if it is force the auto-increment to start at 040001
PHP Code:
$query = 'select * from table';
$result = mysql_query($query);
$num_rows = mysql_num_rows($result); //count the number of rows already in the table
if($num_rows == 0) //if there are no rows in the table, force the auto-increment field to start at 04001
{
$query = "insert into table values ('04001', 'field2', 'field3')";
$result = mysql_query($query);
}
else //if there are already rows in the table, allow the auto-increment to continue from 04001
{
$query = "insert into table values ('', 'field2', 'field3')";
$result = mysql_query($query);
}
This soulution assumes that you would be starting the table with no information already in the table, which would allow the first row to be made the 04001, and it would continue from there. (Only problem would be that after 999 entries, the year would auto change to 05, even if it were still in 2004)
2) Let the autoincrement stay as it is, and insert a new coloumn that would be hard inserted by you. Ex:
PHP Code:
$year = date('y'); //get the two digit year here
$query = 'select * from table'; //select all rows already in the table
$result = mysql_query($query); //get the result of the query
$num_rows = mysql_num_rows($result); //cont the number of rows in the table
$num_rows += 1; //add one to the number of rows for the next entry
if(strlen($num_rows < 3) //if the num_rows doesn't match your 04xxx format
//do a padding of the string to match your xxx format here
$query = "insert into table values ('', '".$year.$num_rows."', 'field3', 'field4')";
$result = mysql_query($query);
This is most likly what I would do, so to be sure that the year will always be the correct year.
Posts: 5,163
Time spent in forums: 6 Days 1 h 34 m 20 sec
Reputation Power: 790
xmadness, one suggestion there:
don't select everything in the entire table to get a count of how many rows there are in it.
use something like the following:
PHP Code:
$sql = 'SELECT COUNT(*) AS cnt FROM table';
$result = mysql_query($sql);
$count = mysql_result($result, 0, 'count');
Imagine pulling the entire contents of the table when the table has over a million rows in it.
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
