mysql muliple queries in one statement

Question

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 to 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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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/

Answer 5

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