autoincrement problem

subwiz

i have a ¨id¨ column and another column ¨rootid¨. if certain condition is satisfied, the rootid field should have the same value as id (auto_increment) field. all this should be done at insertion. previously i had the logic thus:

select max(id)
based on this insert into table rootid=max(id)+1

i came across problem when i deleted some entries:

id rootid
== ======
5 5
6 6
7 7

this is what i normally got. but if i delete '6' the 7th entry becomes:

id rootid
== ======
5 5
7 6

the application gives wrong output. the workaround i had thought was:

1.insert with dummy rootid.

select max(id)
update rootid=max(id)

this requires 3 steps. is there any way i can reduce this?

[deleted]

Never ever insert data into a table based on the current max() value of an auto_increment column. It just does not work that way.

The auto_increment ID should only be used to identify records, and for NOTHING else.

subwiz

what alternate do you recommend?

smarlowe

Why do you need to reproduce the same field? I'm not sure what this is giving you.

Couldn't you just get the id twice like so:

select id, id as root_id from table foo;

Then it WILL always be the same, as it's the same field.

smarlowe

If the root_id needs to be id when the record is created but change over it's life, then set it this way:

get lastid from mysql insert:
update table foo set root_id=id where id=$lastinsertid

[deleted]

Well, first I'd like to know what you are trying to do in the first place 🙂

I can't think of any reason why I'd want to have two identical fields in a table...

smarlowe

Maybe he starts with it being the same, then changes it later to show something happened, and he just needs it to be the same up front.

The best way to do that is grab the lastid and use it to change it like:

update table foo set root_id=id where id=lastid;

subwiz

i am doing a discussion board. this faulty script demo:

http://www.indiwiz.com/discuss/linux.php
like in phpbuilder, i want to display the list of threads originating frm the parent when we r viewing a particular thread. for control over this i have the rootid passed to the script.

subwiz

i have a discussion board. the table struct is:

id (auto incr field)
parentid (0 if no parent otherwise the id of parent)
rootid (id of the originating thread)

the 3rd column is the problamatic one! if a person posts a root thread(new thread) the rootid should have the same value as the id field (obvious, don´t u think?) thats the reason...

thanks guys for the help!

subwiz

i got a idea - i will have the value "0" posted in the rootid field if it is a "root" post. i will change the program to this effect? waiting 4 ur opinion...

[deleted]

Here's how I do it:

my table looks like this:
postid, threadid, parentid

The threadid is the ID of the first post in the thread (the 'rootid')
Whenever a new thread is started, I add the post to the table, and run one single update statement that updates the new post so that the threadid is the same as the postid:

UPDATE table SET threadid = postid WHERE postid = newly_created_postid;

subwiz

that was a sound advice. tnx a lot!