I am trying to set up a recursive relationship via PHP in a MYSQL database. It's easy enough when I want to insert a new record related to an existing record, but sometimes I want to make a new record related to itself.
Simple example:
INSERT INTO idtest SET
(id='NULL' //this is auto_increment
otherid = (same as what got put into 'id')
);
If this was going to be the case all the time, I would just auto_increment both fields. But sometimes I need to relate the new record to an existing record.
Anyone have any idea how I could relate a new record to itself by adding the value of the auto increment field to another field at the same time?
In theory I could make it a two step process by not setting 'otherid' and then immediately performing an UPDATE to set 'otherid' of the newest record to the 'id' of itself, but I think that creates the possibility of messing up my tables.
Preferably there's a nice way to create the sql statement that will do this in one step that I'm just ignorant of. Any help is much appreciated 🙂
