How to increment column of table from 1000 while inserting record in database using php and mysql

[xcfx2888]

I have one table in my database which has 3 columns named id , roll_no, name resp

• now as usual I have set the id column as primary key and auto increment
• and every time a record is inserting I am assigning roll number randomly using rand() function
• but now I want to set column roll number in a way , when a new record is inserting it will increment automatically starting from 1000 and so on
• but I am not able to do it , please guide me

[igor_g]

1. Create another table where roll_no is autoincrement.

2. If you insert in your table, at first insert in another table and get last_insert_id. That will be your roll_no.

UPDATE If you have a lot of users for this table and would to hold the rule id1 < id 2 => roll_no1 < roll_no2, you should to pack both queries in stored procedure.

[benanamen]

Quite simple. For an existing table to start incrementing at one thousand, run this query with whatever table name you are using.

ALTER TABLE users AUTO_INCREMENT=1000;

Here is an example to start with a clean table

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000;