How to transfer a row from one table to another?

Question

I have tried using the method of inserting into desired table select * from XXX then deleting it away, but I just can't seem to insert into my new table and delete the one in the old table.

It reads the trade_id as 1,2,3,4 but I can't seem to get it to delete.There is an error on line 4 , trade_id which i cant seem to get why it is not defined.

Is there anything wrong with the query?

Delete.php

<?php

        // we need to know the student id so that we can delete the right student
        $tradeid= $_GET['trade_id'];

        // the file that contains your database credentials like username and password
        require_once('connect.php');

        // see Lecture Webp_Week13_14_Using_PHPandMySQL(updating).pptx Slide 4 aka Step 1
        $mysqli = new mysqli($database_hostname, $database_username, $database_password, $database_name) or exit("Error connecting to database"); 

        // Slide 5 aka Step 2
        $stmt = $mysqli->multi_query("INSERT INTO `trade_history1` (session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close)
        SELECT session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close
        FROM `opentrades`
        WHERE `trade_id` = ?;

          DELETE FROM `opentrades` WHERE `trade_id` = ?;
          COMMIT;"); 

        // Slide 6 aka Step 3 the bind params must correspond to the ?
        $stmt->bind_param("i", $tradeid); // 1 ? so we use i. we use i because  id is INT

        // Slide 7 aka Step 4
        $successfullyDeleted = $stmt->execute(); 

        // Slide 8 aka Step 5
        // we won't check the delete result here.

        // Slide 9 aka Step 6 and 7
        $stmt->close();

        $mysqli->close();

        // if we successfully delete this, we 
        if ($successfullyDeleted) {
            $_SESSION['message'] = 'Successfully deleted';
        } else {
            $_SESSION['message'] = 'Unable to delete';
        }

        header('Location: js.php');

?>

Important portion of code

JS.php

while($row = mysql_fetch_array($result)){   //Creates a loop to loop through results
    echo "<tr><td>" . $row['trade_id'] . "</td><td>" . $row['selection'] . "</td><td>" . $row['date'] ."</td><td>" . $row['type'] ."</td><td>" . $row['size'] ."</td><td>" . $row['bidprice'] ."</td><td>" . $row['offerprice'] ."</td><td>" . $row['stoploss'] ."</td><td>" . $row['takeprofit'] ."</td><td>" . $row['profitandloss'] . "</td><td><a href ='delete.php?id=".$row['trade_id']."'>X</a></td></tr>";  //$row['index'] the index here is a field name
}

You can only run _one_ query in a `mysql_query()` call (so use 2 seperate calls, preferably in a transaction). Also, don't use the _deprecated_ `mysql_*` functions anymore, there's a warning in the manual for a reason. — Wrikken, Aug 11 '13 at 18:08
2 calls, or you could use a procedure and call it. Procedure could execute both statements then. — xQbert, Aug 11 '13 at 18:17
@NigelHow what you want is to stop using **`mysql_*`** for good as it is deprecated and migrate to MySQLi or PDO, MySQLi you have [**multi_query**](http://php.net/manual/en/mysqli.multi-query.php) which accepts what you want to do. — Prix, Aug 11 '13 at 18:25
I have edited the code to suit mysqli however it still wont work. ' — Nigel How, Aug 11 '13 at 18:31
The most important point here is that the trade_id is undefined , which i dont get. — Nigel How, Aug 11 '13 at 18:33
Look at the form submitting this information and make sure the variable is called `trade_id`. If line four is wrong (`$tradeid = $_GET['trade_id']`) that implies there is no variable called `trade_id` set in the $_GET array. — Benny Hill, Aug 11 '13 at 19:22

score 1 · Accepted Answer · edited May 23 '17 at 10:31

mysqli::multi_query performs a query (one or multiple queries) on the database. It does not create a prepared statement (like mysqli::prepare) to be used with mysqli_stmt::bind_param and executed with mysqli_stmt::execute. Furthermore, the query parameter in the prepared statement must consist of a single SQL statement.

You should do a transaction (No prepared statements!) this way (taken from PHP + MySQL transactions examples):

$tradeid= filter_var($_GET['trade_id'], FILTER_SANITIZE_NUMBER_INT);
require_once('connect.php');
$mysqli = new mysqli($database_hostname, $database_username, $database_password, $database_name) or exit("Error connecting to database");
try {
    // First of all, let's begin a transaction
    $mysqli->begin_transaction();

    // A set of queries; if one fails, an exception should be thrown
    $mysqli->query("INSERT INTO `trade_history1` (session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close)
    SELECT session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close
    FROM `opentrades`
    WHERE `trade_id` = " . $tradeid);
    $mysqli->query("DELETE FROM `opentrades` WHERE `trade_id` = " . $tradeid);

    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $mysqli->commit();
    $_SESSION['message'] = 'Successfully deleted';
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $_SESSION['message'] = 'Unable to delete';
    $mysqli->rollback();
}
$mysqli->close();
...

or with prepared statements (from How can I use prepared statements combined with Transactions with PHP?):

$tradeid= $_GET['trade_id'];
require_once('connect.php');
$mysqli = new mysqli($database_hostname, $database_username, $database_password, $database_name) or exit("Error connecting to database");
try {
    // First of all, let's begin a transaction
    $mysqli->begin_transaction();

    // A set of queries; if one fails, an exception should be thrown
    $stmt =  $mysqli->stmt_init();
    $stmt = $stmt->prepare("INSERT INTO `trade_history1` (session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close)
    SELECT session_id, trade_id, selection, date, type, size, bidprice, offerprice, stoploss, takeprofit, profitandloss, dateclose, close
    FROM `opentrades`
    WHERE `trade_id` = ?");
    $stmt->bind_param("i", $tradeid);
    $stmt->execute();

    $mysqli->query("DELETE FROM `opentrades` WHERE `trade_id` = ?");
    $stmt->bind_param("i", $tradeid);
    $stmt->execute();

    // If we arrive here, it means that no exception was thrown
    // i.e. no query has failed, and we can commit the transaction
    $mysqli->commit();
    $_SESSION['message'] = 'Successfully deleted';
} catch (Exception $e) {
    // An exception has been thrown
    // We must rollback the transaction
    $_SESSION['message'] = 'Unable to delete';
    $mysqli->rollback();
}
$mysqli->close();
...

There is a problem with BeginTransaction() and the trade id not being defined. where could be the problem? This is my personal project i have been working on for a quite some time! — Nigel How, Aug 12 '13 at 07:40
trade_id is a GET variable that should be found in the URL requested like http://example.com?trade_id=0 — Mohsenme, Aug 12 '13 at 07:59
yeah i have a rows limited by many columns which are being called from database. This table is called opentrades where i retrieve them. And i want to bring over from opentrades to trade_history1 but what i dont get is why the trade_id cannot be GET. — Nigel How, Aug 12 '13 at 08:11
It can be, I simply copied it from your code. You can treat it like other variables: $tradeid=5; — Mohsenme, Aug 12 '13 at 08:25
Because i want to delete trade_id defined by each row. This part X"; has no problem because the web url defined the trade_id as (eg 1,2 ,3,4) and it is fine. The problem lies in the defining of the trade_id as the row's trade id and the begin transaction which is undefined. — Nigel How, Aug 12 '13 at 08:41

How to transfer a row from one table to another?

1 Answers1