How does the ID play a part in all this? Is each record getting it's own unique ID through an autoincrement field? If so, when you insert, each record in each table should get the same autoincremented value, so knowing the ID for the purpose of giving each corresponding record the same ID across all 3 tables isn't really important.
BUT - If one table ever gets a record WITHOUT the 2 others getting a record as well, then you would encounter a problem; some tables would have more records than others, so the unique IDs would NOT match for the related records. Since you can't force a specific number into an autoincrement field, it would make more sense to just use one table.
If having just one table isn't a problem, you could always nest a select statement that finds the ID of the record that was last inserted and insert that into the other tables as well.
If I'm understanding correctly, you have a table w/ a generated unique identifier column and two other tables that will include this unique identifier tying the three tables together. I'm really only familiar with SQL databases, so I'll offer a solution for that and hope it helps.
I'm also assuming the data to populate the 3 tables all comes from one input form. I would use a stored procedure similar to this:
------------------------------------------
create proc test
@input1 varchar(20),
@input2 varchar(20),
@input3 varchar(20),
as
declare @myid int
insert into table1
(columnname)
values
(@input1)
set @myid = @@identity
insert into table2
(table1_ID, columnname)
values
(@myid, @input2)
insert into table3
(table1_id, columnname)
values
(@myid, @input3)
---------------------------------------------
The command line in PHP would be "exec test '$input1','$input2','$input3'"
All this just to say that, in SQL, anyway, the Unique Identifier of a row that was inserted is stored in the variable @@identity. Keep in mind it disappears as soon as any other command is run. This is why I moved it to a local variable, as it would have disappeared before the insert into the 3rd table. Hope this helps.
skicamel created a procedure in the SQL engine that does all the work, so the only thing you need is to pass the procedure the right params and the data is inserted. This way is faster cause database engines are faster than PHP, cause you don't have to exchange data between them.
Anikin
Hugo Alexandre Dias
Web-Programmer
anikin_jedi@hotmail.com
If your using MySQL then stored procedures is not yet an option (not supported as of writing this.)
You can get around this by using the function "mysql_insert_id" - which will tell you the autoincrement ID of the last successful MySQL INSERT statement...
Doesn't it lose it's position if the connection is lost and will start at 0 with a next id from 0 being 1? I'm something similar and I'm currently pondering storing the id into it's own table and updating it after each entry.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.