CODEWITHSUNDEEP
phpmysqlselectinsert
Sanya
Sanya

Reputation: 1280

php MySQL INSERT value referencing another table

In the code below, I'm most concerned about the last three variables. The php variables ($ae, $pe, and $de) represent a user's full name as a string.

I'm trying to insert into job_schedule the user's ID that is from the table users.

Currently my result inserts all of the data into the table but places "0" into each record of the last three columns (AE, PE, DE) as if it was not able to find the USERID.

using 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`))"
  ));

I also tried to see if my SELECT statement was correct. So I ran the following code into phpMyAdmin SQL section and I was able to pull a value. So the Select statement works.

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

The result provided a value for USERID

I have no idea where to go from here. How do I insert the user's ID from users.USERID when I am given the user's full name?

Upvotes: 0

Views: 123

Answers (2)

Paul Rowe
Paul Rowe

Reputation: 788

Try using INSERT SET syntax instead.

$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
));

Upvotes: 1

Reenactor Rob
Reenactor Rob

Reputation: 1526

I have never seen an sql statement use a string in the where clause as you are doing.

I would write the sql differently:

":ae" => "SELECT USERID FROM users WHERE CONCAT(users.FIRSTNAME, ' ', users.LASTNAME) LIKE \"" . $ae . "\"";

Upvotes: 0

Related Questions