使用php PDO从另一个表插入行 [英] INSERT row from another table using php PDO

问题描述

我是PDO和php的新手.我想通过链接发送到下面的脚本，将一行从一个表移动到另一个表.

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 "id"用作invtable中的主键(请参见下面脚本中的FROM invtable)

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

submit-ads-florida.php?id=01820007985

脚本 Submit-ads-florida.php

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

通过GET [id]删除了多余的括号).我收到以下错误.

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

致命错误:消息为'SQLSTATE [42000]的未捕获异常'PDOException':语法错误或访问冲突:/home/myaccount/public_html/florida-ave/submit-ads-florida中的1110列'Price'指定了两次.php:16堆栈跟踪:#0/home/myaccount/public_html/florida-ave/submit-ads-florida.php(16):PDO-> query('INSERT INTO flo ...')#1 {main}在第16行的/home/myaccount/public_html/florida-ave/submit-ads-florida.php中抛出

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

UPDATE

我更正了多个价格条目.没有任何数据添加到adstable，并且我没有收到任何错误.

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

推荐答案

好的，所以您遇到的问题是:

Ok, so the issues you had/have were:

• $barCode = $GET['id']);应该是$barCode = $GET['id'];，甚至可能是$_GET['id'];
• 您的SELECT查询选择两次相同的字段(SELECT Brand, Description, >Price<, Size, >Price<)
• 您还将在同一字段中插入两次:INSERT INTO adstable (Brand, Description, >Price<, Size, >Price<
• 您仍然容易受到注入攻击

• $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

接下来，要在SELECT查询中两次使用同一字段，您可以定义一个别名，但是您不需要两次使用同一字段...

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

然后，为了防止一级注入攻击，让我们使用准备好的语句，而不是使用GET参数调用PDO::query:

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

这篇关于使用php PDO从另一个表插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！