Updating multiple SQL fields with PHP

Question

Sorry in advance for my amateur knowledge. I'm creating the basics of a user a database, I have registering working fine fine but I cannot get my update function working correctly.

Register function

mysql_query("INSERT INTO `users` ($fields) VALUES ($data)");

where $fields and $data are taken from a post filled array:

$fields =  '`' . implode('`, `', array_keys($register_data)) . '`';
$data   =  '\'' . implode('\', \'', $register_data) . '\'';

Update function

However using this method for updating records does not work. I have tried 2 methods:

mysql_query("UPDATE `users` SET ($fields) VALUES ($data) WHERE `user_id` = $user_id");

and

mysql_query("UPDATE `users` SET $fields = $data WHERE `user_id` = $user_id");

The first SQL function does not work (annoying as it does of INSERT) and the second one only updates one value from my array.

Can someone correct me on how this should be done? For info my array is as follows:

$save_data = array(
    'username'      => $_POST['username'],
    'password'      => $_POST['password'],
    'email'         => $_POST['email'], 
    'first_name'    => $_POST['first_name'],
    'last_name'     => $_POST['last_name'], 
    'bio'           => $_POST['bio']
);

WARNING: If you're just learning PHP, please, do not learn the obsolete mysql_query interface. It's awful and is being removed in future versions of PHP. A modern replacement like PDO is not hard to learn. A guide like PHP The Right Way can help explain best practices. Always be absolutely sure your user parameters are properly escaped or you will have severe SQL injection bugs. — tadman
– tadman, Commented Feb 24, 2015 at 17:27
did you check error message from mysql? try just echo "UPDATE users` SET ($fields) VALUES ($data) WHERE user_id = $user_id";` before mysql_query(... just to check what your trying to query first and debug. I think the problem could be email @ character — Alex
– Alex, Commented Feb 24, 2015 at 17:29
Thanks for the warning and the sources; I will start looking into PDO. And yes I have yet add my escapes. Thank! — Daniel-Barnes
– Daniel-Barnes, Commented Feb 24, 2015 at 17:45

Blah Argh · Accepted Answer · 2015-02-24 17:38:37Z

1

The syntax for UPDATE is to use SET name=value, name=value, ... http://dev.mysql.com/doc/refman/5.0/en/update.html

So try this:

// Prepare the name-value pairs for the SET clause.
$set = array();
foreach ($register_data as $key => $value) {
    $set[] = "`$key` = '$value'";
}
$set = implode(', ', $set);

// Run the query.
mysql_query("UPDATE `users` SET $set WHERE `user_id` = $user_id");

As a side note, as others have pointed out, mysql_query() is not recommended. There is http://php.net/manual/en/book.mysqli.php, and better yet (apparently), http://php.net/manual/en/book.pdo.php. They're a bit more complicated to use, but well worth the small learning curve.

edited Feb 24, 2015 at 17:38

answered Feb 24, 2015 at 17:30

Blah Argh

3021 silver badge4 bronze badges

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

1 Comment

Daniel-Barnes Over a year ago

Thanks for the info; works perfectly like this. I just need to account for blank fields and it's sorted. Thanks!

Collectives™ on Stack Overflow

Updating multiple SQL fields with PHP

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related