根据另一个表逐步更新表

Question

Above is a scheme i drew. There's an ideal case, when content.user are groupped. But usually they are not groupped. 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. Count on you guys. Thank you.

$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

By using GROUP_CONCAT we group the IDs and CPCs separated by a comma for later use and with GROUP BY the user ID we will get a single row of result per user.

On the foreach we deduce each CPC from the MONETOS and from there we set who needs to be disabled to the $to_disable array that is later used to disable all the id's needed to.

$query = "SELECT b.id AS user_id,
                 b.monetos,
                 GROUP_CONCAT(a.id ORDER BY a.id DESC) AS content_ids, 
                 GROUP_CONCAT(a.cpc ORDER BY a.id DESC) AS cpc,
            FROM content a
            JOIN users b 
              ON a.user = b.id
        GROUP BY b.id";

$to_disable = array();
$to_enable = array();
foreach($rows = $connector->fetchArray($query) as $row)
{
    $monetos = $row['monetos'];
    $data = array_combine(explode(',',$row['content_ids']), explode(',',$row['cpc']));
    echo "USER {$row['user_id']} currently have {$monetos}!<br>
";
    foreach ($data as $content_id => $cpc)
    {
        $monetos -= $cpc;
        echo "USER {$row['user_id']} after CONTENT {$content_id} now have {$monetos}!<br>
";
        if ($monetos <= 0)
        {
            echo "USER {$row['user_id']} should have the CONTENT {$content_id} disabled!<br>
";
            $to_disable[] = $content_id;
        }
        else
        {
            echo "USER {$row['user_id']} should have the CONTENT {$content_id} enabled!<br>
";
            $to_enable[] = $content_id;
        }
    }
    echo "<br>
";
}

if (sizeof($to_disable) > 0)
{
    $connector->query("UPDATE content 
                          SET active = 0
                        WHERE id IN (".implode(',',$to_disable).")");
}
echo "UPDATE content SET active = 0 WHERE id IN (".implode(',',$to_disable).")<br>
";

if (sizeof($to_enable) > 0)
{
    $connector->query("UPDATE content 
                          SET active = 1
                        WHERE id IN (".implode(',',$to_enable).")");
}
echo "UPDATE content SET active = 0 WHERE id IN (".implode(',',$to_enable).")";

---

Using your SQL dump this is what I get:

USER 9 currently have 15!
USER 9 after CONTENT 16 now have 10!
USER 9 after CONTENT 30 now have 5!
USER 9 after CONTENT 17 now have 4!
USER 9 after CONTENT 31 now have -1!
USER 9 should have the CONTENT 31 disabled!
USER 9 after CONTENT 18 now have -4!
USER 9 should have the CONTENT 18 disabled!
USER 9 after CONTENT 32 now have -9!
USER 9 should have the CONTENT 32 disabled!
USER 9 after CONTENT 20 now have -13!
USER 9 should have the CONTENT 20 disabled!
USER 9 after CONTENT 33 now have -18!
USER 9 should have the CONTENT 33 disabled!
USER 9 after CONTENT 21 now have -22!
USER 9 should have the CONTENT 21 disabled!
USER 9 after CONTENT 34 now have -26!
USER 9 should have the CONTENT 34 disabled!
USER 9 after CONTENT 22 now have -31!
USER 9 should have the CONTENT 22 disabled!
USER 9 after CONTENT 24 now have -36!
USER 9 should have the CONTENT 24 disabled!
USER 9 after CONTENT 26 now have -41!
USER 9 should have the CONTENT 26 disabled!
USER 9 after CONTENT 29 now have -45!
USER 9 should have the CONTENT 29 disabled!

USER 10 after CONTENT 28 now have 95!

USER 11 after CONTENT 27 now have -4!
USER 11 should have the CONTENT 27 disabled!

And the UPDATE result:

UPDATE content SET active = 0 WHERE id IN (31,18,32,20,33,21,34,22,24,26,29,27)

And here is the sample code used to read the data as is:

<?php    
// Your database info
$db_host = '';
$db_user = '';
$db_pass = '';
$db_name = '';

$con = new PDO("mysql:host={$db_host};dbname={$db_name}", $db_user, $db_pass);
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = "SELECT b.id AS user_id,
               b.monetos,
               GROUP_CONCAT(a.id ORDER BY a.id DESC) AS content_ids, 
               GROUP_CONCAT(a.cpc ORDER BY a.id DESC) AS cpc
          FROM content a
          JOIN users b 
            ON a.user = b.id
      GROUP BY b.id";
$result = $con->prepare($sql);
$result->execute();

if ($result->rowCount() == 0)
{
    die('No data found...');
}

$to_disable = array();
$to_enable = array();
foreach($result->fetchALL(PDO::FETCH_ASSOC) as $row)
{
    $monetos = $row['monetos'];
    $data = array_combine(explode(',',$row['content_ids']), explode(',',$row['cpc']));
    echo "USER {$row['user_id']} currently have {$monetos}!<br>
";
    foreach ($data as $content_id => $cpc)
    {
        $monetos -= $cpc;
        echo "USER {$row['user_id']} after CONTENT {$content_id} now have {$monetos}!<br>
";
        if ($monetos <= 0)
        {
            echo "USER {$row['user_id']} should have the CONTENT {$content_id} disabled!<br>
";
            $to_disable[] = $content_id;
        }
        else
        {
            echo "USER {$row['user_id']} should have the CONTENT {$content_id} enabled!<br>
";
            $to_enable[] = $content_id;
        }
    }
    echo "<br>
";
}

if (sizeof($to_disable) > 0)
{
    $ids = implode(',',$to_disable);
    $sql = "UPDATE content 
               SET active = 0
             WHERE id IN ({$ids})";
    $disable = $con->prepare($sql);
    $disable->execute();
    echo "UPDATE content SET active = 0 WHERE id IN ({$ids})<br>
";
}
else
{
    echo "Nothing was disabled...<br>
";
}

if (sizeof($to_enable) > 0)
{
    $ids = implode(',',$to_enable);
    $sql = "UPDATE content 
               SET active = 1
             WHERE id IN ({$ids})";
    $enable = $con->prepare($sql);
    $enable->execute();
    echo "UPDATE content SET active = 1 WHERE id IN ({$ids})";
}
else
{
    echo "Nothing was enabled...";
}
$con = NULL;