PHP- Error updating table

Question

I am trying to update a row in a MySQL table row like this:

$conn = new mysqli('localhost','root','','db');
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

//if input == members
$sql = "UPDATE `members` SET id = ".$_POST['id']." fname = ".$_POST['fname'].", lname = ".
$_POST['lname'].", gender = ".$_POST['gender'].", age_group = ".$_POST['age_group'].", status = ".
$_POST['status'].", dob_day = ".$_POST['dob_day'].", dob_month = ".$_POST['dob_month'].", wed_anni_day = ".
$_POST['wed_anni_day'].", wed_anni_month = ".$_POST['wed_anni_month'].", type = ".
$_POST['type'].", email = ".$_POST['email'].", address  = ".$_POST['address'].", city = ".$_POST['city'].", zipco = ".
$_POST['zipco'].", contact1 = ".$_POST['contact1'].", contact2 = ".$_POST['contact2'];

if ($conn->query($sql) === TRUE) {
  echo "Record updated successfully";
} else {
  echo "Error updating record: " . $conn->error;
}

I am getting this error:

Error updating record: 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 'fname = s_name, lname = l_name, gender = Female, age_group = Adult, status = ' at line 1

What am I doing wrong? Thanks.

Never put directly into database query any data which you get from POST/GET. You should filter/escape before you use it. — Tomasz, Aug 27 '15 at 20:51

Shehary · Accepted Answer · 2015-08-27T20:59:17.567

There is a missing , after id = ".$_POST['id']." and here contact2 = ".$_POST['contact2']; can't break the line like this

$sql = "UPDATE `members` SET id = ".$_POST['id']." 
fname = ".$_POST['fname'].", 
lname = ".$_POST['lname'].",
gender = ".$_POST['gender'].",
age_group = ".$_POST['age_group'].",
status = ".$_POST['status'].",
dob_day = ".$_POST['dob_day'].",
dob_month = ".$_POST['dob_month'].",
wed_anni_day = ".$_POST['wed_anni_day'].",
wed_anni_month = ".$_POST['wed_anni_month'].",
type = ".$_POST['type'].",
email = ".$_POST['email'].",
address  = ".$_POST['address'].",
city = ".$_POST['city'].",
zipco = ".$_POST['zipco'].",
contact1 = ".$_POST['contact1'].",
contact2 = ".$_POST['contact2'];

should be

$sql = "UPDATE `members` SET id = ".$_POST['id'].", 
fname = ".$_POST['fname'].", 
lname = ".$_POST['lname'].",
gender = ".$_POST['gender'].",
age_group = ".$_POST['age_group'].",
status = ".$_POST['status'].",
dob_day = ".$_POST['dob_day'].",
dob_month = ".$_POST['dob_month'].",
wed_anni_day = ".$_POST['wed_anni_day'].",
wed_anni_month = ".$_POST['wed_anni_month'].",
type = ".$_POST['type'].",
email = ".$_POST['email'].",
address  = ".$_POST['address'].",
city = ".$_POST['city'].",
zipco = ".$_POST['zipco'].",
contact1 = ".$_POST['contact1'].",
contact2 = ".$_POST['contact2']." ";

And also WHERE clause is missing.

Or may be the query should be like

$sql = "UPDATE `members` SET fname = ".$_POST['fname'].", 
lname = ".$_POST['lname'].",
gender = ".$_POST['gender'].",
age_group = ".$_POST['age_group'].",
status = ".$_POST['status'].",
dob_day = ".$_POST['dob_day'].",
dob_month = ".$_POST['dob_month'].",
wed_anni_day = ".$_POST['wed_anni_day'].",
wed_anni_month = ".$_POST['wed_anni_month'].",
type = ".$_POST['type'].",
email = ".$_POST['email'].",
address  = ".$_POST['address'].",
city = ".$_POST['city'].",
zipco = ".$_POST['zipco'].",
contact1 = ".$_POST['contact1'].",
contact2 = ".$_POST['contact2']."
WHERE
id = ".$_POST['id']." ";

Best way to do;

$id = mysql_real_escape_string($_POST['id']);
$fname = mysql_real_escape_string($_POST['fname']);
$lname = mysql_real_escape_string($_POST['lname']);
$gender = mysql_real_escape_string($_POST['gender']);
$age_group = mysql_real_escape_string($_POST['age_group']);
$status = mysql_real_escape_string($_POST['status']);
$dob_day = mysql_real_escape_string($_POST['dob_day']);
$dob_month = mysql_real_escape_string($_POST['dob_month']);
$wed_anni_day = mysql_real_escape_string($_POST['wed_anni_day']);
$wed_anni_month= mysql_real_escape_string($_POST['wed_anni_month']);
$type = mysql_real_escape_string($_POST['type']);
$email = mysql_real_escape_string($_POST['email']);
$address = mysql_real_escape_string($_POST['address']);
$city = mysql_real_escape_string($_POST['city']);
$zipco = mysql_real_escape_string($_POST['zipco']);
$contact1 = mysql_real_escape_string($_POST['contact1']);
$contact2 = mysql_real_escape_string($_POST['contact2']);

$sql = "UPDATE `members` SET fname = '$fname', 
lname = '$lname',
gender = '$gender',
age_group = '$age_group',
status = '$status',
dob_day = '$dob_day',
dob_month = '$dob_month',
wed_anni_day = '$wed_anni_day',
wed_anni_month = '$wed_anni_month ',
type = '$type',
email = '$email',
address  = '$address',
city = '$city ',
zipco = '$zipco',
contact1 = '$contact1',
contact2 = '$contact2'
WHERE
id = '$id' ";

Thanks but Im still getting this error: `Error updating record: 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 '@tcd.ie, address = 40 C G, city = city, zipco = zip, contact1 = 187' at line 11` — MosesA, Aug 27 '15 at 20:53

score 1 · Answer 2 · answered Aug 27 '15 at 20:45

1

You missed a , and that's why the error

UPDATE `members` SET id = ".$_POST['id']." fname = ".$_POST['fname']
                                          ^..... Here

answered Aug 27 '15 at 20:45

Rahul

76,197
13
71
125

score 1 · Answer 3 · answered Aug 27 '15 at 20:47

1

You have missed a comma after $_POST['id'] and strings need to be inside quotes:

fname = '".$_POST['fname']."'
        ^ >> here           ^ and here...

You'll need to change this for all fields where a string is inserted/updated.

answered Aug 27 '15 at 20:47

baao

71,625
17
143
203

PHP- Error updating table

3 Answers3