How To Use SQL INSERT INTO SELECT STATEMENT In PHP

Posted By Bajarangi soft , Posted On 18-09-2020

INSERT INTO SELECT statement copies data from one table inserts it into new table In INSERT INTO SELECT statement datatype and column names should matches while copying from one table to another table

Syntax SQL INSERT INTO SELECT Statement:

to copy all data from one table to another table use this below syntax

INSERT INTO table2 
SELECT * FROM table1 
WHERE condition;

to copy only few column data from one table to another table use this below syntax

INSERT INTO table2 (column name list)
SELECT column name list
FROM table1
WHERE condition;

Lets Learn.
1.Create table in database as college_student table and copy all data from student table to college_student table

create college_student table.

CREATE TABLE `college_student` (
  `StudentID` int(11) NOT NULL,
  `StudentName` varchar(100) NOT NULL,
  `ParentsContact` varchar(100) NOT NULL,
  `Address` varchar(100) NOT NULL,
  `Country` varchar(100) NOT NULL,
  `Section` varchar(100) NOT NULL,
  `Branch` varchar(50) NOT NULL,
  `Percentage` int(11) NOT NULL,
  `Fee` int(11) NOT NULL,
  `CourseID` varchar(220) NOT NULL
)

Student table.

StudentID	StudentName	Address	Branch	Percentage	Fee	CourseID
1	Liam	23 Andrew road	ISE	66	66000	5
2	Andrew	2415 Anthony market str	ISE	80	65000	6
4	Ryan	18 Christopher sq.	ME	67	70000	1
5	Charles	878 Eli Berguvsvägen 8	ECE	52	75000	8
8	Anikth	23 Andrew road	Civil	66	66000	3
9	Andrew	2415 Anthony market str	Civil	80	65000	3
10	Bhavya	18 Christopher sq.	Civil	67	7000	3

Example(1)

INSERT INTO college_student  SELECT * FROM student;

using above query copies all columns from student table to college_student table

Example(2)

INSERT INTO college_student (StudentName, Address, Country)
SELECT StudentName,Address,Country FROM student

using above query copies only StudentName ,Address ,Country columns from student table to college_student table

Example(3)

INSERT INTO college_student (StudentName, Address, Country) 
SELECT StudentName, Address, Country FROM student WHERE Country='Germany'

using above query copies only StudentName ,Address ,Country columns from student table to college_student table if country is equal to Germany.

2.Complete code of SQL INSERT INTO SELECT Statement in PHP script.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>SQL INSERT INTO SELECT Statement</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
</head>
<body>
<div class="container">
    <br>
    <br>
    <div class="text-center">
        <h1>SQL INSERT INTO SELECT Statement</h1>
    </div>
    <br>
    <div class="well">
        <?php
        $connect = mysqli_connect("localhost", "root", "", "school_management");
        if($connect === false){
            die("ERROR: Could not connect. " . mysqli_connect_error());
        }
        $sql_1 = "INSERT INTO college_student SELECT * FROM student";
        //$sql_2 = "INSERT INTO college_student (StudentName, Address, Country) SELECT StudentName,Address,Country FROM student";
       // $sql_3 = "INSERT INTO college_student (StudentName, Address, Country) SELECT StudentName, Address, Country FROM student WHERE           
                       Country='Germany'";
        $sql_4 = "Select * from college_student;";
        if(mysqli_query($connect, $sql_1)){
            echo "<br><h1 class='text-success'>Data copied successfully.</h1>";
        } else{
            echo "ERROR: Could not able to execute $sql_1. " . mysqli_error($connect);
        }

        if($result_1 = mysqli_query($connect, $sql_4)){
            if(mysqli_num_rows($result_1) > 0){
                echo "<table class='table'>
                    <tr>
                     <th>StudentID</th>
                     <th>StudentName</th>
                     <th>Address</th>
                     <th>Branch</th>
                     <th>Percentage</th>
                     <th>Fee</th>
                     <th>CourseID</th>
                    </tr>
                    ";
                while($row = $result_1->fetch_assoc()){
                    echo "<tr>
                   <td>".$row['StudentID'] /*student table*/ ."</td> 
                   <td>".$row['StudentName']."</td>
                   <td>".$row['Address']."</td>
                   <td>".$row['Branch']."</td>
                   <td>".$row['Percentage']."</td>
                   <td>".$row['Fee']."</td>
                   <td>".$row['CourseID']."</td>
                  </tr>";
                }
                echo "</table>";
            } else{
                echo "No records found.";
            }
        }

        mysqli_close($connect);
        ?>
    </div>
    <br>
</div>
</body>
</html>

Related Post