php - 将自定义 MySQL 查询移至 PHP 中

Question

我编写了一个查询，它直接通过服务器上的 MySQL 运行，如下所示:

CREATE TABLE tmp_1 (user1 varchar(255), pid int(11));INSERT INTO famgallery.tmp_1 SET user1 = (SELECT title FROM cpg15x_albums WHERE aid = (SELECT aid FROM cpg15x_pictures WHERE pid=(select max(pid) from cpg15x_pictures))); UPDATE famgallery.tmp_1 SET pid = (SELECT MAX(pid) FROM cpg15x_pictures) ; UPDATE cpg15x_pictures f, tmp_1 t SET f.user1 = t.user1 WHERE f.pid = t.pid; DROP TABLE tmp_1;

问题是由于尝试将其转移到 PHP 中而出现的:

cpg_db_query("CREATE TABLE tmp_1 (user1 varchar(255), pid int(11));INSERT INTO famgallery.tmp_1 SET user1 = (SELECT title FROM cpg15x_albums WHERE aid = (SELECT aid FROM cpg15x_pictures WHERE pid=(select max(pid) from cpg15x_pictures))); UPDATE famgallery.tmp_1 SET pid = (SELECT MAX(pid) FROM cpg15x_pictures) ; UPDATE cpg15x_pictures f, tmp_1 t SET f.user1 = t.user1 WHERE f.pid = t.pid; DROP TABLE tmp_1;")

经过一天的实验后，应用程序不断给我这个严重错误:

mySQL error: 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 cpg15x_tmp_1 SET `user1` = (SELECT `title` FROM cpg15x_albums WHERE ' at line 1.  File: /home/content/47/9243147/html/family/include/functions.inc.php - Line: 270

我开始认为应用程序代码中的某个地方存在冲突，因为我无法理解为什么它直接在服务器上运行良好，但经过如此大量的修改后在 PHP 中却无法运行。任何想法将不胜感激。

cpg_db_query 内置于应用程序中。据我所知，它执行与 mysqli_query 相同的操作。

相关文件:

• http://www.mconchicago.com/upload.txt
• http://www.mconchicago.com/init.inc.txt

Answer 1

cpg_db_query 是 mysql_query 的包装器；而您将只运行一个查询 - 您正在尝试在那里运行多个查询。

如果您想这样做，则必须将其分解为不同的查询，然后分别运行它们:

cpg_db_query("CREATE TABLE cpg15x_tmp_1 (user1 varchar(255), pid int(11))");
cpg_db_query("INSERT INTO cpg15x_tmp_1 SET user1 = (SELECT title FROM cpg15x_albums WHERE aid = (SELECT aid FROM cpg15x_pictures WHERE pid = (select max(pid) from cpg15x_pictures)))");
cpg_db_query("UPDATE famgallery.cpg15x_tmp_1 SET pid = (SELECT MAX(pid) FROM cpg15x_pictures)");
cpg_db_query("UPDATE cpg15x_pictures f, cpg15x_tmp_1 t SET f.user1 = t.user1 WHERE f.pid = t.pid");
cpg_db_query("DROP TABLE cpg15x_tmp_1");

mysqli_* 有 multi_query ，这是我所知道的使用单个调用运行多个 SQL 查询的唯一方法。