Simple Insert works with phpmyadmin but not with php

Question

im trying to insert this query with mysql_query

INSERT INTO um_group_rights (`um_group_id`,`cms_usecase_id`,`um_right_id`) VALUES (2,1,1) ; 
INSERT INTO um_group_rights (`um_group_id`,`cms_usecase_id`,`um_right_id`) VALUES (2,2,1) ; 
INSERT INTO um_group_rights (`um_group_id`,`cms_usecase_id`,`um_right_id`) VALUES (2,3,1) ; 
INSERT INTO um_group_rights (`um_group_id`,`cms_usecase_id`,`um_right_id`) VALUES (2,4,1) ;

and it returns: 1064 - 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 '; INSERT INTO um_group_rights (um_group_id,cms_usecase_id,um_right_id) V' at line 1

it obviously has a problem with the semicolon but i dont understand why. it works without a problem in phpmyadmin. php version is 5.2.6

Answer 1

Just to be sure : when you are trying to execute these 4 queries from PHP, you're calling mysql_query four times ?

For instance :

mysql_query("INSERT INTO um_group_rights (`um_group_id`,`cms_usecase_id`,`um_right_id`) VALUES (2,1,1)");
mysql_query("INSERT INTO um_group_rights (`um_group_id`,`cms_usecase_id`,`um_right_id`) VALUES (2,2,1)");
mysql_query("INSERT INTO um_group_rights (`um_group_id`,`cms_usecase_id`,`um_right_id`) VALUES (2,3,1)");
mysql_query("INSERT INTO um_group_rights (`um_group_id`,`cms_usecase_id`,`um_right_id`) VALUES (2,4,1)");

What I mean is : you cannot send several distinct queries at once, with only one call to mysql_query (quoting, emphasis mine) :

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that's associated with the specified link_identifier .

You have to "separate" your queries -- which is probably something phpMyAdmin does without telling you.

And, as @Alexandre pointed out in the comments :

The query string should not end with a semicolon.

If you are using the mysqli_* functions (and not mysql_*) to access your database, you could try using mysqli_multi_query.

Unfortunately, there is such function for mysql_*.

(BTW : the mysql_* API is the old one -- it would be better, especially for a new project, to use mysqli_*)

---

Edit after the comment :

If it's about performances, yes, doing a single call to the database, instead of four successive PHP <-> MySQL calls, could be better.

In this case, you could try using the insert syntax that allows to insert several lines at once ; see 12.2.5. INSERT Syntax in MySQL's manual (quoting) :

INSERT statements that use VALUES syntax can insert multiple rows.
To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.
Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

The values list for each row must be enclosed within parentheses.

Answer 2

Unlike phpMyAdmin, mysql_query() can execute only one query at a time.

You would have to split the string, or switch over to mysqli and mysqli_multi_query().