Update using PDO statement

Question

I am still getting my head around a PDO statement but the code below does not do what I assumed it would

  $temp = "6c ";    
  $weather_report = "Its currently $temp " ; 

  $qry = $pdo->exec("UPDATE data_weather SET text= '$weather_report' WHERE period='report' ");

This does update my database but only with 'Its currently' and the temp value is missing ,

After reading some articles I believe I need to use quote but I am not sure how to implement it ?

any help please ?

You need to read up on SQL escaping and make a point to never, ever insert arbitrary user data into your query strings. Seeing $weather_report inside a query should set off alarm bells. — tadman
– tadman, Commented Apr 16, 2013 at 16:46
you should use prepared statements php.net/manual/en/pdo.prepared-statements.php — IROEGBU
– IROEGBU, Commented Apr 16, 2013 at 16:47

Bill Karwin · Accepted Answer · 2013-04-16 16:44:39Z

5

Please use query parameters instead of interpolating variables into SQL strings.
It's safer, faster, and easier.

$temp = "6c ";    
$weather_report = "It's currently $temp " ; 

$sql = "UPDATE data_weather SET text= ? WHERE period='report'";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($weather_report));

Note that you don't need to quote the string. In fact, you must not put quotes around the ? placeholder. You can use apostrophes inside your weather report string safely.

You can use a parameter placeholder any place you would normally put a single scalar value in an SQL expression. E.g. in place of a quoted string, quoted date, or numeric literal. But not for table names or column names, or for lists of values, or SQL keywords.

answered Apr 16, 2013 at 16:44

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

3 Comments

Mick Over a year ago

Thanks for that Bill, I was unsure of declaring $weather_report as an array ?

Bill Karwin Over a year ago

The PDOStatement::execute() function requires its argument to be an array, so you can pass multiple parameters to the SQL query.

Bill Karwin Over a year ago

Anonymous downvoter, please let me know what you feel is not right in this answer, so I can try to improve it.

user35443 · Accepted Answer · 2016-07-17 18:21:47Z

-1

Although Bill has already answered the question, I'd like to add:

Do not use named parameters with TEXT columns, at least not with MySQL. It won't work. Use question marks instead.

answered Jul 17, 2016 at 18:21

user35443

6,43313 gold badges57 silver badges78 bronze badges

4 Comments

Your Common Sense Over a year ago

Would you please kindly delete your misleading comment? Thanks in advance.

user35443 Over a year ago

@YourCommonSense Check it on PHP forums. It is a true bug many have experienced, but it may have already been solved.

Your Common Sense Over a year ago

Many have experienced an aliens' visits as well.

user35443 Over a year ago

@YourCommonSense I do understand that it may sound like an aliens' visit, but I have truly experienced it. I admit that PHP is not my cup of coffee and the problem might have been elsewhere, but using '?' instead of named parameters solved my problem at the time. I am willing to delete my answer the very moment you provide an evidence that it isn't so. I would have done it myself already if I had had an appropriate workspace.

Collectives™ on Stack Overflow

Update using PDO statement

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related