views:

44

answers:

2

dear all. is this possible if i want to insert some data into two tables simultanously? but at table2 i'm just insert selected item, not like table1 which insert all data. This the separate query:

$sql = "INSERT INTO table1(model, serial, date, time, qty) VALUES ('star', '0001', '2010-08-23', '13:49:02', '10')";   

$sql2 = "INSERT INTO table2(model, date, qty) VALUES ('star', '2010-008-23', '10')";   

can i insert COUNT(model) at table2? i have found some script, could i use this?

$sql = "INSERT INTO table1(model, serial, date, time, qty) VALUES ('star', '0001', '2010-08-23', '13:49:02', '10')";
$result = mysql_query($sql,$conn);
if(isset($model))
{
  $model = mysql_insert_id($conn);
  $sql2 = "INSERT INTO table2(model, date, qty) VALUES ('star', '2010-008-23', '10')";   
  $result = mysql_query($sql,$conn);
}
mysql_free_result($result);
A: 

its cant be done in one statment,

if the tables is create by innodb engine , you can use transaction to sure that the data insert to 2 tables

Haim Evgi
what's the better solution for this? can i fill the 2nd table after fill the 1st table?
klox
A: 

The simple answer is no - there is no way to insert data into two tables in one command. Pretty sure your second chuck of script is not what you are looking for.

Generally problems like this are solved by ONE of these methods depending on your exact need:

  • Creating a view to represent the second table
  • Creating a trigger to do the insert into table2
  • Using transactions to ensure that either both insert are successful of both are rolled back.
  • Create a stored procedure that does both inserts.

Hope this helps

Elemental
how about using "switch-case" command?
klox
That seems to allow you to do Insert1 OR Insert2 the and I thought you needed both Insert1 AND Insert2
Elemental