[PHP][MySQL] Inserting in the first tbl doesn't insert in the second tbl.

[TAViX 0]

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.

[niranjnn01 0]

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

[btherl 0]

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.

[Mchl 0]

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

[TAViX 0]

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? :'(

[btherl 0]

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?

[Mchl 0]

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)