How to insert data into two different tables using PHP and MYSQL

Question

I am trying to insert data into two different tables using PHP and MySQL but the data is only inserted into the second table only. Can someone please explain to me why and help me fix the problem. So I read this StackOverflow solution but am not sure if this is Object Oriented style PHP because I had no luck making it work for me.

<?php
//outputing php errors in development
error_reporting(-1);
ini_set('display_errors', 1);

//adding database config
include_once 'config.php';


//processing post data
if(isset($_POST) && !empty($_POST))
{
// variables for input data

            $company_name = $_POST['company_name'];
            $company_address = $_POST['company_address'];
            $name_of_vessel = $_POST['name_of_vessel'];
            $official_vessel_number = $_POST['official_vessel_number'];
            $port_of_registry = $_POST['port_of_registry'];
            $gross_tonnage = $_POST['gross_tonnage'];
            $imo_number = $_POST['imo_number'];
            $call_sign = $_POST['call_sign'];
            $permit_number = $_POST['permit_number'];
            $date_of_issue_op = $_POST['date_of_issue_op'];
            $date_of_expiry_op =$_POST ['date_of_expiry_op'];
            $date_of_expiry_sp = $_POST['date_of_expiry_sp'];
            $date_of_issue_sp =$_POST ['date_of_issue_sp'];

// SQL query for inserting data into the database



      $stmt = $link->prepare("INSERT INTO `customers` (`company_name`,`company_address`,`name_of_vessel`,`official_vessel_number`, `port_of_registry`, `gross_tonnage`, `imo_number`, `call_sign`, `permit_number`) VALUES (?,?,?,?,?,?,?,?,?)");

      $stmt->bind_param('sssssssss',$company_name, $company_address, $name_of_vessel, $official_vessel_number, $port_of_registry, $gross_tonnage, $imo_number, $call_sign, $permit_number);

$stmt = $link->prepare("INSERT INTO `permits` (`date_of_issue_op`, `date_of_expiry_op`, `date_of_expiry_sp`, `date_of_issue_sp`) VALUES (?,?,?,?)");

      $stmt->bind_param('ssss',$date_of_issue_op , $date_of_expiry_op, $date_of_expiry_sp, $date_of_issue_sp );
      if($stmt->execute()){
            echo "<span style='background-color:green; padding:6px; color:white; font-size:16px;'>Permit created successfully </span>";
      }else{
            echo "<p align=center>Error inserting data.</p>";
            echo mysqli_error($link);
      }

}else{
       echo "<p align=center>Empty form submitted</p>";
}


?>

It looks to me like you're only performing `$stmt->execute()` after the second statement. You would probably need to execute it twice, once for each insert. — Wayne Phipps, Jan 02 '19 at 13:18
use 2 different variables : $stmt1 for the first and $stmt2 for the seconde one OR execute $stmt then prepare the second query, in your actual code, after preparing the first statment you just overwrite it with the second one — Mabrouki Fakhri, Jan 02 '19 at 13:23

score 1 · Answer 1 · answered Jan 02 '19 at 13:22

1

You have two times

$link->prepare

and the second overwrite the first

answered Jan 02 '19 at 13:22

GabrieleMartini

1,665
2
19
26

So please how do I fix this? how do I go about this – Jan 02 '19 at 13:28

score 0 · Accepted Answer · answered Jan 02 '19 at 13:37

As Mabrouki Fakhri suggested, for example:

// sql query for inserting data into database



$stmt = $link->prepare("INSERT INTO `customers` (`company_name`,`company_address`,`name_of_vessel`,`official_vessel_number`, `port_of_registry`, `gross_tonnage`, `imo_number`, `call_sign`, `permit_number`) VALUES (?,?,?,?,?,?,?,?,?)");

$stmt->bind_param('sssssssss',$company_name, $company_address, $name_of_vessel, $official_vessel_number, $port_of_registry, $gross_tonnage, $imo_number, $call_sign, $permit_number);

$stmt1 = $link->prepare("INSERT INTO `permits` (`date_of_issue_op`, `date_of_expiry_op`, `date_of_expiry_sp`, `date_of_issue_sp`) VALUES (?,?,?,?)");

$stmt1->bind_param('ssss',$date_of_issue_op , $date_of_expiry_op, $date_of_expiry_sp, $date_of_issue_sp );
  if($stmt->execute() && $stmt1->execute()){
        echo "<span style='background-color:green; padding:6px; color:white; font-size:16px;'>Permit created successfully </span>";
  }else{
        echo "<p align=center>Error inserting data.</p>";
        echo mysqli_error($link);
  }

score 0 · Answer 3 · answered Jan 02 '19 at 13:49

0

You should have two different statements, (i.e. stmt1 , stmt2 ) and prepare, bind both of them. then execute both of them on two different statements also.

answered Jan 02 '19 at 13:49

Tarreq

1,282
9
17

How to insert data into two different tables using PHP and MYSQL

3 Answers3