php - Update a table step by step based on another table -

above scheme drew. there's ideal case, when content.user groupped. not groupped. meant in scheme is:

1. at first step, select users.monetos users.id = content.user

2. at second step, decrement users.monetos every content.cpc value (2.1 , 2.2)

when simulating this: select content.user (9)

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

so have 15 value users.monetos users.id=9, go content table and:

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

and every content.user

more extended - want select contet.* rows have content.active = 1 (n). having data, select users.monetos users.id=content.user previous query. , now, maximum (n) steps decrement users.monetos value content.cpc value , on moment when **users.monetos=0 or less 0, want update content , set active='0'**

by words, want share users.monetos amount each content entry (content.cpc each). , there's no more users.monetos make current content entry inactive. , vor every content.user i'he done @ moment shown below. looks bad, don't know do. count on guys. thank you.

$query = "select content.id, content.cpc, conent.user, content.active content join users b on a.user=b.id group b.id a.active='1'";  /** cycle each user **/ foreach($rows = $connector->fetcharray($query) $row ) {     $monetos = $row['monetos'];     $query = "select id, cpc content user={$row['id']}";     /** cycle each users content **/     foreach($contents = $connector->fetcharray($query) $content) {         echo $monetos;         $monetos -= $content['cpc'];         if($monetos <= 0) {             $disable[] = $content['id'];         }     }     if( isset($disable) ) {         $connector->query("update content set active='0' id in(".implode(',',$disable).")");     } } 

by using group_concat group ids , cpcs separated comma later use , group by user id single row of result per user.

on foreach deduce each cpc monetos , there set needs disabled $to_disable array later used disable id's needed to.

$query = "select b.id user_id,                  b.monetos,                  group_concat(a.id order a.id desc) content_ids,                   group_concat(a.cpc order a.id desc) cpc,             content             join users b                on a.user = b.id         group b.id";  $to_disable = array(); $to_enable = array(); foreach($rows = $connector->fetcharray($query) $row) {     $monetos = $row['monetos'];     $data = array_combine(explode(',',$row['content_ids']), explode(',',$row['cpc']));     echo "user {$row['user_id']} have {$monetos}!<br>\n";     foreach ($data $content_id => $cpc)     {         $monetos -= $cpc;         echo "user {$row['user_id']} after content {$content_id} have {$monetos}!<br>\n";         if ($monetos <= 0)         {             echo "user {$row['user_id']} should have content {$content_id} disabled!<br>\n";             $to_disable[] = $content_id;         }         else         {             echo "user {$row['user_id']} should have content {$content_id} enabled!<br>\n";             $to_enable[] = $content_id;         }     }     echo "<br>\n"; }  if (sizeof($to_disable) > 0) {     $connector->query("update content                            set active = 0                         id in (".implode(',',$to_disable).")"); } echo "update content set active = 0 id in (".implode(',',$to_disable).")<br>\n";  if (sizeof($to_enable) > 0) {     $connector->query("update content                            set active = 1                         id in (".implode(',',$to_enable).")"); } echo "update content set active = 0 id in (".implode(',',$to_enable).")"; 

---

using sql dump get:

user 9 have 15! user 9 after content 16 have 10! user 9 after content 30 have 5! user 9 after content 17 have 4! user 9 after content 31 have -1! user 9 should have content 31 disabled! user 9 after content 18 have -4! user 9 should have content 18 disabled! user 9 after content 32 have -9! user 9 should have content 32 disabled! user 9 after content 20 have -13! user 9 should have content 20 disabled! user 9 after content 33 have -18! user 9 should have content 33 disabled! user 9 after content 21 have -22! user 9 should have content 21 disabled! user 9 after content 34 have -26! user 9 should have content 34 disabled! user 9 after content 22 have -31! user 9 should have content 22 disabled! user 9 after content 24 have -36! user 9 should have content 24 disabled! user 9 after content 26 have -41! user 9 should have content 26 disabled! user 9 after content 29 have -45! user 9 should have content 29 disabled!  user 10 after content 28 have 95!  user 11 after content 27 have -4! user 11 should have content 27 disabled! 

and update result:

update content set active = 0 id in (31,18,32,20,33,21,34,22,24,26,29,27) 

and here sample code used read data is:

<?php     // 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 user_id,                b.monetos,                group_concat(a.id order a.id desc) content_ids,                 group_concat(a.cpc order a.id desc) cpc           content           join users b              on a.user = b.id       group 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) $row) {     $monetos = $row['monetos'];     $data = array_combine(explode(',',$row['content_ids']), explode(',',$row['cpc']));     echo "user {$row['user_id']} have {$monetos}!<br>\n";     foreach ($data $content_id => $cpc)     {         $monetos -= $cpc;         echo "user {$row['user_id']} after content {$content_id} have {$monetos}!<br>\n";         if ($monetos <= 0)         {             echo "user {$row['user_id']} should have content {$content_id} disabled!<br>\n";             $to_disable[] = $content_id;         }         else         {             echo "user {$row['user_id']} should have content {$content_id} enabled!<br>\n";             $to_enable[] = $content_id;         }     }     echo "<br>\n"; }  if (sizeof($to_disable) > 0) {     $ids = implode(',',$to_disable);     $sql = "update content                 set active = 0              id in ({$ids})";     $disable = $con->prepare($sql);     $disable->execute();     echo "update content set active = 0 id in ({$ids})<br>\n"; } else {     echo "nothing disabled...<br>\n"; }  if (sizeof($to_enable) > 0) {     $ids = implode(',',$to_enable);     $sql = "update content                 set active = 1              id in ({$ids})";     $enable = $con->prepare($sql);     $enable->execute();     echo "update content set active = 1 id in ({$ids})"; } else {     echo "nothing enabled..."; } $con = null;