I am attempting to prepare a statement with mysqli
$stmt = $mysqli->prepare("INSERT HIGH_PRIORITY INTO `user` (`FirstName`, `LastName`, `Department`, `Email`) SELECT * FROM (SELECT ?,?,?,?) AS tmp WHERE NOT EXISTS ( SELECT `Email` FROM `user` WHERE `Email` = ? ) LIMIT 1;");
if (!$stmt) {
printf('errno: %d, error: %s', $mysqli->errno, $mysqli->error);
die;
}
$statementReturnCode = $stmt->bind_param("sssss", $ssoFirstName, $ssoLastName, $ssoDepartment, $ssoEmail, $ssoEmail);
if (!$statementReturnCode) {
printf('errno: %d, error: %s', $stmt->errno, $stmt->error);
}
$stmt->execute();
$stmt->close();
When this is run I receive the following error:
errno: 1060, error: Duplicate column name '?'
I've been able to bind in this fashion in the past, but I've never tried to bind the same column twice in a different location in the query (Email).
How can I use the same value for Email in two different locations, or is this a different issue?
To clarify what is being done with this query:
This query will be run frequently. If the user exists already in the user table, no insert should be attempted. If the user does not exist, the user should be added to the user table.
The user table has a UserID field that auto-increments. If an insert is attempted the user will not be added due to a unique constraint, but the AUTO-INCREMENT will add 1 even though the insert did not occur. This WHERE NOT EXISTS query is an attempt to mitigate this issue.
Example use:
INSERT INTO `user` (
`user`.`FirstName`,
`user`.`LastName`,
`user`.`Department`,
`user`.`Email`)
SELECT * FROM (SELECT 'John', 'Doe', 'Marketing', 'John.Doe@mycorp.com') AS tmp
WHERE NOT EXISTS (
SELECT `user`.`Email`
FROM `user`
WHERE `user`.`Email` = 'John.Doe@mycorp.com'
) LIMIT 1;
I have tested this query and it works as I had expected. The issue I'm having is with properly changing this query into a prepared statement with php.
