INSERT row from another table using php PDO

问题:

I'm new to PDO and php. I want to move a row from one table to another with a link i send to the script below.

LINK The "id" is used as a primary key in the invtable, (see FROM invtable in the script below)

submit-ads-florida.php?id=01820007985

SCRIPT submit-ads-florida.php

<?php
    $host = "localhost";
    $user = "user";
    $password = "pass";
    $database_name = "db";
    $pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));

$barCode = $GET['id'];

$query = "INSERT INTO adstable (Brand, Description, Price, Size, Price, Barcode) 
          SELECT Brand, Description, Price, Size, Price, Barcode FROM invtable 
          WHERE Barcode='".$barCode."'";

$pdo->query($query);
?>

PROBLEM

removed extra bracket by GET[id]). I'm getting the following error.

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1110 Column 'Price' specified twice' in /home/myaccount/public_html/florida-ave/submit-ads-florida.php:16 Stack trace: #0 /home/myaccount/public_html/florida-ave/submit-ads-florida.php(16): PDO->query('INSERT INTO flo...') #1 {main} thrown in /home/myaccount/public_html/florida-ave/submit-ads-florida.php on line 16

UPDATE

I corrected multiple entries of Price. No data is being add to adstable and I'm not getting any errors.

回答1:

Ok, so the issues you had/have were:

• $barCode = $GET['id']); should have been $barCode = $GET['id'];, and possibly even $_GET['id'];
• Your SELECT query selects the same field twice (SELECT Brand, Description, >Price<, Size, >Price<)
• You're also inserting in the same field twice: INSERT INTO adstable (Brand, Description, >Price<, Size, >Price<
• You're vulnerable to injection attacks, still

So let's address the issues:

$barCode = isset($_GET['id']) ? $_GET['id'] : null;//avoids undefined index notice

Next, to use the same field twice in the SELECT query, you can define an alias, but you just don't need the same field twice...

SELET SELECT Brand, Description, Price as price_1, Size, Price as price_2, Barcode FROM

Then, to protect against first degree injection attacks, let's use a prepared statement instead of calling PDO::query with a GET parameter:

$stmt = $pdo->prepare('INSERT INTO adstable (Brand, Description, Price, Size, Barcode) 
      SELECT Brand, Description, Price, Size, Barcode FROM invtable 
      WHERE Barcode=:barcode'
);
$stmt->execute([':barcode' => $barCode]);

The code, then should look something like this:

$barCode = isset($_GET['id']) ? (int) $_GET['id'] : null;
// check $barCode is valid value, if not, don't bother connecting
if ($barCode) {
    $pdo = new PDO(
        sprintf(
            'mysql:host=%s;dbname=%s;charset=utf8', // add charset here!
            $host,
            $dbName
        ),
        $user, $password,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        ]
    );
    $stmt = $pdo->prepare(
        'INSERT INTO adstable(Brand, Description, Price, Size, Barcode)
         SELECT Brand, Description, Price, Size, Barcode FROM invtable
         WHERE Barcode = :barcode'
    );
    $stmt->execute(
        [
            ':barcode' => $barCode
        ]
    );
}

That should do the trick. But seriously: error messages tell you what's wrong Read them

回答2:

Please check and correct this line: $barCode = $GET['id'];

Let me know if this is not your solution.