TAViX Posted March 5, 2009 Share Posted March 5, 2009 Ok, I have 2 tables let's say first one is called batch_info with the following entries: batch_id (Primary Key - PK), trainee_num (FK), nickname (FK), etc, and passport_id with those entries: passport_id (PK), trainee_num (PK), nickname (PK), etc... As you can see in the first table, batch_id, trainee_num and nickname, are linked to the values from the second table. Now, I have this script that introduce the values in passport_info table, but the problem is that this script doesn't also update/insert the values from trainee_num and nickname also in the batch_info table, even if the entries are linked... MySQL: batch_info Links to: trainee_num: dbrev00.passport_info.trainee_num ON DELETE: cascade ON UPDATE: cascade trainee_num: dbrev00.passport_info.nickname ON DELETE: cascade ON UPDATE: cascade So my question is, how to update this code in order to have the same values in both tables?? mysql_query("INSERT INTO passport_info (trainee_num, sur_name, given_names, middle_name, nickname) VALUES ('".clean($tc)."', '".clean($sn)."', '".clean($gn)."','".clean($mn)."','".clean($nn)."')" ) or die(mysql_error()); Thanks in advance. Quote Link to comment https://forums.phpfreaks.com/topic/148045-phpmysql-inserting-in-the-first-tbl-doesnt-insert-in-the-second-tbl/ Share on other sites More sharing options...
niranjnn01 Posted March 5, 2009 Share Posted March 5, 2009 I guess you are asking for the mysql query?.. if so, then a better place to get help is the mysql forum http://dev.mysql.com/ .u will get better answers there. its a very active forum. and what every mysql query u get at the end, run it in mysql_query(); Hope this helps. Rakesh Quote Link to comment https://forums.phpfreaks.com/topic/148045-phpmysql-inserting-in-the-first-tbl-doesnt-insert-in-the-second-tbl/#findComment-777084 Share on other sites More sharing options...
btherl Posted March 5, 2009 Share Posted March 5, 2009 Phpfreaks also has a mysql expert (and it's not me). I'm going to request that this post get moved to the mysql sub-forum so he can see it. Quote Link to comment https://forums.phpfreaks.com/topic/148045-phpmysql-inserting-in-the-first-tbl-doesnt-insert-in-the-second-tbl/#findComment-777096 Share on other sites More sharing options...
Mchl Posted March 5, 2009 Share Posted March 5, 2009 Perhaps reading how table constraints actually work would help http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html In short: they do not work on inserts Quote Link to comment https://forums.phpfreaks.com/topic/148045-phpmysql-inserting-in-the-first-tbl-doesnt-insert-in-the-second-tbl/#findComment-777101 Share on other sites More sharing options...
TAViX Posted March 6, 2009 Author Share Posted March 6, 2009 Perhaps reading how table constraints actually work would help http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html In short: they do not work on inserts That's exactly what I have. So what's the solution? Any workaround of this? On short, how do I upload the same data, on the same rows but on 2 diferent tables? :'( Quote Link to comment https://forums.phpfreaks.com/topic/148045-phpmysql-inserting-in-the-first-tbl-doesnt-insert-in-the-second-tbl/#findComment-777789 Share on other sites More sharing options...
btherl Posted March 6, 2009 Share Posted March 6, 2009 Can you just do two inserts inside a transaction? Or is it possible to create your own ON INSERT trigger to do what you want done? Quote Link to comment https://forums.phpfreaks.com/topic/148045-phpmysql-inserting-in-the-first-tbl-doesnt-insert-in-the-second-tbl/#findComment-777801 Share on other sites More sharing options...
Mchl Posted March 6, 2009 Share Posted March 6, 2009 Bottom line: two INSERT queries are needed (well... in your case three - in other words: one per table). However, inserting same data into three tables might indicate that your database design is flawed (i.e. not normalised) Quote Link to comment https://forums.phpfreaks.com/topic/148045-phpmysql-inserting-in-the-first-tbl-doesnt-insert-in-the-second-tbl/#findComment-777957 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.