简单插入适用于phpmyadmin，但不适用于php [英] Simple Insert works with phpmyadmin but not with php

问题描述

im试图使用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) ;

，它返回: 1064-您的SQL语法有误；检查与您的MySQL服务器版本相对应的手册，以在'附近使用正确的语法；在行1上插入um_group_rights(um_group_id，cms_usecase_id，um_right_id)V'

显然分号有问题，但我不明白为什么.它的工作原理在phpmyadmin中没有问题. php版本是5.2.6

解决方案

请确保:当您尝试从PHP执行这4个查询时，您要调用mysql_query四次?

例如:

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)");

我的意思是:您不能一次仅发送一个调用 mysql_query (引用，强调我的意思):

mysql_query()发送唯一查询 (不支持多个查询) 服务器上的数据库 与指定的关联 link_identifier.

您必须分离"查询-可能是phpMyAdmin所做的而没有告诉您.

而且，正如 @Alexandre指出的那样:

查询字符串不应以a结尾 分号.

如果您使用 mysqli_* 函数(而不是mysql_*)访问数据库，则可以尝试使用 mysqli_multi_query .

不幸的是，mysql_*有这样的功能.

(顺便说一句:mysql_* API是旧的-使用mysqli_*会更好，特别是对于新项目而言)

---

在评论后进行

如果是关于性能的，是的，对数据库进行一次调用而不是四个连续的PHP <-> MySQL调用可能会更好.

在这种情况下，您可以尝试使用允许一次插入多行的插入语法；参见 12.2.5. MySQL手册(引用)中的INSERT语法:

使用VALUES的

INSERT语句 语法可以插入多行.
为此，请包括多个列表 列值，每个都包含在其中 括号并用逗号分隔.
示例:

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

每行的值列表必须为 括在括号内.

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 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.

这篇关于简单插入适用于phpmyadmin，但不适用于php的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！