Cannot send multiple mysql queries to my database via php

Question

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

[This answer](http://stackoverflow.com/a/10664265/4154967) is what you probably need (I'm assuming you will be switching to mysqli over mysql_* functions, as these are deprecated) — iLot, Nov 07 '14 at 22:24
The query in the error message and the two queries that you say are working are not the same. Can you paste the query that fails? — Félix Adriyel Gagnon-Grenier, Nov 07 '14 at 22:25
Please, [don't use `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and use [PDO](http://us1.php.net/pdo) or [MySQLi](http://us1.php.net/mysqli). You will also want to [Prevent SQL Injection!](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Jay Blanchard, Nov 07 '14 at 22:26
And you should really not be using deprecated mysql_query anymore. Google mysqli or PDO — Félix Adriyel Gagnon-Grenier, Nov 07 '14 at 22:27
possible duplicate of [php/mysql with multiple queries](http://stackoverflow.com/questions/10610675/php-mysql-with-multiple-queries) — iLot, Nov 07 '14 at 22:29
Sorry, just edited the post. I have a few users' in my table so 2 or 3 works when pasted in the myPhpAdmin table. Just looking at MySQLi as we speak. So there is obviously a problem when trying to execute two sql queries in one go with these old functions then. — user1574598, Nov 07 '14 at 22:38

score 2 · Answer 1 · edited Nov 08 '14 at 23:29

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

Thank you - thats very informative indeed. I do need to work more on learning mysql statementas I didn't realise that you could do multiple inserts like you demonstrated above. A good note to end the day on too, as I can sleep easy knowing that it wasn't a weird bug in my code :-) — user1574598, Nov 07 '14 at 23:10

Nisse Engström · Accepted Answer · 2014-11-07T22:55:33.560

1

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

edited Nov 07 '14 at 22:55

answered Nov 07 '14 at 22:43

Nisse Engström

4,738
23
27
42

Thanks for that - I've just tried eliminating the `.=` and it works, but I'm only getting 1 entry (the last entry) instead of 2 entries. Again, will start reading the docs on either MySQLi or PDO. – user1574598 Nov 07 '14 at 22:50
@user1574598: Just updated the answer. Move `queryTable($sql)` into the loop. – Nisse Engström Nov 07 '14 at 22:56
1

Ah I see - it works now so I'll mark your question correct :-) However, Im glad I posted this problem as it's signified that the functions I was going to implement for my questionnaire are practically depreciated! First thing tomorrow I'll be reading the documentation for MySQLi or PDO. – user1574598 Nov 07 '14 at 23:00

Cannot send multiple mysql queries to my database via php

2 Answers2