Updating SQL Column

Question

So I have a table with 6 columns, each column corresponds to a certain product type. Each column holds a number that corresponds to the number of times people have chosen that product type.

A | B | C | D | E | F
---------------------
0 | 0 | 0 | 0 | 0 | 0

So if the user picks type A, then I want to update column A's number from 0 to 1. So here's the SQL code I wrote:

$link = new PDO('***;dbname=***;charset=UTF-8','***','***');
$stmt = $link->prepare("UPDATE table SET :column=:num");
$stmt->bindParam(':column', $column);
$stmt->bindParam(':num', $num);
$stmt->execute();

But it's not updating anything at all. So i'm guessing there is something wrong with the SQL code, most likely having to do with the column placeholder :column. Can anyone tell me the right SQL code?

wesside · Accepted Answer · 2012-11-16 23:06:14Z

4

First make sure, $column is in an accepted list of values. Next, you can't bind :column you will have assign it like so:

$stmt = $link->prepare('UPDATE table SET ' . $column .' = :num'); 
$stmt->bindParam(':num', $num);
$stmt->execute();

If you were going to check for a valid $column I would use

$valid_column = preg_match('/[a-z0-9_]/i, $column);

or a sufficient replace (preg_replace). Though you would likely wrap it in a try/catch and set exceptions to be thrown in your PDO instance to make sure it's even legit.

edited Nov 16, 2012 at 23:06

answered Nov 16, 2012 at 20:35

wesside

5,7605 gold badges32 silver badges35 bronze badges

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

3 Comments

Levente Pánczél Over a year ago

To teach good practices, the injection of $column into the SQL would deserve some escaping.

wesside Over a year ago

I would never suggest a type of assignment or mass assignment in this fashion. So yes, +1. I'm bender from the future ;)

Clockwork-Muse Over a year ago

Um, no, don't escape it. I'd do a check of 'is the string given exactly one in this set'. Anything else raises the possibility of problems. Your given check won't restrict the column selected to the 'proper' list, and is only valid for the english/latin alphabet; if you check some of the other questions on this site, you'll see database table/columns with names with non-latin characters. It also won't allow 'escaped' names (which may have been reserved words, not usually valid column names).

Collectives™ on Stack Overflow

Updating SQL Column

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related