Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
creating new records in mulitple tables
- Thread starter dunskii
- Start date
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.
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.
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.
- Thread starter
- #5
This is SQL programming.
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
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...
check out:
Good Luck!
Jochem
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...
check out:
Good Luck!
Jochem
To get the last inserted id of a table
<?php
$result = mysql_query("select last_insert_id() as lastid"
;
$lastid = mysql_result($result,0);
?>
this script should be run directly after inserting the new row in the first table, cause it retrieves the last autoincremented id of the connection.
now u can use the $lastid variable to insert it into the other tables
<?php
$result = mysql_query("select last_insert_id() as lastid"
;$lastid = mysql_result($result,0);
?>
this script should be run directly after inserting the new row in the first table, cause it retrieves the last autoincremented id of the connection.
now u can use the $lastid variable to insert it into the other tables
Guest_imported
New member
- Jan 1, 1970
- 0
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.
- Status