Adding auto numbers

[daneth1712]

Hi,

 

I need to add into my database a field that inserts consecutive numbers starting from a 8 digit number I have selected.

I need to be able to add the next number automatically to each row in the database (there are over 100,000) and then add some php code so that when a new entry is added, it adds the next number to the new record.

 

The problem is I already have an auto increment field in the database, and am not sure how to do this.

 

Can someone please help me out?

 

Thanks in advance.

 

[kickstart]

Hi

 

Only idea I can suggest is to have a 2nd table with a single column which is an autonumber (starting at your start point), and when you want a new number you insert into that and then grab the inserted key value for use in your main table.

 

All the best

 

Keith

[daneth1712]

Hi,

 

thanks for your response, is there no way to do this via some kind of generator?

 

I thought php had this function?

 

Thanks

[Mchl]

In MySQL 5+ you can use ON INSERT trigger for that

[daneth1712]

Hi Mchl

 

I am using MySQL version 5.1.36.

 

Could you kindly explain to me how to do this?

sorry for my noobishness with this.

 

Thanks

[Mchl]

First off familiarise yourself with the manual documentation for triggers. Check out the examples provided and see if you can try to write it yourself.

 

http://dev.mysql.com/doc/refman/5.1/en/triggers.html

[Mr_J]

Hi,

I`m no guru but wont rand or srand work? I`m sure you can generate numbers in sequence in PHP and simply store the number into its own field/column

[Psycho]

What's wrong with using a standard auto-increment INT field and start it at whatever value you want. It's true that it won't be padded to 8 characters, but that is a display issue. By keeping the field as an INT type you get much more flexibility; e.g. select where field > [some_value].

 

Then whenever you want to display the field simply use string_pad() or some other methos to display with it padded to eight characters

[Mchl]

What's wrong with using a standard auto-increment INT field and start it at whatever value you want.

 

The problem is I already have an auto increment field in the database, and am not sure how to do this.

[daneth1712]

Hi guys,

 

Thanks for your help, but I managed to do it another way.

 

I added the code below in case anyone is interested.... just have to add script to insert next number into new record, which is the easy bit.

 

<?php
$d="10085201";

$db = mysql_connect("$host", "$user", "$pass") or die ("Error connecting to database.");
mysql_select_db("$database", $db) or die ("Couldn't select the database.");
$sql = "select id from $users ORDER by id asc";
$result=mysql_query($sql,$db) or die(mysql_error()); 

while($row=mysql_fetch_array($result)) { 
$reg=$row['id']; 
$sql2= "update $users SET d_id = '$d' WHERE id='$reg'";
$d ++;
$result2=mysql_query($sql2 ,$db) or die(mysql_error());
}
?>

[kickstart]

Hi

 

Problem with this solution is when you come to add new rows in the future. If 2 scripts run simultaneously then the value assigned might not be what you expect.

 

All the best

 

Keith

[daneth1712]

Hi Kevin,

 

This script was only meant to be used once, all I needed to do was add a consecutive number to each record on the database. now I have done that, the script above is going to be binned. I will now use another bit of code to make sure the last record is checked and next number is added to new record.

 

Thanks for everyones help!

[Psycho]

What's wrong with using a standard auto-increment INT field and start it at whatever value you want.

 

The problem is I already have an auto increment field in the database, and am not sure how to do this.

 

OK, so use THAT field for this number. Just start the auto-increment from the number you need to start at and, as I stated above, use PHP to format the number appropriately for display purposes. Maybe I am missing something, but it would seem that two auto-increment fields for a table are superfluous.

[Mchl]

It probably is used as a foreign key in several other tables. Changing its values would be tedious. It doesn't have to be continuous as some rows might have been removed in the past etc...