php - 使用 INSERT INTO SELECT 和 VALUES MYSQL PDO

Question

经过大量谷歌搜索和 StackOverflow 中的不同问题，我还没有真正找到解决我的问题的解决方案。

我需要将数据从 2 个表和 $_POST 请求插入到 MySQL 表中。

我管理了我需要的 2 个表中的信息，但我似乎无法插入 $_POST 变量。

这是我现在拥有的

$stmt = $conn->prepare("INSERT INTO user_orders (order_item_id, order_item, order_quantity, order_user, order_name, order_address, order_phone) SELECT item_ID, item_name, item_quantity, user_name FROM $user_cart, user_main WHERE item_status = 'carted' and user_name = :user_name VALUES ($order_name, $order_address, $order_phone)");
$stmt->bindParam(":user_name", $_SESSION['login_user']);
$stmt->execute();

服务器没有抛出错误，它正确执行但没有插入数据。

我遵循的逻辑是，它在 SELECT 语句之后寻找更多数据以插入到打开列的表中，但它没有获取呈现给它的 VALUES，这可能是由于我的语法错误.

问题:使用 1 个准备好的 PDO 语句从 2 个表和 $_POST 请求中插入所需数据的正确方法是什么？

Answer 1

你说:

The server doesn't throw an error, it executes properly but no data is inserted.

你是怎么得出这个结论的？如果没有错误报告和异常处理，您将无法确定。

在我的代码中有两个 TODO 注释。搜索它们并按照说明进行操作。

关于在 INSERT INTO...SELECT 语句中使用 VALUES 子句:它不起作用。实际上，您会收到“MySQL 语法错误”消息。

您必须包含 PHP 值，例如PHP 变量 $order_name、$order_address 和 $order_phone，作为 SELECT 部分中的列标识符。

关于sql语句中使用的参数标记，是这样的php.net在 mysqli::prepare 上说关于它们的页面 - php.net 没有在 PDO::prepare 上指定它页面也是，我不知道为什么不:

Note: The markers are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value. However, they are not allowed for identifiers (such as table or column names), in the select list that names the columns to be returned by a SELECT statement, or to specify both operands of a binary operator such as the = equal sign.

最后是代码:

<?php

/*
 * ============================================================
 * Set error reporting level and display errors on screen.
 * Use it ONLY ON A DEVELOPMENT SYSTEM, NEVER ON PRODUCTION!
 * If you activate it on a live system, then the users will see
 * all the errors of your system. And you don't want this!
 * ============================================================
 */
error_reporting(E_ALL);
ini_set('display_errors', 1);

try {
    // Read needed variables.
    // TODO: Provide your values.
    $user_cart_table_name = 'user_cart_table_name';
    $user_name = 'user name value';
    $order_name = 'order name value';
    $order_address = 'order address value';
    $order_phone = 'order phone value';

    // Create a PDO instance as db connection.
    // TODO: Delete this and use your own connection. 
    //       But use the first two driver options that I defined here as
    //       the options on your connection.
    $conn = new PDO(
            'mysql:host=localhost;port=3306;dbname=mydb;charset=utf8'
            , 'myuser'
            , 'mypass'
            , array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => FALSE,
        PDO::ATTR_PERSISTENT => TRUE,
            )
    );

    /*
     * The sql statement - it will be prepared.
     * 
     * Familiarize yourself with the sprintf() function.
     * It is your very good friend when building complex
     * sql statements.
     */
    $sql = sprintf('INSERT INTO user_orders (
                        order_item_id,
                        order_item,
                        order_quantity,
                        order_user,
                        order_name,
                        order_address,
                        order_phone
                    ) 
                    SELECT 
                        item_ID,
                        item_name,
                        item_quantity,
                        user_name,
                        "%s",
                        "%s",
                        "%s" 
                    FROM 
                        %s,
                        user_main 
                    WHERE 
                        item_status = "carted" 
                        AND user_name = :user_name'
            , $order_name
            , $order_address
            , $order_phone
            , $user_cart_table_name
    );

    // Prepare the sql statement.
    $stmt = $conn->prepare($sql);

    // Bind the input parameters to the prepared statement.
    $bound = $stmt->bindValue(':user_name', $user_name, PDO::PARAM_STR);

    // Execute the prepared statement.
    $executed = $stmt->execute();

    // Get the last insert id.
    $lastInsertId = $conn->lastInsertId();

    // Display last insert id.
    echo 'Record added with id ' . $lastInsertId;

    // Close connection.
    $conn = NULL;
} catch (PDOException $exc) {
    echo $exc->getMessage();
    exit();
} catch (Exception $exc) {
    echo $exc->getMessage();
    exit();
}

祝你好运!