Incrementing a field in SQL using PHP

Question

I have a table (T1) with some fields F2, F3 (Columns) with already some values in it .... .

I need to add a new field (F1) to the table (T1) and F1 will be the primary key .

The field type F1 is a integer .

I assigned a random 8 digit number to the first row of F1 and the remaining rows should be incremented by 1 from the previous row. This increment should go in par with the other fields F2 , F3 etc...

This F1 should also be increased if entirely new row is added to the table.

I tried to do it with lastinsertid() in PHP but it only works for rows which are newly created.

Can u ppl help me in writing the code for the above using lastinsertid().

If its not clear plzz let me know..

Would it not be easier to set F1 as an auto-incrementing integer? Then it will increment every time a new row is added automatically. — James Allardice
– James Allardice, Commented Jul 5, 2011 at 23:08
I'd like to help you, but it's not really clear to me what you mean. You have an existing Table with 2 columns and you want to add a new column to that?? If you set your F1 to auto_increment, it will automatically add 1 to the id of the previous row. So you can just INSERT (F2, F3) VALUES ('val1', 'val2'); and F1 will automatically be assigned. — Jules
– Jules, Commented Jul 5, 2011 at 23:10
Well i need to only use the function lastInsertId function in PHP.. what lastinsertid does is that Returns the ID of the last inserted row or sequence value .. — Dumb_Shock
– Dumb_Shock, Commented Jul 5, 2011 at 23:13
@razzz manzzz - Assuming you're using MySQL: dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html — James Allardice
– James Allardice, Commented Jul 5, 2011 at 23:14

ypercubeᵀᴹ · Accepted Answer · 2011-07-05 23:47:14Z

4

If you are using MySQL:

ALTER TABLE myTable
AUTO_INCREMENT = 1677216 ,
ADD COLUMN F1 INT PRIMARY KEY AUTO_INCREMENT ;

Existing rows will have the F1 field automatically filled with ids, starting from 1677216.

If you are worried that it is an integer and not a string, pretend it's a string by never using id, only use RIGHT(id,8)

edited Jul 5, 2011 at 23:47

answered Jul 5, 2011 at 23:39

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Incrementing a field in SQL using PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related