mysql muliple queries in one statement

I've looked around on stackoverflow for a similar question, but haven't found exactly what I was looking for, so here goes. In phpMyAdmin you can have multiple queries in one statement and it executes it for you, eg:'

UPDATE `test` WHERE `test2` = 4;
UPDATE `test` WHERE `test4` = 8;
UPDATE `test` WHERE `test8` = 1;

Now if I try t开发者_Go百科o do something like that in PHP, it doesn't work at all. eg:

 $test = 'UPDATE `test` SET `value` = "123" WHERE `test2` = 4;
             UPDATE `test` SET `value` = "321" WHERE `test4` = 8;
             UPDATE `test` SET `value` = "533" WHERE `test8` = 1;';
    mysql_query($test);

Gives and error:

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 test SET value = "123" WHERE test2 = 4; UPDATE test SE' at line 1

Is it even possible to combine, say, multiple queries like above, in one statement? I want to do this in the following situation: (The logic behind this is probably very bad, but I don't have much MySQL experience, so please let me know if there's a better way to do it)

The following loops over a couple of times:

function SaveConfig($name, $value)
{
        global $sql_save_query;
    $sql = 'SELECT * FROM `config` WHERE `name` = "'.$name.'"';
    $res = mysql_query($sql);

    if($res)
    {
        $sql_save_query .= 'UPDATE `config` SET value = "'.$value.'" WHERE `name` = "' .$name. '"; '."\n";
    }
    else
    {
            $sql_save_query .= 'INSERT INTO `config`(`id`,`name`,`value`) VALUES("","' .$name. '","' .$value. '"); '."\n";
    }
}

Then after the loop finishes it runs:

mysql_query($sql_save_query);

Which gives an error:

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 config SET value = "" WHERE name = "fcolour2"; UPDATE config SE' at line 1

Now my other option (in my mind) is to just execute an SQL query after each loop, one query at a time. But wouldn't that be bad/slow/bad practice?

the php API forbids you to issue multiple queries in a single call to reduce the chance of an SQL injection attack to your code (think of what would happen if I passed '; UPDATE users SET admin=1 WHERE username='hacker' to your login script as username). You need to either execute multiple statements, or wrap the logic of your statements into a single statement (which is not possible in your case).

It's not possible to execute multiple queries using mysql_query.

You can perform multiple inserts at once using this syntax:

INSERT INTO table (col1, col2) VALUES (0, 1), (2, 3), (4, 5); -- Insert 3 rows

In general less queries = better but for updates you just have to do them.

The loop you have in your example is indicative of an architectural problem.

If you are dealing with an existing record, pass the primary key - then you don't need the select at all - you can just run an update statement.

If you are dealing with a new record, pass no key - then you know to run an insert statement.

probably you can use INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

Some other useful links

http://dev.mysql.com/doc/refman/5.0/en/replace.html

http://www.mysqlperformanceblog.com/2007/01/18/insert-on-duplicate-key-update-and-replace-into/

$sqls = explode(";",$test);
foreach ($sqls as $key=>$sql) {
  if (strlen(trim($sql))>0) {
       mysql_query(trim($sql));
   }
}