在单个函数中使用Insert和Update

Question

I was wondering if someone would be able to shed some light on how I may overcome this problem.

I'm trying to add and update information on a database, so when a user first enters completes the questionnaire its fine and it works, However when they go back to update the questionnaire it throws an error, "Please go back and try again".

I have updated the PHP code with the recommendations given to me so far.

Thank You.

PHP code:

function updatePartCTQ_part1($questionAns, $memberid) {

//First Insert MemberID
$ctqmemberinsert = "INSERT INTO ctq_questionnaire (user_id) VALUES ('$memberid')";
$addresult = mysqli_query($ctqmemberinsert);

if ($addresult) {

    $update = "UPDATE ctq_questionnaire SET Item1= '{$questionAns[0]}', Item2 = '{$questionAns[1]}' WHERE user_id = '$memberid'";

    mysqli_query($conn, $update);

} else {
    echo 'Please go back and try again';
}
}

Any help will be greatly appreciated.

Finished Code

Thanks to Michael and the rest of the guys I was able to get the code working, so I thought I'd post an update, if anyone else gets stuck they'd be able to have a glance at the working version of the code:

function updatePartCTQ_part1($questionAns, $memberid) {

//Check whether user exists
$exists = mysql_query("SELECT * FROM ct1_questionnaire WHERE user_id = '$memberid'");

if (mysql_num_rows($exists) === 0) {
    // Doesn't exist. INSERT User into Table

    $ctqmemberinsert = "INSERT INTO ctq_questionnaire (user_id) VALUES ('$memberid')";
    mysqli_query($ctqmemberinsert);
} 
    // UDPATE after INSERT

    $update = "UPDATE ctq_questionnaire SET Item1= '{$questionAns[0]}', Item2 = '{$questionAns[1]}, Item3 = '{$questionAns[2]}',  
    Item4 = '{$questionAns[3]}',Item5 = '{$questionAns[4]}', Item6 = '{$questionAns[5]}', Item7 = '{$questionAns[6]}', 
    Item8 = '{$questionAns[7]}', Item9 = '{$questionAns[8]}', Item10 = '{$questionAns[9]}', Item11 = '{$questionAns[10]}', 
    Item12 = '{$questionAns[11]}', Item13 = '{$questionAns[12]}', Item14 = '{$questionAns[13]}', Item15 = '{$questionAns[14]}'
    WHERE user_id = '$memberid'";

    mysql_query($update);
}

Answer 1

Your UPDATE syntax is incorrect. You must not repeat the SET keyword:

$update = "UPDATE ctq_questionnaire SET Item1= '{$questionAns[0]}', Item2 = '{$questionAns[1]}' WHERE user_id = '$memberid'";
//-------------------------------------------------------------^^^^^^^ no SET here        

For readability it is recommended to enclose the array values in {}, although your way should work.

Note that your try/catch isn't going to be of much use since mysql_query() does not throw an exception. Instead it will just return FALSE on error. Instead, store it in a variable and test for TRUE/FALSE as you did with the INSERT.

// We assume these values have already been validated and escaped with mysql_real_escape_string()...
$update = "UPDATE ctq_questionnaire SET Item1= '{$questionAns[0]}', Item2 = '{$questionAns[1]}' WHERE user_id = '$memberid'";
$upd_result = mysql_query($update);
if ($upd_result) {
  // ok
}
else {
  // error.
}

Finally, and I suspect you've heard this before, the old mysql_*() functions are scheduled for deprecation. Consider moving to an API which supports prepared statements, like MySQLi or PDO.

Update

Assuming you have a unique index or PK on ctq_questionnaire.user_id on subsequent calls, the first query will error and your second won't be run. The simplest fix is to use INSERT IGNORE, which will treat key violations as successful.

$ctqmemberinsert = "INSERT IGNORE INTO ctq_questionnaire (user_id) VALUES ('$memberid')";

A more complicated solution is to first test if the username exists in the table with a SELECT, and if not, do the INSERT.

$exists_q = mysql_query("SELECT 1 FROM ct1_questionnaire WHERE user_id = '$memberid'");
if (mysql_num_rows($exists_q) === 0) {
  // Doesn't exist. Do the INSERT query
}
// proceed to the UDPATE after INSERTing if necessary