Multiple INSERT / UPDATE without a php loop

[mortadelle]

I'd like to make multiple updates in one mysql command like " UPDATE table SET value=1 WHERE id=1; UPDATE table SET value=2 WHERE id=50; without using a php loop.

It works fine on mysql ( i've made a test on phpmyadmin .. ) but it fails with php !

I've tested something like this:

// connection to DBB
[...]

$sql="UPDATE table SET value=1 WHERE id=1; UPDATE table SET value=2 WHERE id=50;";

// same thing without the ";" and the end $sql="UPDATE table SET value=1 WHERE id=1; UPDATE table SET value=2 WHERE id=50";

mysql_query($sql);

in every case it fails : i got an error 1064 .... it seems not to like the ";" and the end of the first update ....

The question is :
- what i am trying to do is possible or not ?
- Is it totaly useless or not ? because I try to avoid php working all the time and i try to use mysql ressource when its possible ...

thks for your advice !

[jeremuck]

I'm not sure if it's possible or not; try inserting a newline (\n) after the semi-colon or in place of the semi-colons.
I once read something somewhere about not putting semi-colons in.

Anyway, it's funny you metioned wanting to take the load of PHP and using MYSQL resources.

I don't know, but I strongly suspect that mySQL resources are much more expensive than PHP resources.

In fact, I've heard cases of people specifically avoiding using mySQL resources, but it was usually in places where dealing with the results took place on the client's machine. Which means it was actually taking the load off the server.
But with PHP, whether it's mySQL resources or PHP resources, it's the same machine (obviously depending if it's installed on the same machine).

Anyway, I don't have any qualms about using PHP's resources. I suspect that mySQL is more expensive.

[mortadelle]

It doesnt work with newlines (\n) or/and semi-colons 🙁

It really puzzles me seeing this kind of request failing with php and working directly with mysql 😕

But it's not a problem, Php will do this work then... 😃

thks jeremuck