How To Use SQL SELECT INTO STATEMENT With Example In PHP

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

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

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