0

I'm trying to create a form that enters information into two tables. In the table1 I'd like to enter the contact's information. In the table2 enter their comment.

Then I want to update the table2 with the id that was generated with the insert into table1. The form is breaking on the insert into table2. I tested out the SQL and it worked for the second table insert.

    $sql="INSERT INTO table1 (firstname, email, phone) 
          VALUES ('".$Name."','".$Email."', '".$Phone."')";

    $sql.="INSERT INTO table2 (notes_comments, created_date) 
           VALUES ('".$comments."','".$date."')";

    $sql.="UPDATE table2, table1
        SET  table2.notes_quote_id = table1.id
        WHERE table1 = '".$date."'
        AND table2.notes_comments = '".$comments."'
        AND table2.created_date =  table1.created_date";

What am I doing wrong?

BELOW is the corrected statement that now works for me:

$sql="INSERT INTO table1 (firstname, email, phone) 
      VALUES ('".$yourName."','".$yourEmail."', '".$yourPhone."');";
$sql.="INSERT INTO table2 (notes_comments, created_date) 
      VALUES ('".$comments."','".$datefix."');";
$sql.="UPDATE table2 
      INNER JOIN table1 
      ON table2.created_date = table1.created_date 
      SET table2.notes_quote_id = table1.id 
      WHERE table1.id = (SELECT MAX(id) FROM table1);";
N13Design
  • 85
  • 2
  • 2
  • 12
  • 3
    Multiqueries (if supported by api) should be divided by `;`. – u_mulder Sep 29 '16 at 15:37
  • You are comparing table1 with $date. I think it should be something like table1.date. – Rax Weber Sep 29 '16 at 15:40
  • Not a fan of this method because it can be tough to sort what part of a multi query is failing, but yes, queries need to end with ';' so mysql can tell where they end. `".$Phone."');";` `$date."');";` ` table1.created_date;";` – Duane Lortie Sep 29 '16 at 15:41
  • Adding the semicolon worked for the first two insert statements. The Update didn't work. @DuaneLortie - Is there another way to insert form data into multiple tables other than running a multi query? – N13Design Sep 29 '16 at 17:25
  • if you are using mysqli, you could get the insert ID from table1, then your 3rd qry is much easier. After 1st insert, `$insertID =mysqli_insert_id($link));` then you can use $insertID for the update rather than a subquery – Duane Lortie Sep 29 '16 at 23:37

0 Answers0