user2703642 user2703642 - 5 months ago 18
MySQL Question

Multi line statement in PHP to MySQL database (resorting auto incremented IDs)

I have a little bit of a problem figuring out where the error comes from.
The background is that I have a table and using a form I update the values into the table and can update them and delete them using different buttons.

This leaves the auto incremented IDs in disorder after deletion and I thought it would be just good practice to see if I could reset the order using a simple button.

Otherwise I've been updating them using

SET @num := 0;

UPDATE tableName SET id = @num := (@num+1);


in phpmyadmin. I got it from this answer :
Auto Increment after delete in MySQL

//resort database

// Database connection opening
$mysqli = NEW MySQLi('localhost','root','','powerstations'); //our server, the username, the password (empty), the database itself
if($mysqli) {
echo "Connected!";
} else {
echo "Problem.";

$sql_resort = "SET @num := 0; ";
$sql_resort .= "UPDATE powerdata SET id = @num := (@num+1); ";
$sql_resort .= "ALTER TABLE powerdata AUTO_INCREMENT = 1; ";

if ($mysqli->query($sql_resort) === TRUE) {
echo "Resorted successfully";
} else {
echo "Error: " . $sql_resort . "<br>" . $mysqli->error;


The error I get is:
SET @num := 0; UPDATE powerdata SET id = @num := (@num+1); ALTER TABLE powerdata AUTO_INCREMENT = 1;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE powerdata SET id = @num := (@num+1); ALTER TABLE powerdata AUTO_INCREMENT' at line 1

I tried to search for typos by putting the multi line statement into phpmyadmin, but found nothing and I don't see where the problem with the "code structure" is if there is one.


When attempting do execute multiple statements in a single query you have to call $mysqli->multi_query($sql) not $mysqli->query($sql).

So you will need to update

if ($mysqli->query($sql_resort) === TRUE) {}


if ($mysqli->multi_query($sql_resort) === TRUE) {