Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
514 views
in Technique[技术] by (71.8m points)

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...