插入带有两个变量的表名并从中选择

4xrmg8kj  于 2021-06-18  发布在  Mysql
关注(0)|答案(2)|浏览(294)

我正在尝试使用已发布的变量和另一个表中的内容将项插入表中。我不太清楚我哪里出了问题,因为table上没有任何东西。我非常困惑。这是我的密码:

$stmt = $conn->prepare("INSERT INTO Student_Choices (Username,T1_Choice,T2_Choice,T3_Choice,Current_DB)
                      VALUES (:username,:t1choice,:t2choice,:t3choice, db.DB)
                      SELECT DB FROM Current_DB as db
                      ");
$stmt->bindParam(':username', $_SESSION['username']);
$stmt->bindParam(':t1choice', $_POST["term1sport"]);
$stmt->bindParam(':t2choice', $_POST["term2sport"]);
$stmt->bindParam(':t3choice', $_POST["term3sport"]);
$stmt->execute();
im9ewurl

im9ewurl1#

要设计此插入查询,请首先使用select创建要插入的结果集。

SELECT :username AS Username,
                         :t1choice AS t1choice,
                         :t2choice AS t2choice,
                         :t3choice AS t3Choice, 
                         DB 
                    FROM Current_DB

然后将该结果集用作insert的数据源。

INSERT INTO Student_Choices 
                  (Username,T1_Choice,T2_Choice,T3_Choice,Current_DB)     
           SELECT :username AS Username,
                  :t1choice AS t1choice,
                  :t2choice AS t2choice,
                  :t3choice AS t3Choice, 
                  DB 
             FROM Current_DB

注意select操作如何替换values()子句。
(请小心,除非在select上放置适当的where子句,否则可能会插入许多行,在当前的\u db中每行插入一行。)

gpfsuwkq

gpfsuwkq2#

这个 SELECT DB FROM Current_DB as db 在内部无效 INSERT 判决。只需先执行这个查询,然后获取 DB 将值转换为变量,最后与一起使用 bindParam() 与其他参数一样:类似于:

/* Get the database name. */

$stmt = $conn->prepare("SELECT DB FROM DB_Year");
$stmt->execute();
$res = $stmt->fetchAll();
$db = $res[0]['DB'];

/* Execute the insert statement. */

$stmt = $conn->prepare(
    "INSERT INTO Student_Choices (Username, T1_Choice, T2_Choice, T3_Choice, Current_DB)
     VALUES (:username, :t1choice, :t2choice, :t3choice, :db)"
);

$stmt->bindParam(':username', $_SESSION['username']);
$stmt->bindParam(':t1choice', $_POST["term1sport"]);
$stmt->bindParam(':t2choice', $_POST["term2sport"]);
$stmt->bindParam(':t3choice', $_POST["term3sport"]);
$stmt->bindParam(':db', $db);
$stmt->execute();

相关问题