is this possible?

[ptep]

Hi, as you can see im fairly new (first post even!) and i have a question that im hoping someone might be able to help me out with:

I have 2 tables, lets call them tbl1 and tbl2, and what i would like to happen is if a new entry in tbl1 is created with ID being the primary key, i would like a series of values to be put into tbl2 under that ID, also there needs to be four sets of these series of values to be entered into tbl2 with each new ID in tbl1.

If anyone could help me out it would be really appreciated

Fraser

Edit - Just read a sticky so i thought id add im using MySQL v5.0

[chazzy]

Use a trigger

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

[Ultimater]

Triggers is one way.
PROCEDUREs and FUNCTIONs is another way.
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

You could also get the auto_increment ID into a variable after the query and use it in your next query.
The safest way I've found is to include a UUID (Universal Unique Identifier) field in your table for an easy select.


$uuid=mysql_result(mysql_query("SELECT UUID()"),0);
mysql_query('INSERT INTO tbl1 (a,b,c,uuid) VALUES(1,2,3,'.$uuid.')');
$id=mysql_result(mysql_query("SELECT id from tbl1 WHERE uuid='".$uuid."'"),0);
mysql_query('INSERT INTO tbl2 (id,a,b,c,d) VALUES('.$id.',1,2,3,4)');



You can also use MySQL's LAST_INSERT_ID() function.

mysql_query('INSERT INTO tbl1 (a,b,c) VALUES(1,2,3)');
mysql_query('INSERT INTO tbl2 (id,a,b,c,d) VALUES(LAST_INSERT_ID(),1,2,3,4)');


Or PHP's mysql_insert_id function.

mysql_query('INSERT INTO tbl1 (a,b,c) VALUES(1,2,3)');
$id=mysql_insert_id();
mysql_query('INSERT INTO tbl2 (id,a,b,c,d) VALUES('.$id.',1,2,3,4)');

[ptep]

Thanks for your help guys, youve been most helpful; im going to go have a good go at this and ill post back how i got on :)

Fraser

[chazzy]

also, just so you know, the big differences here are:

- stored procedures need to be invoked separately, where as triggers would always be run
- stored procedures are less resource dependent than a trigger
- doing it at the application level only guarantees that the application runs this way, if you have other clients that can connect to the system, it could be a problem.