How To Use SQL INSERT INTO SELECT STATEMENT In PHP

admin_img 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

SQL INSERT INTO SELECT Statement,

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