facarroll Posted June 16, 2011 Share Posted June 16, 2011 Can someone tell me what is wrong with the following code. It successfully inserts the first set of data, but does not update successfully. $sql_check = mysql_query("SELECT * FROM quiz WHERE managerId = '$managerId' AND userIdRec = '$userIdRec' AND quizId = '$quizId' AND quizTitle = '$quizTitle' "); $exist_check = mysql_num_rows($sql_check); if($exist_check == 0) { //Adding data to the database... $sql = "INSERT INTO `quiz` (`id`, `quizId`, `quizTitle`, `managerId`, `userId`, `userIdRec`, `userGroup`, `egroup`, `userScore`, `totalScore`, `passScore`, `passState`, `Result`, `userDate`, `addDate`) "; $sql = $sql." VALUES (null, '".$quizId."', '".$quizTitle."', '".$managerId."', '".$userId."', '".$userIdRec."', '".$userGroup."', '".$egroup."', '".$userScore."', '".$totalScore."', '".$passScore."', '".$passState."', '".$strResults."', '".$postDate."', NOW())"; }else{ //Updating data in the database... $sql = "UPDATE `quiz` SET (`userScore` = $userScore, `passState` = $passState, `Result` = $strResults, `userDate` = $postDate, `addDate` = NOW())"; $sql = $sql." WHERE (`managerId` = $managerId AND `userIdRec` = $userIdRec AND `quizId` = $quizId AND `quizTitle` = $quizTitle) "; } $rs = mysql_query($sql); Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/ Share on other sites More sharing options...
gristoi Posted June 16, 2011 Share Posted June 16, 2011 remove the brackets: $sql = "UPDATE `quiz` SET `userScore` = $userScore, `passState` = $passState, `Result` = $strResults, `userDate` = $postDate, `addDate` = NOW()"; $sql = $sql." WHERE `managerId` = $managerId AND `userIdRec` = $userIdRec AND `quizId` = $quizId AND `quizTitle` = $quizTitle "; Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230414 Share on other sites More sharing options...
facarroll Posted June 16, 2011 Author Share Posted June 16, 2011 I tried removing brackets, but no difference. Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230415 Share on other sites More sharing options...
gristoi Posted June 16, 2011 Share Posted June 16, 2011 Ok, ensure that your error checking is turned on and add a die command after your query: $rs = mysql_query($sql) or die ("Error in query: $sql. ".mysql_error()); Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230416 Share on other sites More sharing options...
facarroll Posted June 16, 2011 Author Share Posted June 16, 2011 I decided to include the whole script as it is quite short. Several of the variables come from a login page and the rest are results from a student test which is delivered as a Flash file with responses entered by checkboxes and radio buttons, as well as some image mapping. All of this is sorted by the flash file, and the output is fine. The data is then sent to this file for processing into the database. The database will accept a new entry from a first attempt by a student, but is meant to update when a second or further attempt is made, but the update is not working. The suggested changes included here are not making any difference. Thanks for any help. <?php session_start(); //This page is used to receive the data sent by Flash player and the data is then saved to the database. error_reporting(E_ALL & ~E_NOTICE & ~E_DEPRECATED); // Put stored session variables into local php variable $id = $_SESSION['id']; $userId = $_SESSION['userId']; $userGroup = $_SESSION['userGroup']; $userIdRec = $_SESSION['userIdRec']; $egroup = $_SESSION['egroup']; $managerId = $_SESSION['managerId']; include_once("filepath.php"); //The following lines are used to receive the data from quiz player... $quizId = $_POST["quizId"]; $quizTitle = $_POST["quizTitle"]; $userName = $_POST["userName"]; $userMail = $_POST["userMail"]; $userScore = $_POST["userScore"]; $totalScore = $_POST["totalScore"]; $passScore = $_POST["passScore"]; $passState = ($_POST["passState"] == "True") ? 1 : 0; $strResults = $_POST["quesInfo"]; $strResults = addslashes($strResults); $postDate = $_POST["postDate"]; $sql_check = mysql_query("SELECT * FROM quiz WHERE managerId = '$managerId' AND userIdRec = '$userIdRec' AND quizId = '$quizId' AND quizTitle = '$quizTitle' "); $exist_check = mysql_num_rows($sql_check); if($exist_check == 0) { //Adding data to the database... $sql = "INSERT INTO `quiz` (`id`, `quizId`, `quizTitle`, `managerId`, `userId`, `userIdRec`, `userGroup`, `egroup`, `userScore`, `totalScore`, `passScore`, `passState`, `Result`, `userDate`, `addDate`) "; $sql = $sql." VALUES (null, '".$quizId."', '".$quizTitle."', '".$managerId."', '".$userId."', '".$userIdRec."', '".$userGroup."', '".$egroup."', '".$userScore."', '".$totalScore."', '".$passScore."', '".$passState."', '".$strResults."', '".$postDate."', NOW())"; }else{ //Adding data to the database... $sql = "UPDATE `quiz` SET `userScore` = $userScore, `passState` = $passState, `Result` = $strResults, `userDate` = $postDate, `addDate` = NOW()"; $sql = $sql." WHERE `managerId` = $managerId AND `userIdRec` = $userIdRec AND `quizId` = $quizId AND `quizTitle` = $quizTitle "; } $rs = mysql_query($sql) or die ("Error in query: $sql. ".mysql_error()); //Feedback for the flash player... if ($rs) { echo "feedMsg=Data has been posted successfully"; } else { echo "feedMsg=Failed to post data to database"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230420 Share on other sites More sharing options...
facarroll Posted June 16, 2011 Author Share Posted June 16, 2011 One more thing... I do get the success message, but the data from the Flash file is not being posted, just the data from the login variables. Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230423 Share on other sites More sharing options...
gristoi Posted June 16, 2011 Share Posted June 16, 2011 Ok, even if your update is failing it should return something so $rs will always be true. Try this: if (last_insert_id()!= 0) { echo "feedMsg=Data has been posted successfully"; } else { echo "feedMsg=Failed to post data to database"; } that will return the id of the last row affected ( as long as the primary key is auto incrimented) Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230428 Share on other sites More sharing options...
facarroll Posted June 16, 2011 Author Share Posted June 16, 2011 OK. I made the change suggested and got this Error in query: UPDATE `quiz` SET `userScore` = , `passState` = 0, `Result` = , `userDate` = , `addDate` = NOW() WHERE `managerId` = 20 AND `userIdRec` = 71 AND `quizId` = AND `quizTitle` = . 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 ' `passState` = 0, `Result` = , `userDate` = , `addDate` = NOW() WHERE `managerId' at line 1 The change that I made was this; $rs = mysql_query($sql) or die ("Error in query: $sql. ".mysql_error()); //Feedback for the flash player... if (last_insert_id()!= 0) { echo "feedMsg=Data has been posted successfully"; } else { echo "feedMsg=Failed to post data to database"; } I figure that the description of the error is such because there is no data coming from Flash, but that is only because I'm calling the file under discussion directly through my browser. I cannot call it any other way because the Flash file includes a directive to send data to the database and then directly go to another page. So you see, I cannot run the file directly from a browser except as the output without the Flash data. Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230436 Share on other sites More sharing options...
gristoi Posted June 16, 2011 Share Posted June 16, 2011 ok, I see what your saying now. Just to confirm what is getting passed in try and kill the script and echo out the sql to see what is being passed intto the query. that should give u a heads up: if($exist_check == 0) { //Adding data to the database... $sql = "INSERT INTO `quiz` (`id`, `quizId`, `quizTitle`, `managerId`, `userId`, `userIdRec`, `userGroup`, `egroup`, `userScore`, `totalScore`, `passScore`, `passState`, `Result`, `userDate`, `addDate`) "; $sql = $sql." VALUES (null, '".$quizId."', '".$quizTitle."', '".$managerId."', '".$userId."', '".$userIdRec."', '".$userGroup."', '".$egroup."', '".$userScore."', '".$totalScore."', '".$passScore."', '".$passState."', '".$strResults."', '".$postDate."', NOW())"; }else{ //Adding data to the database... $sql = "UPDATE `quiz` SET `userScore` = $userScore, `passState` = $passState, `Result` = $strResults, `userDate` = $postDate, `addDate` = NOW()"; $sql = $sql." WHERE `managerId` = $managerId AND `userIdRec` = $userIdRec AND `quizId` = $quizId AND `quizTitle` = $quizTitle "; } die($sql); Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230438 Share on other sites More sharing options...
facarroll Posted June 16, 2011 Author Share Posted June 16, 2011 I hard coded some false data instead of some of the variables and this is what I got. "Error in query: UPDATE `quiz` SET `userScore` = 10, `passState` = 0, `Result` = These are the results., `userDate` = 1989, `addDate` = NOW() WHERE `managerId` = 20 AND `userIdRec` = 71 AND `quizId` = 12345 AND `quizTitle` = 12345 . 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 'are the results., `userDate` = 1989, `addDate` = NOW() WHERE `managerId` = 20 AN' at line 1" I'm curious to know why it reports " `managerId` = 20 AN' " at the end instead od "AND", because AND is definitely correctly spelt in the script. Thinking about it, that's probably just a maximum string length in the error report. Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230439 Share on other sites More sharing options...
facarroll Posted June 16, 2011 Author Share Posted June 16, 2011 When I kill the script but using the false data I get "UPDATE `quiz` SET `userScore` = 10, `passState` = 0, `Result` = These are the results., `userDate` = 1989, `addDate` = NOW() WHERE `managerId` = 20 AND `userIdRec` = 71 AND `quizId` = 12345 AND `quizTitle` = 12345 " Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230441 Share on other sites More sharing options...
gristoi Posted June 16, 2011 Share Posted June 16, 2011 stick single quotes around each variable. something is causing the query to hit an escape character: if($exist_check == 0) { //Adding data to the database... $sql = "INSERT INTO `quiz` (`id`, `quizId`, `quizTitle`, `managerId`, `userId`, `userIdRec`, `userGroup`, `egroup`, `userScore`, `totalScore`, `passScore`, `passState`, `Result`, `userDate`, `addDate`) "; $sql = $sql." VALUES (null, '".$quizId."', '".$quizTitle."', '".$managerId."', '".$userId."', '".$userIdRec."', '".$userGroup."', '".$egroup."', '".$userScore."', '".$totalScore."', '".$passScore."', '".$passState."', '".$strResults."', '".$postDate."', NOW())"; }else{ //Adding data to the database... $sql = "UPDATE `quiz` SET `userScore` = '$userScore', `passState` = '$passState', `Result` = '$strResults', `userDate` = '$postDate', `addDate` = NOW()"; $sql = $sql." WHERE `managerId` =' $managerId' AND `userIdRec` = '$userIdRec' AND `quizId` =' $quizId' AND `quizTitle` = $quizTitle "; } Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230442 Share on other sites More sharing options...
facarroll Posted June 16, 2011 Author Share Posted June 16, 2011 Thanks man. You are magic. All good. If you want, give me 5 minutes and you can see for yourself online at http://www.safetytestingonline.com/demonstration/admin_login/login_do_quiz.php Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230444 Share on other sites More sharing options...
gristoi Posted June 16, 2011 Share Posted June 16, 2011 Great quiz !!. Especially like the way u used clicking on certain parts of the image for the answer. Good luck with the rest Quote Link to comment https://forums.phpfreaks.com/topic/239531-problem-with-insert-else-update/#findComment-1230446 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.