How To Use SQL SELECT INTO STATEMENT With Example In PHP

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

SELECT INTO statement copies data from one table into a another table.

SQL SELECT INTO Statement

Syntax for SELECT INTO


SELECT * used to copy all columns in old table.

SELECT *
INTO newtable
FROM oldtable
WHERE condition;



SELECT column_name list used to select only few columns from old table.

SELECT column_name list
INTO newtable
FROM oldtable
WHERE condition;
 


Lets Learn.

1.Create table in database as backUpStudents table and copy all data from student table to backUpStudents table

  • create backUpStudents table.
CREATE TABLE `backupstudents` (
  `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,
  `DOB` date 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)

SELECT * INTO backUpStudents  from student; 

SQL statement copy all the data from student into backUpStudents  table.

Example(2)
SELECT StudentID,StudentName, INTO backUpStudents
FROM student;

SQL statement copy only few column from student into backUpStudents  table.
 
Select * from backUpStudents;//to check copied data 


MySQL Server doesn't support the SELECT ... INTO TABLE Sybase SQL extension.Instead, MySQL Server supports  the INSERT INTO ... SELECT standard SQL syntax, which is basically the same thing. See Section INSERT , “INSERT ... SELECT Syntax”.
 

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

<!DOCTYPE html>
<html lang="en">
<head>
    <title> SQL SELECT INTO 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 SELECT INTO 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 = "SELECT * INTO backUpStudents from student";
        $sql_2 = "Select * from backUpStudents;";
        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_2)){
            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