Problem with INSERT else UPDATE

[facarroll]

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

[gristoi]

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

[facarroll]

I tried removing brackets, but no difference.

[gristoi]

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

[facarroll]

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";
}
?> 

[facarroll]

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.

[gristoi]

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)

[facarroll]

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.

[gristoi]

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

 

[facarroll]

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.

[facarroll]

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 "

[gristoi]

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

[facarroll]

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 

[gristoi]

Great quiz !!. Especially like the way u used clicking on certain parts of the image for the answer.

 

Good luck with the rest