Update SQL query - Set values to PHP string variable

Question

This is probably a very trivial question but I've been struggling with it for a while and also tried finding answers online and still getting errors.

Trying to write a simple UPDATE query for a PHP/MySql form:

  $sql="UPDATE mytable SET numericValue = '".$someid."', description =  '".$sometext."' WHERE id='".$myid."' ";

Whilst all numeric values are being passed and updated fine, I can't get the description right. The description column is a VARCHAR and $sometext is a string and I cant get it escaped / wrapped with quotes correctly.

halfdan · Accepted Answer · 2011-04-07 21:04:26Z

1

You should make use of sprintf, it avoids string confusion by providing placeholders (%d for decimals, %s for strings). See the manual for more.

$sql= sprintf("UPDATE mytable SET numericValue = %d, description = '%s' WHERE id = %d", $someid, $sometext, $myid);

If $someText is coming from GET/POST/.. you should wrap a mysql_real_escape_string() around it to prevent SQL injection (or use PDO prepared statements).

answered Apr 7, 2011 at 21:04

halfdan

34.3k8 gold badges83 silver badges88 bronze badges

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

2 Comments

Marc B Over a year ago

sprintf() is an ugly method if it's a complex query with lots of values to insert. Far too easy to mis-place parameters. better to massage the values before building the string and then build it "traditionally", or better yet, yes, use PDO.

dkatwitt Over a year ago

I did try sprintf method, however still not successful. Record is being updated but only the numeric values change - no sql errors but VARCHAR doesn't get updated

SIFE · Accepted Answer · 2011-04-07 21:09:48Z

0

$sql="UPDATE mytable SET numericValue = '$someid' , description = '$sometext' WHERE id='$myid' ";

I think you have to worry about sql injection.

answered Apr 7, 2011 at 21:09

SIFE

5,7537 gold badges34 silver badges48 bronze badges

Collectives™ on Stack Overflow

Update SQL query - Set values to PHP string variable

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related