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");
Answer
Solution:
http://php.net/manual/en/function.mysql-query.php
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 deprecatedmysql_querymethod.Another option would be to rewrite your concatenation logic, so it generates one query for multiple inserts:
something like
Answer
Solution:
You are adding all the queries together using the (
.=) string concatenation operator. You need to use normal assignment, and movequeryTable($sql)into the loop.Note also that the MySQL extension is deprecated and will be removed in the future. You should use MySQLi or PDO instead.