0

I'm attempting to make a form for a charity that allows staff members to view and update victim records that are stored in the SQL Database via the website. I have managed to display the records and create a form that allows the alteration of the records, however when I enter variables I receive a SYNTAX error. 

    $updateSQL="UPDATE Victims SET victimFName=".$victimFN." WHERE victimId=".$id."";
    $exeupdateSQL= mysql_query($updateSQL) or die (mysql_error());
    echo "The Record has been updated";

I receive the 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 'WHERE victimId=' at line 1'

I then researched SQL Injections and changed the code:

    $updateSQL="UPDATE Victims SET victimFName=".mysql_real_escape_string($_POST['victimFName']).";
    WHERE victimId=".mysql_real_escape_string($_POST['victimId'])."";
    exeupdateSQL= mysql_query($updateSQL) or die (mysql_error());
    echo "The Record has been updated";

This still didnt work.

I have attempted to replace my variables $VictimFN and $id by entering data into the SQL query and the code works, updating the record. Such as:

      $updateSQL="UPDATE Victims SET victimFName='Mary Smith' WHERE victimId='1'";
      $exeupdateSQL= mysql_query($updateSQL) or die (mysql_error());
      echo "The Record has been updated";

I am fairly new to programming and was wondering how I could fix this as the issue is to do with my variables.

Thanks to your help I have established the problem with the code and have now fixed it to:

    $updateSQL="UPDATE Victims SET victimFName='".mysql_real_escape_string($_POST['victimFName'])."' WHERE victimId='".mysql_real_escape_string($_POST['victimId'])."'";
     $exeupdateSQL=mysql_query($updateSQL) or die (mysql_error());
     echo "The Record has been updated";
     echo "<br><a href=ViewVictimRequest.php>View Updated Record</a>";

There are now no errors, however the records state they are updated when they are not. Is there anyway to fix this?

Thank you in advance for your response and sorry for the inconvenience!

user3527721
  • 3
  • 4

2 Answers2

0

The correct way is:

$updateSQL="UPDATE Victims SET victimFName='".mysql_real_escape_string($_POST['victimFName'])."' WHERE victimId='".mysql_real_escape_string($_POST['victimId'])."'";

Your $_POST values are string, thus you need to enclose them in single quotes '. Also remove the semi-colon ; before WHERE, it's breaking the string.

Note:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Community
  • 1
  • 1
AyB
  • 11,609
  • 4
  • 32
  • 47
  • Thank you very much for your response! I tried this code and received the error: Parse error: syntax error, unexpected '=' in /home/unix/student10/w1284519/public_html/STF/geteditform.php on line 26 – user3527721 Apr 13 '14 at 13:20
  • $exeupdateSQL=mysql_query($updateSQL) or die (mysql_error()); – user3527721 Apr 13 '14 at 13:24
  • @user3527721 Try to print the query before executing and see if the values are right. – AyB Apr 13 '14 at 13:24
  • I receive the response 'The record has been updated' which is what I wished it to say, however the record in the database doesn't update? – user3527721 Apr 13 '14 at 13:32
  • @user3527721 Could you echo the values of $_POST['victimFName'] and $_POST['victimId'] and paste them here? – AyB Apr 13 '14 at 13:37
  • as in: echo"$_POST['victimFName']"; ? When doing so I receive an error: Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING – user3527721 Apr 13 '14 at 13:43
  • @user3527721 It should be `echo $_POST['victimFName']; `. Do not add extra quotes where not necessary. Are you trying the above code exactly the way it is? Or else you could save them into variables and use them in the query like the way John has done. – AyB Apr 13 '14 at 13:51
  • Sorry about that! I tried that and nothing is shown only the original message "Edit The Record has been updated View Updated Record" I tried the code exactly how you showed it – user3527721 Apr 13 '14 at 13:57
  • would you know how I could fix the issue of the variable? – user3527721 Apr 13 '14 at 16:32
  • @user3527721 Do you mind pasting the full code? Maybe we can figure something out. – AyB Apr 13 '14 at 17:30
  • I'm not sure how to post it in the comment, should I create a new question? – user3527721 Apr 13 '14 at 17:32
  • @user3527721 You can edit your this question itself. – AyB Apr 13 '14 at 17:33
  • Thank you for your help! I managed to solve the issue, the name of the fields I used on the previous page in my form, were not matching the ($_POST[victimFName]) a stupid mistake on my part, thank you so much for your help solving my error may you forever be blessed! – user3527721 Apr 13 '14 at 17:42
  • @user3527721 You're most welcome! :D Glad you solved it. Feel free to come back here anytime you need help with! – AyB Apr 13 '14 at 17:43
0

You have an errant semi-colon and missing quotes. Here's a cleaner version of your query:

$updateSQL="UPDATE Victims SET victimFName='".mysql_real_escape_string($_POST['victimFName'])."'
WHERE victimId=".mysql_real_escape_string($_POST['victimId']);

Or

$fname = mysql_real_escape_string($_POST['victimFName']);
$id    = mysql_real_escape_string($_POST['victimId']);
$updateSQL="UPDATE Victims SET victimFName='{$fname}' WHERE victimId={$id}";
John Conde
  • 217,595
  • 99
  • 455
  • 496
  • Thank you very much for your response! The second code gives me the error: Parse error: syntax error, unexpected '=', expecting T_VARIABLE or '$' in /home/unix/student10/w1284519/public_html/STF/geteditform.php on line 25 – user3527721 Apr 13 '14 at 13:24
  • line 24: $fname= mysql_real_escape_string($_POST['victimFName']); line 25: $=mysql_real_escape_string($_POST['victimId']); – user3527721 Apr 13 '14 at 13:27