Cannot send multiple mysql queries to my database via php


I've no idea what is going on here, but I cannot get two mysql statements to add content to my db via themysql_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 intomysql_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 theforeach loop produces that works in phpMyAdmin, but not themysql_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_query() sends a unique query (multiple queries are NOT supported)

Docu-cite-service (tm)

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

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

   (user_id, song, genre, emotion, time_date) 
   (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);



$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 movequeryTable($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.

People are also looking for solutions to the problem: php - Maddhatter Calendar view not being displayed


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.