MySQL复杂的查询。 基于另一个动态更新表中的行

Question

Above is a scheme I drew. There's an ideal case, when content.user are grouped. But usually they are not grouped. What i meant in this scheme is:

1. At the first step, I select users.monetos WHERE users.id = content.user

2. At the second step, I decrement users.monetos with every content.cpc value (2.1 , 2.2)

When simulating this: select content.user (9)

select users.monetos Where users.id=content.users (15)

So we have 15 value for users.monetos for users.id=9, now we go back to content table and:

1. decrement 15 value with 8 (content.cpc) (15-8=7 > 0 -> go to step 2)
2. decrement 7 (result from previous step) with 10 (content.cpc) (7-10=-3 <0 -> update content set active='0' where content.id= (current id when a negative result obtained) )

And like this for every content.user

More extended - I want to select contet.* rows which have content.active = 1 (n). Having this data, SELECT users.monetos WHERE users.id=content.user from previous query. And now, by maximum (n) steps I decrement users.monetos value by content.cpc value and on the moment when **users.monetos=0 or less than 0, i want to update content and SET active='0'**

By words, I want like to share users.monetos amount to each content entry (content.cpc for each). And there's no more users.monetos make the current content entry inactive. And do this vor every content.user What i'he done at this moment is shown below. I now it looks really bad, but I already don't know what to do.

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

Answer 1

You can try this code:

$query = "select * from users";
foreach($rows = $connector->fetchArray($query) as $row ) {
    $a='';
$query = "select * from content where user='{$row[id]}' and active='1' ";
foreach($contents = $connector->fetchArray($query) as $content ) {
    $a++;
    $a_min=$a-1;
    $test[$a]=$row[monetos]-$content[cpc];      
    if($a==2){
        if($test[$a_min] > 0){
            $test2=$test[$a_min]-$content[cpc];
            if($test2 < 0){
                $connector->query("update content set active='0' where id='$content[id]'");
            }
        }
    }
}   
}