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 the SQL syntax; check the manual that matches the version of your MySQL server for the correct syntax to use next to '; INSERT INTO um_group_rights ( um_group_id , cms_usecase_id , um_right_id ) V 'on line 1

it obviously has a semicolon problem, but I don’t understand why. It works without problems in phpmyadmin. Php version - 5.2.6

Answer 1

To be sure: when you try to execute these 4 queries with PHP, do you call 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)"); 

I mean: you cannot send several different queries at once, only one mysql_query call (quoting, my emphasis):

mysql_query() sends a unique query (multiple queries are not supported) for the current active database on the server associated with the specified link_identifier .

You should "split up" your requests - perhaps something like phpMyAdmin without telling you.

And as @Alexandre pointed out in the comments:

The query string should not end with a semicolon.

If you use mysqli_* functions (and not mysql_* ) to access your database, you can try using mysqli_multi_query .

Unfortunately, there is such a function for mysql_* .

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

---

Edit after comment:

As far as performance is concerned, yes, making one call to the database instead of four consecutive calls to PHP <-> MySQL might be better.

In this case, you can try using the insert syntax, which allows you to insert multiple lines at once; see 12.2.5. MySQL INSERT syntax (quoting):

INSERT that use the VALUES syntax can insert multiple lines.
To do this, include several lists of column values, each of which is enclosed in parentheses and separated by commas.
Example:

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

The list of values ​​for each row must be enclosed in parentheses.

Answer 2

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

You would need to break the string or switch to mysqli and mysqli_multi_query () .