-2

I have three different table and want to save values from the single form into three different tables at once.

User Table:      comment table:                user_comment     
id               id                            id    
name             comment                       u_id(fk from user table)
email            trip_id (fk from trip table)  c_id(fk from comment table)

How do I insert into all three tables at once? Progress so Far

<?php

$con=mysql_connect("localhost","root","");
$db = mysql_select_db("comment", $con);


$trip = $_POST['trip'];
$name=$_POST['name'];
$email=$_POST['email'];
$title=$_POST['title'];
$comment=$_POST['comment'];

$sql="INSERT INTO comment (id, title, comment, trip_id) VALUES ('', 
'$title','$comment','$trip');
INSERT INTO user_comment (r_id) VALUES (LAST_INSERT_ID());";
$result1 = mysql_query($sql) or die(mysql_error());


 $sql2="INSERT INTO users (id, r_name, r_email) SELECT * FROM (SELECT '', 
 '$name', '$email') AS tmp WHERE NOT EXISTS (SELECT r_email FROM reviewer 
 WHERE r_email = '$email') LIMIT 1; INSERT INTO user_comment (u_id) VALUES 
 (LAST_INSERT_ID())";
 $result2 = mysql_query($sql2) or die(mysql_error());

if(mysql_affected_rows() > 0){
    echo '<div class="alert alert-success alert-dismissable">
   <a href="#" class="close" data-dismiss="alert" aria-label="close">&times 
  </a>
  Review saved
 </div>';
}
else{
    echo '<div class="alert alert-danger alert-dismissable">
  <a href="#" class="close" data-dismiss="alert" aria-label="close">&times;
  </a> You have already commented this trip!
 </div>';
dilip shrestha
  • 27
  • 7
  • Go through this [once](http://php.net/manual/en/mysqli.multi-query.php) – Rahul Sep 18 '17 at 06:03
  • the obvious answer is using 3 INSERT statements. The last one will need to take the IDs generated by the earlier ones, I would guess. Wrap it all in a transaction to ensure you don't end up with incomplete/unusuable data in the event of an error. You can write 3 statements to run via PHP, or create a stored procedure, there are a few ways to do it. What you have tried so far, and what are you stuck with? There's no code here at all, so we don't know what your exact problem is. – ADyson Sep 18 '17 at 06:03
  • You _can't_ insert to three different tables in one statement/query. The best you could do would be to use three separate insert statements within a single logical transaction. Perhaps you can elaborate on why you think you need a single query? – Tim Biegeleisen Sep 18 '17 at 06:04
  • 3
    Possible duplicate of [sql - insert into multiple tables in one query](https://stackoverflow.com/questions/3860280/sql-insert-into-multiple-tables-in-one-query) – Amal lal T L Sep 18 '17 at 06:05

2 Answers2

1

MySQL doesn't support multi-table insertion in a single INSERT statement. Oracle is the only one I'm aware of that does, oddly...

INSERT INTO NAMES VALUES(...)
INSERT INTO PHONES VALUES(...)
Amal lal T L
  • 400
  • 5
  • 20
0

try this, just add ; every query.

$sql_query = @"
               INSERT INTO User (......) VALUES (......);
               INSERT INTO comment (......) VALUES (......);
               INSERT INTO user_comment (......) VALUES (......);
              ";