MySQL Update Syntax Error

Question

I made a simple query to update my table using php, but could not figure out the error.

My Function :

function editData($id, $data) {
    $message ="";
    $query= "UPDATE blood_data SET name='{$data['name']}', group='{$data['group']}',
             address1='{$data['address']}', district='{$data['district']}',
             age='{$data['age']}', sex='{$data['sex']}', phone='{$data['phone']}', 
             mobile='{$data['mobile']}',  email='{$data['email']}' 
             WHERE id='{$id}' ";

    if(mysql_query($query)) {
        $message ="Data Successfully Updated ";
    }else {
        die("failed: " . mysql_error());    
    }

    return $message;

}

This is what passed in function :

$dataArray = [
    'name'=>$_POST['full_name'],
    'group'=>$_POST['group'],
    'sex'=>$_POST['sex'],
    'age'=>$_POST['age'],
    'address'=>$_POST['address'],
    'district'=>$_POST['district'],
    'email'=>$_POST['email'],
    'phone'=>$_POST['phone'],
    'mobile'=>$_POST['mobile']
  ];

  $msg = editData($idUploader, $dataArray);

And the error message thrown was :

failed: 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 'group='O +', address1='dsd', district='sdfs', age='22', sex='male', phone='23423' at line 1

Matt Humphrey · Accepted Answer · 2012-09-05 08:24:21Z

7

You need to quote your field names, as group is a reserved MySQL keyword. For example:

UPDATE blood_data SET `name` = '{$data['name']}', `group` = '{$data['group']}' ...

Look at using PDO as it will do it for you and help prvent SQL injection, amongst many other benefits. Not to mention the mysql_* functions are deprecated.

edited Sep 5, 2012 at 8:24

answered Sep 5, 2012 at 8:13

Matt Humphrey

1,5541 gold badge12 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rkosegi · Accepted Answer · 2012-09-05 08:18:27Z

0

You should avoid such code, your program is vulnerable to SQL injection attacks. Consider using mysqli with prepared statements instead.

As Matt Humphrey already mentioned in his answer, group is reserved mysql keyword, but you can use it when you disclose it in backticks like this:

`group`

answered Sep 5, 2012 at 8:18

rkosegi

14.8k5 gold badges57 silver badges90 bronze badges

Collectives™ on Stack Overflow

MySQL Update Syntax Error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related