php - 根据另一个表更新 mysql 表。复杂查询

Question

简要介绍我的表格:我有 2 个表格:内容、用户

content 结构类似于:编号 |标题 |中国共产党 |用户 |活跃

users is:

id |用户 |莫尼托斯

Idea - 我想选择 contet.* 行，其中 content.active = 1 (n)。有了这些数据，从之前的查询中 SELECT users.monetos WHERE users.id=content.user 。现在，通过最大(n)步，我将users.monetos值减少content.cpc值当 *users.monetos=0 或小于 0 时，我想更新 content 并设置 active=' 0'*

言下之意，我想分享每个内容条目的users.monetos金额(每个content.cpc)。并且不再有 users.monetos 制作当前内容条目无效。对每个 content.user 执行此操作我此时所做的如下所示。我现在看起来很糟糕，但我已经不知道该怎么办了。指望你们。谢谢。

$kak2 = array();
$rs16 = $connector->query("SELECT user FROM content WHERE active='1'");


while($rw16 = $connector->fetchArray($rs16))
{
 $users_ids[] = $rw16['user'];    
}

$user_info2 = $connector->fetchArray("SELECT monetos,id FROM users WHERE id IN (".implode(',',$users_ids).")");

while($user_info = $connector->fetchArray($user_info2))
{
    $current_entry_info2 = $connector->query("SELECT cpc,id FROM content WHERE user='$user_info[id]' ORDER BY date DESC");

    while ($current_entry_info = $connector->fetchArray($current_entry_info2))
    {
        $user_info['monetos']= $user_info['monetos'] - $current_entry_info['cpc'];
        if($user_info['monetos'] = 0)
        {
            $updt = $connector->query("UPDATE content SET active='0' WHERE id='$current_entry_info[id]' LIMIT 1");

        }

    }

}

Answer 1

我想我已经知道你想要做什么，所以构建了一个小代码片段，我认为它应该做你想做的事情。

注意这是未经测试的代码

/** get the cost of all content for all users **/
$query = "select b.id, sum(a.cpc) as cpc from content a
join users b on a.user= b.id
group by b.id where a.active = 1"

/** cycle each user **/
foreach($rows = $connector->fetchArray($query) as $row ) {
    $menotos = $row['cpc'];
    $query = "select id, cpc from content where user={$row['id']}"
    /** cycle each users content **/
    foreach($contents = $connector->fetchArray($query) as $content) {
        $menotos -= $content['cpc'];
        if($menotos <= 0) {
            $disable[] = $content['id'];
        }
    }
    if( isset($disable) ) {
        $connector->query("update content set active=0 where id in(" . implode(',', $disable) . ")";
    }
}