I'm trying to run consecutive MYSQL statements using PHP as shown in the code snippet below (it just copies one row to another and renames the ids via the tmp table).
I receive duplicate syntax error messages. I've tried countless iterations. The code looks like the code I've studied in the PHP manual and other mysql questions on SO (excluding the php dimension).
Can anyone explain why my php syntax is incorrect?
include("databaseconnect.php");// This obviously works. Used a zillion time
$sql ="CREATE TEMPORARY TABLE tmp SELECT * FROM event_categoriesBU WHERE id
= 1;";
$sql.="UPDATE tmp SET id=100 WHERE id = 1;";
$sql.="INSERT INTO event_categoriesBU SELECT * FROM tmp WHERE id = 100;";
if ($conn->query($sql) === TRUE)
{
echo "Table row copied successfully. Do something with it";
}
else
{
echo "Error creating table: " . $conn->error;
//close connection etc
}
PHP message return:
Error creating table: There is an error in your SQL syntax; check the manual for your MariaDB server version for "UPDATE tmp SET id=100 WHERE id = 1INSERT INTO event_categoriesBU SELECT * FROM t at line 1 Correct syntax to use near ”
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Don't run a bunch of queries at once. Often the success of one of these operations depends on all the other operations being performed correctly, so when something goes wrong you can't just move on as if nothing went wrong.
You can do this:
$queries = [ "CREATE TEMPORARY TABLE tmp SELECT * FROM event_categoriesBU WHERE id = 1", "UPDATE tmp SET id=100 WHERE id = 1", "INSERT INTO event_categoriesBU SELECT * FROM tmp WHERE id = 100" ]; foreach ($query as $query) { $stmt = $conn->prepare($query); $stmt->execute(); }Don't forget to enable exceptions so that any query failure stops your process rather than letting things get out of control.
The reason you are not using
multi_queryis that this function does not support placeholder values. If you need to bring in some kind of user data in this query, you'll need to usebind_paramto do so safely. Without placeholder values, you are exposed to SQL injection errors, one of which is enough to make your entire application vulnerable.It's worth noting that PDO is more flexible and adaptable than
mysqli, so if you're not heavily invested inmysqliit's worth considering the switch.