Exporting a Line of Data From One Data Table to Another

Question

The code below is for a project I'm working on. I'm having some issues with my PHP code and am in need of help.

I have a button on my data table named "Export". When the button is clicked, I wish to copy the data on that row and move it to an archive.

<?php

    function val($data) {
        $data = trim($data);
        $data = stripslashes($data);
        $data = htmlspecialchars($data);
        return $data;
    }
    
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "databasename";
    
    $ticket_id = $_GET["ticket_id"];
    
    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 
    
    $sql = "SELECT * FROM activeticket WHERE ticket_id='$ticket_id' INSERT INTO `ticketarchive`(`name`, `account_num`, `department`, `ticket_desc`, `email`, `assigned`, `status`, `fibre_site`) VALUES ([name],[account_num],[department],[ticket_desc],[email],[assigned],[status],[fibre_site])";
    
    if ($conn->query($sql) === TRUE) {
        echo "New record created successfully. Record ID is: ";
    } else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
    
    $conn->close();
?>

Below is the error that this produces:

Error: SELECT * FROM activeticket WHERE ticket_id='1' INSERT INTO archiveticket(name, account_num, department, ticket_desc, email, assigned, status, fibre_site) VALUES ([name],[account_num],[department],[ticket_desc],[email],[assigned],[status],[fibre_site]) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT INTO archiveticket(name, account_num, department, ticket_desc, `' at line 1

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 27 '20 at 20:22
Test your query in MySQL Workbench or PhpMyAdmin first. It looks like you copy a record identified by ticket_id but not delete it from the originating table, hence it is not "moved" but copied. To correct the syntax, look at the sample in https://www.w3schools.com/SQL/sql_insert_into_select.asp — theking2, Aug 27 '20 at 21:43

score 0 · Answer 1 · answered Aug 27 '20 at 12:26

0

First execute the select query and after that execute the insert one. Right now you are trying to run them both and this is the problem.

answered Aug 27 '20 at 12:26

manqlele

77
11

score 0 · Answer 2 · answered Aug 27 '20 at 12:34

This isn't really a PHP issue you're having, you just seem to be unfamiliar with SQL. What you're trying to do is insert the result of a SELECT query into a table. This isn't the way to do it at all. What you're looking for is :

$sql = "INSERT INTO `ticketarchive`(
    `name`,
    `account_num`,
    `department`,
    `ticket_desc`,
    `email`,
    `assigned`,
    `status`,
    `fibre_site`
)
SELECT
    `name`,
    `account_num`,
    `department`,
    `ticket_desc`,
    `email`,
    `assigned`,
    `status`,
    `fibre_site`
FROM
    `activeticket`
WHERE
    `ticket_id` = $ticket_id"

For more information, read here.

I'd also advice you look into parametrized queries to avoid SQL injections.

Exporting a Line of Data From One Data Table to Another

2 Answers2