I have a MySQL view created using INNER JOIN of two tables. It is matching an ID number from the first table to the same ID in the second table in order to access more data.
CREATE OR REPLACE VIEW vy_enheterILager
AS
(
SELECT enheter.enhID, enheter.produktID, batcher.inDatum, enheter.utDatum
FROM enheter
INNER JOIN batcher ON enheter.batchID = batcher.batchID
WHERE enheter.utDatum IS NULL
)
I would like to update the utDatum column with a specific date on the first two rows of the view, ordered by the inDatum column.
This works in phpMyAdmin:
UPDATE vy_enheterILager
SET utDatum = '2018-05-06'
WHERE produktID = 3
ORDER BY inDatum
LIMIT 2
However, when trying to do it from PHP using a prepared statement, it does not work.
$sql = "UPDATE vy_enheterILager SET utDatum = ? WHERE produktID = ? ORDER BY inDatum LIMIT ?";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("sii", $param_utLevDatum, $param_utLevProdukt, $param_utLevAntal);
// Set parameters
$param_utLevDatum = $utLevDatum;
$param_utLevProdukt = $utLevProdukt;
$param_utLevAntal = $utLevAntal;
// Attempt to execute the prepared statement
if($stmt->execute()){
// Records created successfully
header("location: lager.php");
exit();
} else{
printf("Error: %s.\n", $stmt->error);
}
}
I get the following error:
Incorrect usage of UPDATE and ORDER BY.
Why does it work in phpMyAdmin and not from my PHP script? Thank you in advance.
EDIT: I believe this is a new question. I have seen the documentation of UPDATE mentioning this:
"For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. In this case, ORDER BY and LIMIT cannot be used."
However, if what I am trying to do is not supported, how come it works from phpMyAdmin and not from PHP? It shouldn't be working at all, then? It's the same server!
