CODEWITHSUNDEEP
phpmysqlpdo
july23
july23

Reputation: 13

Move Data from one table to another table using PDO php

I know that this may been ask many times, I just can't find the proper keywords to search my problem, although I found several ways doing it on mysqli. Though I was wondering if anyone can help me how to this on PDO.

<?php
$dsn = 'mysql:host=localhost;dbname=dbsample';
$username = 'root';
$password = '';
$options = [];
try {
$connection = new PDO($dsn, $username, $password, $options);
} 
catch(PDOException $e) {

$id = $_GET['id'];
$sql = 'INSERT INTO table2 SELECT * FROM table1 WHERE id=:id';
$sql. = 'DELETE FROM table1 WHERE id=:id';
$statement = $connection->prepare($sql);
if ($statement->execute([':id' => $id])) {
 header("Location:.");
}

Update: here's the error i get 

Parse error: syntax error, unexpected '='

I've tried removing $sql. = but only get another error at the end.

Also tried removing the ., and same error at end Parse error: syntax error, unexpected end of file in

Upvotes: 0

Views: 1812

Answers (2)

Barmar
Barmar

Reputation: 780949

PDO doesn't allow you to execute two queries in a single call. So you need to prepare two different queries, then execute each of them separately.

You should use a transaction to ensure that the database is consistent across the two queries.

$stmt1 = $connection->prepare('INSERT INTO table2 SELECT * FROM table1 WHERE id=:id');
$stmt2 = $connection->prepare('DELETE FROM table1 WHERE id=:id');
$connection->beginTransaction();
if ($stmt1->execute([':id' => $id]) && $stmt2->execute([':id' => $id])) {
    $connection->commit();
    header("Location:.");
} else {
    $connection->rollBack();
}

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133360

first end each query with ; PDO allow multiple query but must each one must be properly declared and ternimated .. 

  $sql = 'INSERT INTO table2 SELECT * FROM table1 WHERE id=:id;';
  $sql. = 'DELETE FROM table1 WHERE id=:id';

then if you have error again could be that the schema for the two table don't match (or don't match for insert select ) so try using an explict columns declaration 

  $sql = 'INSERT INTO table2  (col1, col2, ..,coln) 
            SELECT (col1, col2, ..,coln)  
            FROM table1 
            WHERE id=:id; ';
  $sql. = ' DELETE FROM table1 WHERE id=:id';

Upvotes: 0

Related Questions