Update int column using a variable in php

Question

I am using following query

$abilityPts = xxx;
$statement = $conn->prepare('UPDATE players SET ability = (ability + $abilityPts) WHERE id = :myPlayerId');

However it is giving mer error..

$abilityPts column not found

How can i resolve this issue?

{"error":"SQLSTATE[42S22]: Column not found: 1054 Unknown column '$abilityPts' in 'field list'"}

Everyone's who's suggesting string concatenation should read [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php) — Álvaro González, Mar 07 '13 at 08:43

kapa · Accepted Answer · 2013-03-07T08:47:00.960

You are using prepared statements. So use prepared statements. Use :abilityPts and bind $abilityPts the same way you are binding :myPlayerId.
```
$conn->prepare('UPDATE players SET ability = (ability + :abilityPts) WHERE id = :myPlayerId');
```
Creating SQL queries through concatenation and embedding PHP variables in strings is a very bad practice, it can leave your code open to SQL Injection which is serious business. Prepared statements are much cleaner, more secure and generally are accepted as best practice by the community. If you are already using prepared statements, you are doing well, just keep using them.
You should do what I've described in 1., but also notice the difference between ' and " in PHP.

' (Single quoted):

Unlike the double-quoted and heredoc syntaxes, variables and escape sequences for special characters will not be expanded when they occur in single quoted strings.

This basically means the following:
```
$a = 15;
echo 'My favorite is $a.'; //My favorite is $a.
echo "My favorite is $a."; //My favorite is 5.
```
For further information, please read the PHP manual on Strings.

Christoph Grimmer · Answer 2 · 2014-02-11T13:03:01.940

-2

Putting a variable in single quotes will not have the variable evaluated. Either use double quotes or string concatenation.

To avoid further punishment for my rather generic answer: You should not use variables directly in queries to the database in general and especially when using prepared statements.

edited Feb 11 '14 at 13:03

answered Mar 07 '13 at 08:35

Christoph Grimmer

4,210
4
40
64

score -2 · Answer 3 · answered Mar 07 '13 at 08:36

-2

$statement = $conn->prepare(sprintf('UPDATE players SET ability = (ability + %d) WHERE id = :myPlayerId', $abilityPts));

try this

answered Mar 07 '13 at 08:36

zkanoca

9,664
9
50
94

Kumar Saurabh Sinha · Answer 4 · 2013-03-07T09:39:05.710

-2

try doing the below:

$statement = $conn->prepare("UPDATE players SET ability = (ability + " . $abilityPts . ") WHERE id = :myPlayerId");

edited Mar 07 '13 at 09:39

answered Mar 07 '13 at 08:36

Kumar Saurabh Sinha

880
8
23

Not my downvote, but you should read [How to prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-to-prevent-sql-injection-in-php). – Álvaro González Mar 07 '13 at 08:42
I am so sorry to use that statement! I have realized that. – Kumar Saurabh Sinha Mar 07 '13 at 08:53
do edit your question and remove unsolicited phrases and make an effort to prove the answer against SQL injection. – Roman C Mar 07 '13 at 08:57

score -2 · Answer 5 · answered Mar 07 '13 at 08:36

-2

Please try below code:

$abilityPts = xxx;
$statement = $conn->prepare("UPDATE players SET ability = (ability + ".$abilityPts.") WHERE id = :myPlayerId");

answered Mar 07 '13 at 08:36

Kevin G Flynn

231
4
14

Update int column using a variable in php

5 Answers5