CODEWITHSUNDEEP
phpmysql
Tom
Tom

Reputation: 13

How can I combine two mysql queries into one?

I want to combine these two queries into one:

mysql_query("INSERT INTO categories (name, parent) VALUES ('$name', '$parent')");

mysql_query("UPDATE categories SET child='$child' WHERE ID = $id");

Can it be done?

Upvotes: 1

Views: 565

Answers (2)

XpiritO
XpiritO

Reputation: 2827

Although I don't know if this fits your problem, in fact it is possible to combine multiple queries into one, by using the MySQLi extension (it will not work with mysql extension)

mysqli_multi_query($connection, "INSERT INTO a (attrib1, attrib2) VALUES ('$a', '$b');UPDATE b SET attrib3='$c' WHERE attrib4 = $d");

Upvotes: 0

thetaiko
thetaiko

Reputation: 7834

Create a trigger in MySQL.

Without doing any checking on if a parent category exists, or catching exceptions the following should do the trick. Replace 'dbname' with whatever your db is.

DROP TRIGGER IF EXISTS `dbname`.`update_parent`//
CREATE TRIGGER `dbname`.`categories` AFTER INSERT ON `dbname`.`categories`
FOR EACH ROW BEGIN
UPDATE categories SET child = NEW.ID WHERE ID = NEW.parent;
END
//

When the trigger is functioning correctly, the update will get called automatically after an INSERT so all you have to do is run:

mysql_query("INSERT INTO categories (name, parent) VALUES ('$name', '$parent')");

Upvotes: 1

Related Questions