Simple Insert works with phpmyadmin but not with php
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
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 specifiedlink_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 useVALUES
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.
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().
精彩评论