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

Question

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

ALTER TABLE tableName AUTO_INCREMENT = 1;

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

//resort database
if(isset($_POST['resort'])){

// 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;
}

$mysqli->close();

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.

Just leave the ids alone that is not a good practice at all. You will end up having to update large swatches if say you delete 3 out of 5000. Not to mention what that would do to your FK. — nerdlyist, Sep 14 '16 at 15:11
I understood from the previous thread that resetting IDs is not a good practice, but this is more of personal lesson to understand PHP and MySQL for me and I have problems figuring out what I don't understand — user2703642, Sep 14 '16 at 15:13
you can't issue multiple statements in a single query() call. it's a limitation in the underlying mysql driver as a basic defense against one form of sql injection attack. You have to do each statement as its OWN `query()` call. — Marc B, Sep 14 '16 at 15:15
Okay, I thought it was possible from http://php.net/manual/en/mysqli.quickstart.multiple-statement.php but this is in other words different? How do I split up the query so that it remembers, for example @num? I tested running one line at a time in phpmyadmin but that didn't work — user2703642, Sep 14 '16 at 15:17
No but `$mysqli->query` would need to be `$mysqli->multi_query($sql)` like the manual shows. — nerdlyist, Sep 14 '16 at 15:18
Alright, will try, I'll be back in a while :) Edit: Alright: Worked, tyvm! — user2703642, Sep 14 '16 at 15:19
Since this is about MySQL instead of Microsoft SQL Server, I have edited your tags to remove the tag for MS SQL Server. — Isaac Bennetch, Sep 26 '16 at 18:20

score 1 · Accepted Answer · answered Sep 14 '16 at 15:23

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) {}

to

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

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

1 Answers1