CODEWITHSUNDEEP
phpmysqlinsert
klox
klox

Reputation: 2093

How to insert same data into two tables in mysql

Is this possible if I want to insert some data into two tables simultaneously? 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);

Upvotes: 0

Views: 10335

Answers (5)

Haim Evgi
Haim Evgi

Reputation: 125466

It can't be done in one statment.

If the tables are created by innodb engine, you can use transaction to make sure that the data insert to 2 tables.

Upvotes: 0

Alexandru Alex
Alexandru Alex

Reputation: 3

<?php 

if(isset($_POST['register'])) {
    $username = $_POST['username'];
    $password = $_POST['password'];
    $email = $_POST['email'];
    $website = $_POST['website'];
    if($username == NULL OR $password == NULL OR $email == NULL OR $website == NULL) {
        $final_report2.= "ALERT - Please complete all fields!";  
    } else {
        $create_chat_user = mysql_query("INSERT INTO `chat_members` (`id` , `name` , `pass`) VALUES('' , '$username' , '$password')");
        $create_member = mysql_query("INSERT INTO `members` (`id`,`username`, `password`, `email`, `website`) VALUES ('','$username','$password','$email','$website')"); 
        $final_report2.="<meta http-equiv='Refresh' content='0; URL=login.php'>";
    }
}
?>

you can use something like this. it works.

Upvotes: -1

Brion
Brion

Reputation: 11

//if you want to insert the same as first table

$qry = "INSERT INTO table (one, two, three) VALUES('$one','$two','$three')";

$result = @mysql_query($qry);

$qry2 = "INSERT INTO table2 (one,two, three) VVALUES('$one','$two','$three')";

 $result = @mysql_query($qry2);

//or if you want to insert certain parts of table one

  $qry = "INSERT INTO table (one, two, three) VALUES('$one','$two','$three')";

  $result = @mysql_query($qry);

  $qry2 = "INSERT INTO table2 (two) VALUES('$two')";

  $result = @mysql_query($qry2);

//i know it looks too good to be right, but it works and you can keep adding query's just change the 

     "$qry"-number and number in @mysql_query($qry"")

Upvotes: 1

Elemental
Elemental

Reputation: 7466

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 inserts are successful or both are rolled back.
  • Create a stored procedure that does both inserts.

Hope this helps

Upvotes: 4

user679071
user679071

Reputation: 36

In general, here's how you post data from one form into two tables:

<?php
$dbhost="server_name";
$dbuser="database_user_name";
$dbpass="database_password";
$dbname="database_name";

$con=mysql_connect($dbhost, $dbuser, $dbpass) or die('Error connecting to the database:' . mysql_error());

$mysql_select_db($dbname, $con);

$sql="INSERT INTO table1 (table1id, columnA, columnB) 
         VALUES (' ', '$_POST[columnA value]','$_POST[columnB value]')";

mysql_query($sql);

$lastid=mysql_insert_id();

$sql2=INSERT INTO table2 (table1id, table2id, columnA, columnB)
              VALUES ($lastid, ' ', '$_POST[columnA value]','$_POST[columnB value]')";

//tableid1 & tableid2 are auto-incrementing primary keys 

mysql_query($sql2);

mysql_close($con);

?>

//this example shows how to insert data from a form into multiples tables, I have not shown any security measures

Upvotes: -1

Related Questions