无法通过php向我的数据库发送多个mysql查询

问题描述：

I've no idea what is going on here, but I cannot get two mysql statements to add content to my db via the mysql_query. Here is my code:

function sendDataToDB() {

    // first send the user to the users table
    $audio_survey = new dbclass();
    $audio_survey -> connectToDB();

    $sql = $_SESSION['user']['sql'];

    $audio_survey -> queryTable($sql);

    // get the current users' ID number from the table
    $sql = "SELECT user_id FROM users WHERE name=\"" . $_SESSION['user']['name'] . "\"";

    $result = $audio_survey -> queryTable($sql);
    $output = $audio_survey -> getDataFromDB($result);

    $user_id = $output['user_id'];

    $songs = $_SESSION['songs'];

    foreach ($songs as $song) {

            $sql .= "INSERT INTO survey (user_id, song, genre, emotion, time_date) VALUES ($user_id, \"" . $song['song'] . "\", \"" . $song['genre'] . "\", \"" . $song['emotion'] . "\", \"" . $song['time_date'] . "\");<br />";
    }
    $audio_survey -> queryTable($sql);
    $audio_survey -> closeDBconnection();
}

Everything works, as in a user gets added to my "users" table, but when the variable $sql is passed into mysql_query then I get this error:

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 survey (user_id, song, genre, emotion, time_date) VALUES (3, "one mo' at line 1

I've tried pasting in the statement I have concatenated using the $sqlvariable straight into the sql query box in phpMyAdmin and it works! This is what the foreach loop produces that works in phpMyAdmin, but not the mysql_query function! I've made sure I have allocated enough space for characters, etc.

INSERT INTO survey (user_id, song, genre, emotion, time_date) VALUES (3, "one more time", "dance", "happy", "15:32:21 07-11-14");
INSERT INTO survey (user_id, song, genre, emotion, time_date) VALUES (3, "dance dance dance", "disco", "relaxed", "15:32:28 07-11-14");

我不知道这里发生了什么，但是我无法获得两个mysql语句来向我的数据库添加内容通过mysql_query code>。这是我的代码： p>

  function sendDataToDB（）{
 
 //首先将用户发送到users表
 $ audio_survey = new dbclass（）; 
  $ audio_survey  - ＆gt;  connectToDB（）; 
 
 $ sql = $ _SESSION ['user'] ['sql']; 
 
 $ audio_survey  - ＆gt;  queryTable（$ sql）; 
 
 //从表中获取当前用户的ID号
 $ sql =“SELECT user_id FROM users WHERE name = \”“。$ _SESSION ['user'] ['name'  ]。“\”“; 
 
 $ result = $ audio_survey  - ＆gt;  queryTable（$ sql）; 
 $ output = $ audio_survey  - ＆gt;  getDataFromDB（$ result）; 
 
 $ user_id = $ output ['user_id']; 
 
 $ songs = $ _SESSION ['songs']; 
 
 foreach（$ songs as $ song）{\  n 
 $ sql。=“INSERT INTO survey（user_id，song，genre，emotion，time_date）VALUES（$ user_id，\”“。$ song ['song']。”\“，\”“。$ song [  'genre']。“\”，\“”。$ song ['emotion']。“\”，\“”。$ song ['time_date']。“\”）;＆lt; br /＆gt;“;  
} 
 $ audio_survey  - ＆gt;  queryTable（$ sql）; 
 $ audio_survey  - ＆gt;  closeDBconnection（）; 
} 
  code>  pre> 
 
 一切正常，就像用户被添加到我的“users”表中一样，但是当变量 $ sql 传递到 mysql_query  code>然后我收到此错误： p> 
 
 
 错误：您的SQL语法有错误; 查看与您的MySQL服务器版本相对应的手册，以便在'INSERT INTO调查（user_id，song，genre，emotion，time_date）值附近使用正确的语法（3，“1 mo”在第1行 em>  p  > 
 
 
我已经尝试将我已经使用 $ sql  code>变量连接的语句直接粘贴到phpMyAdmin的sql查询框中并且它正常工作！这就是  foreach  code>循环生成在phpMyAdmin中，但不是 mysql_query  code>函数！我已经确保为角色等分配了足够的空间。 p> 
 
 
  INSERT INTO调查（user_id，song，genre，emotion，time_date）VALUES（3，“再一次”，“跳舞”，“快乐”，“15:32:21 07-11-14”）;  
INSERT INTO调查（user_id，song，genre，emotion，time_date）VALUES（3，“舞蹈舞蹈”，“迪斯科”，“放松”，“15:32:28 07-11-14”）; 
   pre> 
  div>

答

$sql = "SELECT user_id FROM users ...";

...

foreach ($songs as $song) {
    $sql .= "INSERT INTO survey ...";
}

You are adding all the queries together using the (.=) string concatenation operator. You need to use normal assignment, and move queryTable($sql) into the loop.

foreach ($songs as $song) {
    $sql = "INSERT INTO survey ...";
    $audio_survey -> queryTable($sql);
}

Note also that the MySQL extension is deprecated and will be removed in the future. You should use MySQLi or PDO instead.

答

http://php.net/manual/en/function.mysql-query.php

mysql_query() sends a unique query (multiple queries are NOT supported)

Docu-cite-service (tm)

You need to either use Nisse's approach, while calling mysql_query() inside the loop - or use something else, that allows to execute multiple queries within one statement rather than the deprecated mysql_query method.

Another option would be to rewrite your concatenation logic, so it generates one query for multiple inserts:

INSERT INTO survey 
   (user_id, song, genre, emotion, time_date) 
VALUES 
   (3, "one more time", "dance", "happy", "15:32:21 07-11-14"),
   (3, "dance dance dance", "disco", "relaxed", "15:32:28 07-11-14"),
   ...

something like

$sql = "INSERT INTO survey (user_id, song, genre, emotion, time_date) VALUES ";
$atLeastoneInsert = false;
foreach ($songs as $song) {
    $atLeastoneInsert = true;
    $sql .= "($user_id, \"" . $song['song'] . "\", \"" . $song['genre'] . "\", \"" . $song['emotion'] . "\", \"" . $song['time_date'] . "\"),";
}
$sql = trim($sql,",");

if ($atLeastoneInsert){
    $audio_survey -> queryTable($sql);
}

无法通过php向我的数据库发送多个mysql查询

相关推荐