php MySQL INSERT 值引用另一个表

Question

在下面的代码中，我最关心的是最后三个变量。 php 变量($ae、$pe 和 $de)将用户的全名表示为字符串。

我正在尝试将 users 表中的用户 ID 插入到 job_schedule 中。

目前，我的结果将所有数据插入表中，但将“0”放入最后三列(AE、PE、DE)的每条记录中，就好像无法找到USERID.

使用 PDO:

$res =  $db_qms->prepare("INSERT INTO `job_schedule` (`JID`, `HPL`, `WO`, `DESCRIP`, `MFG_LINE`, `CUSTOMER`, `AE`, `PE`, `DE`) VALUES (:jid, :hpl, :wo, :descrip, :mfg, :customer, :ae, :pe, :de)");
  $res->execute(array(
    ":jid" => $id,
    ":hpl" => $hpl,
    ":wo" => $wo,
    ":descrip" => $descrip,
    ":mfg" => $mfg,
    ":customer" => $customer,
    ":ae" => "SELECT `USERID` FROM `users` WHERE " . $ae . " LIKE (CONCAT(`users`.`FIRSTNAME`, ' ' , `users`.`LASTNAME`))",
    ":pe" => "SELECT `USERID` FROM `users` WHERE " . $pe . " LIKE (CONCAT(`users`.`FIRSTNAME`, ' ' , `users`.`LASTNAME`))",
    ":de" => "SELECT `USERID` FROM `users` WHERE " . $de . " LIKE (CONCAT(`users`.`FIRSTNAME`, ' ' , `users`.`LASTNAME`))"
  ));

我还尝试查看我的 SELECT 语句是否正确。因此，我将以下代码运行到 phpMyAdmin SQL 部分，并且能够提取一个值。所以 Select 语句有效。

"SELECT `USERID` FROM `users` WHERE "John Doe" LIKE (CONCAT(`users`.`FIRSTNAME`, ' ' , `users`.`LASTNAME`))"

结果为USERID提供了值

我不知道从这里该去哪里。当我获得用户的全名时，如何从 users.USERID 插入用户 ID？

Answer 1

尝试改用 INSERT SET 语法。

$rest = $db_qms->prepare(
  "INSERT INTO `job_schedule` " .
  "SET " .
    "`JID` = :jid, " .
    "`HPL` = :hpl, " .
    "`WO` = :wo, " .
    "`DESCRIP` = :descrip, " .
    "`MFG_LINE` = :mfg, "
    "`CUSTOMER` = :customer, " .
    "`AE` = (SELECT `USERID` FROM `users` WHERE (:ae LIKE CONCAT(`users`.`FIRSTNAME`, ' ', `users`.`LASTNAME`))), " .
    "`PE` = (SELECT `USERID` FROM `users` WHERE (:pe LIKE CONCAT(`users`.`FIRSTNAME`, ' ', `users`.`LASTNAME`))), " .
    "`DE` = (SELECT `USERID` FROM `users` WHERE (:de LIKE CONCAT(`users`.`FIRSTNAME`, ' ', `users`.`LASTNAME`)));"
);
$res->execute(array(
  ":jid" => $id,
  ":hpl" => $hpl,
  ":wo" => $wo,
  ":descrip" => $descrip,
  ":mfg" => $mfg,
  ":customer" => $customer,
  ":ae" => $ae,
  ":pe" => $pe,
  ":de" => $de
));