PHP: Update multiple MySQL fields in single query

Question

I am basically just trying to update multiple values in my table. What would be the best way to go about this? Here is the current code:

$postsPerPage = $_POST['postsPerPage'];
$style = $_POST['style'];

mysql_connect ("localhost", "user", "pass") or die ('Error: ' . mysql_error());
mysql_select_db ("db");

mysql_query("UPDATE settings SET postsPerPage = $postsPerPage WHERE id = '1'") or die(mysql_error());

The other update I want to include is:

mysql_query("UPDATE settings SET style = $style WHERE id = '1'") or die(mysql_error());

Thanks!

Community · Accepted Answer · 2017-05-23 12:32:02Z

51

Add your multiple columns with comma separations:

UPDATE settings SET postsPerPage = $postsPerPage, style= $style WHERE id = '1'

However, you're not sanitizing your inputs?? This would mean any random hacker could destroy your database. See this question: What's the best method for sanitizing user input with PHP?

Also, is style a number or a string? I'm assuming a string, so it would need to be quoted.

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Mar 10, 2011 at 0:42

Fosco

38.6k7 gold badges90 silver badges101 bronze badges

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

Comments

Christian · Accepted Answer · 2011-03-10 00:43:36Z

6

Comma separate the values:

UPDATE settings SET postsPerPage = $postsPerPage, style = $style WHERE id = '1'"

answered Mar 10, 2011 at 0:43

Christian

964 bronze badges

1 Comment

Lightness Races in Orbit Over a year ago

OK, but no upvote because you forgot to point out the glaring flaws in this approach. :)

Linh · Accepted Answer · 2016-10-09 05:04:33Z

1

If you are using pdo, it will look like

$sql = "UPDATE users SET firstname = :firstname, lastname = :lastname WHERE id= :id";
$query = $this->pdo->prepare($sql);
$result = $query->execute(array(':firstname' => $firstname, ':lastname' => $lastname, ':id' => $id));

answered Oct 9, 2016 at 5:04

Linh

61.3k25 gold badges274 silver badges298 bronze badges

Comments

Pedro Lobito · Accepted Answer · 2017-12-04 19:00:08Z

0

I guess you can use:

$con = new mysqli("localhost", "my_user", "my_password", "world");
$sql = "UPDATE `some_table` SET `txid`= '$txid', `data` = '$data' WHERE `wallet` = '$wallet'";
if ($mysqli->query($sql, $con)) {
    print "wallet $wallet updated";
}else{
    printf("Errormessage: %s\n", $con->error);
}
$con->close();

answered Dec 4, 2017 at 19:00

Pedro Lobito

99.8k36 gold badges274 silver badges278 bronze badges

Collectives™ on Stack Overflow

PHP: Update multiple MySQL fields in single query

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related