How to use multiple insert queries in PHP

In this tutorial, we will learn how to use multiple insert queries in PHP. With mysqli_multi_query, we can create a bunch of insert statement and run them with a single submission.

In this tutorial, we used three MySQL Table. Structure of each table given below :

• tblemployee
• tbleducation
• tblexperience

tblexperience Structure

CREATE TABLE tblemployee (
  id int(11) NOT NULL,
  EmpName varchar(120) DEFAULT NULL,
  MobileNumber bigint(12) DEFAULT NULL,
  EmailId varchar(120) DEFAULT NULL,
  EmpAddress varchar(255) DEFAULT NULL,
  RegDate timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tbleducation structure

CREATE TABLE tbleducation (
  id int(11) NOT NULL,
  MobileNumber bigint(12) DEFAULT NULL,
  HighestEducation varchar(200) DEFAULT NULL,
  CollegeSchoolName varchar(255) DEFAULT NULL,
  PassingYear int(11) DEFAULT NULL,
  CgpaPercentage varchar(15) DEFAULT NULL,
  PostingDate timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblexperience Structure

CREATE TABLE tblexperience (
  id int(11) NOT NULL,
  MobileNumber bigint(12) DEFAULT NULL,
  CompanyName varchar(250) DEFAULT NULL,
  TotalExp varchar(100) DEFAULT NULL,
  CurrentCtc decimal(10,0) DEFAULT NULL,
  NoticePeriod int(11) DEFAULT NULL,
  PostingDate timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now, We have to create an HTML form with three different part. One is for personal information, the second is for educational information and the third one is for professional information. We will save this information in the three different MySQL tables with a single submission.

HMTL FORM (index.php)

 <form method="post">
<div class="container register-form">
            <div class="form">
                <div class="note">
                    <p>How to use Insert Multiple Queries in PHP</p>
                </div>

                <div class="form-content">
                    <h5 style="color:blue" align="center">Personal Information</h5>
                    <hr />
                    <div class="row">
                        <div class="col-md-6">
                            <div class="form-group">
                                <input type="text" class="form-control" name="fullname" placeholder="Your Name *" required="true" />
                            </div>
                            <div class="form-group">
                                <input type="text" class="form-control" name="phonenumber" placeholder="Phone Number *" required="true"/>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="form-group">
                                <input type="email" class="form-control" name="emailid" placeholder="Email id" required="true"/>
                            </div>
                            <div class="form-group">
                                <input type="text" class="form-control" name="address" placeholder="Address *" required="true"/>
                            </div>
                        </div>
                    </div>
                    <hr />
                    <h5 style="color:blue" align="center">Education Information</h5>
                    <hr />
                    <div class="row">
                        <div class="col-md-6">
                            <div class="form-group">
                                <input type="text" class="form-control" placeholder="You Highest education *" name="highesteducation" required="true" />
                            </div>
                            <div class="form-group">
                                <input type="text" class="form-control" placeholder="Passing Year *" name="edpassyear" required="true"/>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="form-group">
                                <input type="text" class="form-control" placeholder="College / School Name" name="csnmae" required="true"/>
                            </div>
                            <div class="form-group">
                                <input type="text" class="form-control" placeholder="CPGA / Pecbcentage *" name="cgpapercentage" required="true"/>
                            </div>
                        </div>
                    </div>

<hr />
                    <h5 style="color:blue" align="center">Professional Information</h5>
                    <hr />
                    <div class="row">
                        <div class="col-md-6">
                            <div class="form-group">
                                <input type="text" class="form-control" placeholder="Current Company *" name="ccompany" required="true" />
                            </div>
                            <div class="form-group">
                                 <input type="text" class="form-control" placeholder="CTC *" name="ctc" required="true"/>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="form-group">
                                  <input type="text" class="form-control" placeholder="Total Experience *" name="totalexp" required="true"/>
                           
                            </div>
                            <div class="form-group">
                                  <input type="text" class="form-control" placeholder="Notice Period in Days *" name="noticep" required="true"/>
                            </div>
                        </div>
                    </div>
                    <button type="submit" class="btnSubmit" name="submit">Submit</button>
                </div>
            </div>
        </div>
        </form>

PHP Code for data insertion. Put this code at the top of the index.php page. In this example, we create the $sql variable and keep adding insert statements. Then run it.

<?php
include_once('config.php'); // Database connection file
if(isset($_POST['submit']))
{
//Personal Information
$fullname=$_POST['fullname'];
$mobileno=$_POST['phonenumber'];
$emailid=$_POST['emailid'];
$address=$_POST['address'];
// Education Information
$hedu=$_POST['highesteducation'];
$edpyear=$_POST['edpassyear'];
$collegeschoolname=$_POST['csnmae'];
$cgpapertge=$_POST['cgpapercentage'];

// Experience Information
$ccompany=$_POST['ccompany'];
$ctc=$_POST['ctc'];
$texp=$_POST['totalexp'];
$noticep=$_POST['noticep'];
//Insert queries
$sql="INSERT INTO tblemployee(EmpName,MobileNumber,EmailId,EmpAddress) VALUES ('$fullname','$mobileno', '$emailid','$address');";
$sql.="INSERT INTO tbleducation(MobileNumber,HighestEducation,CollegeSchoolName,PassingYear,CgpaPercentage) VALUES ('$mobileno','$hedu','$collegeschoolname','$edpyear','$cgpapertge');";
$sql.="INSERT INTO tblexperience(MobileNumber,CompanyName,TotalExp,CurrentCtc,NoticePeriod) VALUES ('$mobileno','$ccompany','$ctc','$texp','$noticep')";
$query = mysqli_multi_query($con, $sql);
if ($query) {
echo '<script>alert("Record submitted successfully")</script>';
echo "<script>window.location.href='index.php'</script>";
} 
else {
  echo "<script>alert('Something went wrong. Please try again.');</script>";  
echo "<script>window.location.href='index.php'</script>";
}
}
?>