php - 陷入更新列值的困境

Question

这是我的 table 喜欢

id  type parent country votes
1   1   0       US      0 # This value need to be 9
2   2   1       US      6
19  3   1       US      3
3   3   2       US      3
7   3   2       US      3
4   10  3       US      1
5   10  3       US      1
6   10  3       US      1
10  10  7       US      1
9   10  7       US      1
8   10  7       US      1
20  10  19      US      1
21  10  19      US      1
22  10  19      US      1

我正在编写一个脚本来更新表中的总票数。

此处，类型 10 更新类型 3，类型 3 更新 2 和 1，类型 2 更新 1

当您运行我的脚本时，您将看到它是如何工作的。

此处，id 1 需要为 9，并且不应在每次脚本运行时刷新。其他人则不然。但我找不到一种方法来更新 1 而不使其值加倍。

你能帮我想个办法吗？

这是脚本。

$conn = connect();

$what = 10;
$pathType = 15;

while ( $pathType >=2 )
{
$stmt = $conn->prepare("select max(type) as type from likes where type < :type and country = 'US'");
$stmt->bindParam(':type', $pathType);
$stmt->execute();
$pathData = $stmt->fetch();
$pathType = $pathData['type'];
echo 'Path Type is '.$pathType.'<br>';

    $stmt = $conn->prepare("select sum(votes) as votes, parent as parent from likes where type=:type group by parent");
    $stmt->bindParam(':type', $pathData['type']);
    $stmt->execute();
    $rows = $stmt->rowCount();

        while( $row = $stmt->fetch() ) {
            echo $row['parent']." ".$row['votes'];
            echo "<br>";

            if($row['parent'] == 1){
            echo 'Passed Level 1<br>';
                $wtf = $conn->prepare("update likes set votes=votes+:votes where id=:parent");
            }else{
                $wtf = $conn->prepare("update likes set votes=:votes where id=:parent");
            }

            $wtf->bindParam(':votes', $row['votes']);
            $wtf->bindParam(':parent', $row['parent']);
            $wtf->execute();
        }
    echo "-----------------------------------------------<br>";
}

这里是创建的，以防您需要它们:

-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               5.5.25 - MySQL Community Server (GPL)
-- Server OS:                    Win64
-- HeidiSQL version:             7.0.0.4053
-- Date/time:                    2013-05-17 14:41:11
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;

-- Dumping database structure for wwp-db
DROP DATABASE IF EXISTS `wwp-db`;
CREATE DATABASE IF NOT EXISTS `wwp-db` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `wwp-db`;


-- Dumping structure for table wwp-db.likes
DROP TABLE IF EXISTS `likes`;
CREATE TABLE IF NOT EXISTS `likes` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) DEFAULT '0',
  `parent` int(10) DEFAULT '0',
  `country` varchar(2) DEFAULT NULL,
  `votes` int(10) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4176 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- Dumping data for table wwp-db.likes: 14 rows
/*!40000 ALTER TABLE `likes` DISABLE KEYS */;
INSERT IGNORE INTO `likes` (`id`, `type`, `parent`, `country`, `votes`) VALUES
    (1, 1, 0, 'US', 9),
    (2, 2, 1, 'US', 6),
    (3, 3, 2, 'US', 3),
    (4, 10, 3, 'US', 1),
    (5, 10, 3, 'US', 1),
    (6, 10, 3, 'US', 1),
    (7, 3, 2, 'US', 3),
    (8, 10, 7, 'US', 1),
    (9, 10, 7, 'US', 1),
    (10, 10, 7, 'US', 1),
    (19, 3, 1, 'US', 3),
    (20, 10, 19, 'US', 1),
    (21, 10, 19, 'US', 1),
    (22, 10, 19, 'US', 1);
/*!40000 ALTER TABLE `likes` ENABLE KEYS */;
/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Answer 1

likes 中的第一行首先不满足 if 语句的条件。

if($row['parent'] == 1){
  echo 'Passed Level 1<br>'; //This doesn't apply to row with id 1 - but does to one with parent id 1
  $wtf = $conn->prepare("update likes set votes=votes+:votes where id=:parent");
}else{
 //Does not affect row with id 1 as it has a parent of 0
 $wtf = $conn->prepare("update likes set votes=:votes where id=:parent");
}

编辑:好的，它执行这个: 并更新第 0 行

$wtf = $conn->prepare("update likes set votes=votes+:votes where id=:parent");

当它到达父 ID 为 1 的行时，

您指示它添加到未设置值的投票，每次刷新时它自然会添加更多，如果您希望它停止在某个值，请在查询或代码中添加另一个条件