bonaca bonaca - 3 months ago 11
MySQL Question

cannot execute multiple queries

I'm trying to execute multiple queries, but something is wrong.

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

$sql = "
UPDATE tmatria SET par = " . $newpar . ", inde = " . $newinde . " WHERE id =" . $cutid . ";
SELECT * FROM tmatria ORDER BY inde ASC;
SET @i := 0;
UPDATE tmatria SET inde = @i := @i + 1;
";

try {
$db->exec($sql);
}
catch (PDOException $e) {
echo $e->getMessage();
die();
}


I want update some columns, then sort table by
inde
column and finally set
inde
values to
1 2 3...


I think lines
UPDATE tmatria SET par...
and
SELECT * FROM tmatria ORDER BY inde ASC;
are critical, but cannot see what's wrong.

Any help?

Answer

You have some fundamental misunderstanding about how to use SQL. You do a SELECT ... ORDER BY, and then you expect the following UPDATE to obey the same ordering. The UPDATE is ordering the table in its natural order, it doesn't pay attention to the SELECT query at all.

And as a matter of coding, there's no need or benefit to executing multiple SQL statements in one call. PDO permits it, but it's not a good habit. You should execute one SQL statement per call. As long as you use the same db connection, the session variable @i will retain its value.

Also use prepared queries when you want to combine PHP variables with a SQL statement; don't concatenate PHP variables into your SQL string.

try {
    $sql = "UPDATE tmatria SET par = ?, inde = ? WHERE id = ?";
    $stmt = $db->prepare($sql);
    $stmt->execute([$newpar, $newinde, $cutid]); 
    $sql = "SET @i := 0";
    $db->exec($sql);
    $sql = "UPDATE tmatria SET inde = @i := @i + 1 ORDER BY inde ASC";
    $db->exec($sql);
}
catch (PDOException $e) {
    echo $e->getMessage();
    die();
}

It also looks like you're trying to renumber inde after every update, to force that column to have consecutive values. This will get slower and slower the more rows you have in the table, right?

You should reconsider why you need that column to have consecutive values.

Comments