0

I wish to copy data from table1 into table2 and table3 below is what I have tried.

$query="INSERT INTO table2,table3 (st_id, name,reg)SELECT st_id,name,reg FROM table1";
Dharman
  • 30,962
  • 25
  • 85
  • 135
Nuel Young
  • 13
  • 2
  • 1
    I never tried to copy to 2 tables at a time. But you can for one table for sure. `INSERT INTO table2 (st_id, name,reg) SELECT st_id,name,reg FROM table1`. If `st_id` is a auto_increment value, omit it : `INSERT INTO table2 (name,reg) SELECT name,reg FROM table1`. – DanB Oct 18 '19 at 13:25
  • 1
    you can't insert into multiple tables in one MySQL command – Mykhailo Bryzhak Oct 18 '19 at 13:25
  • Possible duplicate of [insert data from one table to another in mysql](https://stackoverflow.com/questions/14907863/insert-data-from-one-table-to-another-in-mysql) – dZ. Oct 18 '19 at 13:32
  • If you need to do it sometime later, after the data is inserted in table1: You should perform SELECT on table1, store values in php variables, then perform two INSERT statements to table2 and table3. If you need to do it immediately after inserting, create AFTER INSERT trigger on table1 which would INSERT same data to table2 and table3. – equi Oct 18 '19 at 13:36

2 Answers2

1
$query2="INSERT INTO table2 (st_id, name,reg)SELECT st_id,name,reg FROM table1";
$query3="INSERT INTO table3 (st_id, name,reg)SELECT st_id,name,reg FROM table1";
UncaAlby
  • 5,146
  • 1
  • 16
  • 19
-1
<?php


// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}


// firstly select the data you want to copy

$sql_1 = "SELECT st_id,name,reg FROM table1";

$result = $conn->query($sql_1);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $st_id = $row["st_id"];
        $name = $row["name"];
        $reg = $row["reg"]; 
    }

} else {
    echo "Not Found";
}


// secondly insert the data you select in the past

$stmt_1 = $conn->prepare("INSERT INTO table2 (st_id, name,reg) VALUES (?, ?, ?)");
$stmt_1->bind_param("iss", $st_id, $name, $reg);

$stmt_2 = $conn->prepare("INSERT INTO table3 (st_id, name,reg) VALUES (?, ?, ?)");
$stmt_2->bind_param("iss", $st_id, $name, $reg);

$stmt_1->execute();
$stmt_1->close();

$stmt_2->execute(); 
$stmt_2->close();

$conn->close();
Sajib Talukder
  • 99
  • 5