Sunday, July 12, 2009

Multiple MySQL statements with a single query

To run multiple statements within a single query you simply need to separate the statements with a semicolon to signify the end of the statement.

UPDATE `inventory` SET `value` = 2 WHERE `name` = "Strawberries";
UPDATE `inventory` SET `value` = 4 WHERE `name` = "Blueberries"

This is the best route to take with any ALTER TABLE or UPDATE queries.

For inserting multiple rows you should do the following.

INSERT INTO `inventory` (`id`, `name`, `value`) VALUES
(1, 'Strawberries', 4),
(2, 'Blueberries', 4);

Unfortunately, while PHP's mysqli_multi_query does support multiple queries, mysql_query does not.  A workaround for this is to explode the sql string by a semicolon and execute each query manually.

$sql = "UPDATE `inventory` SET `value` = 2 WHERE `name` = \"Strawberries\";
UPDATE `inventory` SET `value` = 4 WHERE `name` = \"Blueberries\"";

$pieces = explode(";", $sql);
foreach ($pieces as $query) {
     mysql_query($query) or die(mysql_error());
}

This can easily be put into a function to keep your code clean while still getting the usability you're looking for.

No comments:

Post a Comment