Updating multiple columns on same row MySQL

Question

I couldn't find a definite answer to this specific issue, basically what I have created is a HTML table which displays the contents of a database into columns and rows. Currently it is very simple as I am understanding how it works for now.

ID | First_Name | Last_Name | Bio | Created

ID = Primary Key for each row of the database.

I wish to be able to update an entire row of the database with one update command, if this is possible. Currently I am using:

$sql = "UPDATE databasetest.people SET First_Name = '".htmlspecialchars($_POST['FirstName'])."' WHERE people.ID = ".$_POST['edit'];

...where $_POST['FirstName'] is the first name, as defined by a form and $_POST['edit'] is the ID as defined by an initial form where the user selects "Edit User".

Is it possible to update multiple columns (first name, last name, bio and created at once) in this way?

EDIT: Very quick answers, thank you very much! I am not sure why I was downvoted, possibly because it was dumb? Sorry, still learning :)

Yes, use a , after each value UPDATE databasetest.people SET First_Name = '".htmlspecialchars($_POST['FirstName'])."', lastname = 'whatever' WHERE people.ID = ".$_POST['edit']; dev.mysql.com/doc/refman/5.0/en/update.html — user3783243
– user3783243, Commented May 12, 2015 at 17:26
@user3783243 it's best to answer the question in an answer rather than a comment. It's an SEO thing. — O. Jones
– O. Jones, Commented May 12, 2015 at 17:27
@OllieJones wasn't certain this was the issue. Isrwluke you should separate out user input from your queries. This is how SQL injections occur. You were probably downvoted because this is one of the first things on the manuals page. — user3783243
– user3783243, Commented May 12, 2015 at 17:32
Incorporating potentially "unsafe" values in SQL statements creates SQL Injection vulnerability. Consider what happens in your code (what SQL statement your code issues to the database) when a nefariously clever user sends a request with the value of edit set to a string like "1 OR 1=1". — spencer7593
– spencer7593, Commented May 12, 2015 at 17:44
Unless the page is secure anyone can execute it that finds it. htmlspecialchars only encodes special HTML characters <, >, &. php.net/htmlspecialchars stackoverflow.com/questions/60174/… — user3783243
– user3783243, Commented May 12, 2015 at 17:47

O. Jones · Accepted Answer · 2015-05-12 17:25:53Z

6

You want

  UPDATE table
     SET col='val', col2='val2', col3=123
   WHERE ID = 345

See how you can use commas to separate multiple columns and the values they should get?

answered May 12, 2015 at 17:25

community wiki

O. Jones

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

1 Comment

lukesrw Over a year ago

Thank you very much! I wasn't sure how it would work in terms of formatting to simply add a comma... that clears that up.

Collectives™ on Stack Overflow

Updating multiple columns on same row MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related