使用准备好的语句在MySQL/PHP中增加字段的问题

问题描述

我有以下代码，应该在准备好的PHP mysql语句中将字段值增加1:

I have the following code which is supposed to increment a field value by 1 in a prepared PHP mysql statement:

function db_OP_doVote($pdo, $postid, $votetype)
{
    $prepStatement = $pdo->prepare(
        "UPDATE content_posts SET `:votetype` = `:votetype` + 1 WHERE `id` = :id"
    );

    $prepStatement->execute(array(':votetype' => $votetype, ':id' => $postid));

    echo "Success";
}

但是，此操作无济于事.错误的SQL语法不会引发任何错误，脚本可以运行到完成，但是我的字段根本不会更新.

This however, does nothing. No error is thrown back about incorrect SQL syntax and the script runs to completion, but my field does not update at all.

此脚本的值通过jQuery post()馈送到此脚本:

The values for this script are fed through a jQuery post() to this script:

//isset checking here
$postID = (int)$_POST['id'];
$voteType = $_POST['type'];

if ($voteType == "y")
{
    $trueType = "v-cool";
}
elseif ($voteType == "m")
{
    $trueType = "v-meh";
}
elseif ($voteType == "n")
{
    $trueType = "v-shit";
}
else
{
    die();
}

$db = db_Connect();

db_OP_doVote($db, $postID, $trueType);

这似乎还会过滤值并将其正确发送.我无法解决可能出现的问题.要递增的字段是BIGINT(20).

Which also appears to filter the values and send them fine. I can't wrap my head around what the issue could be. The field being incremented is a BIGINT(20).

我想念什么?

解决了这个问题.

N.B的注释打在了头上-绑定列名称会使它被引用，从而使查询无效.谢谢！

N.B's comment hit the nail on the head - binding the column name causes it to be quoted, which invalidates the query. Thanks!

推荐答案

您无法使用PDO参数化列名称. 所要做的就是具有硬编码的值(您基本上已经有了)并相应地构造SQL字符串.我也会在实际函数中检查此值，只是为了安全起见:

You can't parameterize column names with PDO. What you can do is have hard-coded values (which you basically already have) and construct the SQL string accordingly. I would check this value in the actual function too though, just to be on the safe side:

function db_OP_doVote($pdo, $postid, $votetype)
{
    if( !in_array( $votetype, array( 'v-cool', 'v-meh', 'v-shit' /*, etc. */ ), true ) )
    {
        throw new InvalidArgumentException( 'Unexpected $votetype: ' . $votetype );
        // or simply return false perhaps
    }

    $sql = '
        UPDATE content_posts
        SET `' . $votetype . '` = `' . $votetype . '` + 1
        WHERE `id` = :id
    ';

    $prepStatement = $pdo->prepare( $sql );

    $prepStatement->execute(array(':id' => $postid));

    echo "Success";
}

但是，此策略表明您的数据库设计可能需要更多注意.您现在拥有的方式是，对于每种投票类型，都有一列.这并不是真正有效和/或灵活的数据库设计.如果要求您添加另一种投票方式会怎样?

However, this strategy suggests your database design could use a little more attention. The way you have it now, is that for every type of vote, you have a column. This is not really efficient and/or flexible database design. What happens if you get asked to add another type of vote?

我建议添加另一个表，以使其更加灵活:

I'd suggest adding another table, to be more flexible:

CREATE TABLE `content_post_vote` (
  `content_post_id` int(11) NOT NULL,
  `vote_type` enum('cool','meh','shit') NOT NULL, # using enum() to assure valid vote types
  `votes` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`content_post_id`,`vote_type`)
)

然后您的查询将类似于:

Then your query would be something like:

$sql = '
    INSERT INTO `content_post_vote` (`content_post_id`,`vote_type`,`votes`)
    VALUES( :id, :votetype, 1 )
    ON DUPLICATE KEY UPDATE `votes` = `votes` + 1
';

如果没有某个主键(content_post_id，vote_type)的记录，此操作将插入表决；如果该记录已存在，则通过表决更新记录.

What this does is insert a vote if there is no record for a certain primary key (content_post_id,vote_type) yet, and else update the record with a vote if the record already exists.

然后要查询数据库以获取特定content_post的特定类型的票数，请执行以下操作:

Then to query the database for how many votes of a particular type a particular content_post has gotten, you do this:

 $sql = '
     SELECT `votes` # or perhaps more columns
     FROM `content_post_vote`
     WHERE `content_post_id` = :id AND
           `vote_type` = :votetype
 ';

这篇关于使用准备好的语句在MySQL/PHP中增加字段的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！