Using PDO Prepared Statements With MySQL Query Variable

Question

I am trying to increment rows in a table using PHP PDO and i have come up with this query

UPDATE users SET log = ?

I am trying to make an update based on the previous value on each column of the log

So, if my table was like this earlier

+----+------+------+-----------+--------+ | id | name | age | eye_color | log | +----+------+------+-----------+--------+ | 21 | OLa | 19 | black | 1 | | 22 | OLa | 19 | Green | 2 | | 23 | OLa | 19 | Grey | 3 | +----+------+------+-----------+--------+

Am expecting to get this result

+----+------+------+-----------+--------+ | id | name | age | eye_color | log | +----+------+------+-----------+--------+ | 21 | OLa | 19 | black | 2 | | 22 | OLa | 19 | Green | 3 | | 23 | OLa | 19 | Grey | 4 | +----+------+------+-----------+--------+

I found out i could make the value of my placeholder to be

log + 1

There by making the full query

UPDATE users SET log = log + 1

This works well when i use the PDO's query method and also from my terminal, the problem comes when I try updating this using prepared statements, If i did

$stmt = $this->pdo->prepare("UPDATE users SET log = ?");
$stml->execute(['height + 1']);

Then all the log columns becomes 0.

Is there anything am doing wrong? I also know i can just make the query plain

$stmt = $this->pdo->prepare("UPDATE users SET log = log + 1");
$stmt->execute(['log + 1']);

But i would prefer my initial approach, i am working with some constraints.

The query execute where you used height + 1 what is height supposed to be? With the single quotes around it you are passing a literal string to the update which is not what you intended. — Dave
– Dave, Commented Feb 21, 2019 at 18:56
You need UPDATE users SET log = height + ? and $stml->execute(['1']); — MonkeyZeus
– MonkeyZeus, Commented Feb 21, 2019 at 18:57
the height is suppose to be the previous value of the height from the db table, sql works that way — olayemii
– olayemii, Commented Feb 21, 2019 at 18:58
This post make absolutely no sense. If you need height + 1 then why does your code show log + 1 — MonkeyZeus
– MonkeyZeus, Commented Feb 21, 2019 at 18:59

Bill Karwin · Accepted Answer · 2019-02-21 21:37:23Z

You can't use query parameters to insert expressions to your syntax. Parameters are not just string-interpolation. If they were, there would be no benefit to using them, because you can do string-interpolation easily in PHP already.

The whole point of query parameters is that the value is combined with the query on the server, after the SQL syntax has been parsed, so it's too late for you to insert any new syntax, like an expression.

Query parameters are always treated as a single scalar value. You can't use a parameter for:

Table identifiers
Column identifiers
SQL keywords
Expressions
Lists of values

As others have explained, in this case, you have no need to use a query parameter anyway. Using the literal expression log + 1 directly in your query is safe. There's no untrusted content (from users or other sources) being inserted into the query, so there's no risk of SQL injection.

Vidal · Accepted Answer · 2019-02-21 19:01:04Z

2

Since you are not getting information from the user, and is to sum 1 to a column you are safe executing a regular query.

UPDATE users SET log = log + 1

If you use a prepare statement you will have to query the column get the current value and then on the second query do the update and add 1 to it.

answered Feb 21, 2019 at 19:01

Vidal

2,6062 gold badges20 silver badges35 bronze badges

Collectives™ on Stack Overflow

Using PDO Prepared Statements With MySQL Query Variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related