更新SQL列 [英] Updating SQL Column

问题描述

所以我有一个包含6列的表格，每列对应一种特定的产品类型.每列都有一个数字，该数字与人们选择该产品类型的次数相对应.

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

因此，如果用户选择类型A，那么我想将列A的编号从0更新为1.因此，这是我编写的SQL代码:

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();

但是它根本不更新任何内容.因此，我猜测SQL代码有问题，最有可能与列占位符:column有关.谁能告诉我正确的 SQL代码?

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?

推荐答案

首先请确保$column在可接受的值列表中.接下来，您将无法绑定:column，您将像这样分配它:

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();

如果您要检查有效的$column我会使用

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

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

或足够的替换(preg_replace).尽管您可能会将其包装在try/catch中，并设置要在PDO实例中引发的异常，以确保其合法性.

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.

这篇关于更新SQL列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！