cannot execute multiple queries

Question

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?

That is absolutely not how you include variables in a query. — Ben
– Ben, Commented Aug 17, 2016 at 16:18
What is happening when you try to run this? Are you getting a specific error message? — Robert Columbia
– Robert Columbia, Commented Aug 17, 2016 at 16:18
@RobertColumbia inde column is set to 1 2 3... before table is sorted by inde column. In fact it is not sorted at all. — qadenza
– qadenza, Commented Aug 17, 2016 at 16:23

Bill Karwin · Accepted Answer · 2016-08-17 16:55:55Z

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.

O. Jones · Accepted Answer · 2016-08-17 16:44:20Z

1

You need to pass your queries to PDO one at a time. You can still use variables like @i, because PDO will run your queries in order. But it won't run a mess of queries in one call.

answered Aug 17, 2016 at 16:44

O. Jones

110k17 gold badges134 silver badges187 bronze badges

1 Comment

qadenza Over a year ago

I'm afraid I don't understand. Could you give me an example ?

Collectives™ on Stack Overflow

cannot execute multiple queries

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related