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="https://phpgurukul.com/how-to-use-multiple-insert-queries-in-php/index.php"</script>";
} 
else {
  echo "<script>alert('Something went wrong. Please try again.');</script>";  
echo "<script>window.location.href="https://phpgurukul.com/how-to-use-multiple-insert-queries-in-php/index.php"</script>";
}
}
?>

Download Full Source Code (How to use MySQL multiple insert Queries in PHP)

Size: 3.73 KB

Version: V 1.0

Source link

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top