CODEWITHSUNDEEP
phpmysqlcron
Jamez Roz
Jamez Roz

Reputation: 61

count id, insert, then update by number range in another columns in same table mysql

I am trying to update levels in db like this: "count (id) from users table where referral_id = id As aggref set agg_referral = aggref AND level = 1 if aggref in between 1 to 3" So far i know but no success :(

Here is my db structure users table:

  id (AI)      name       Referral id      agg_referral   Level
=========== ========== ================== ============== ======
     1        user A          0                 0          0
     2        user B          3                 0          0
     3        user C          1                 0          0
     4        user D          3                 0          0

Here is my code i am running it using cron job:

  <?php
    include( $_SERVER['DOCUMENT_ROOT'] . '/config.php' );
    mysql_connect(DB_HOST,DB_USER,DB_PASS);
    mysql_select_db(DB_NAME);
    $query="UPDATE users SET agg_referral = (SELECT COUNT(id) from users WHERE users.referral_id = users.id)";
    mysql_query($query);

    ?>

I am expecting results like this

 id (AI)      name       Referral id      agg_referral   Level
=========== ========== ================== ============== ======
     1        user A          0                 0          0
     2        user B          3                 0          0
     3        user C          1                 2          1
     4        user D          3                 0          0

Upvotes: 1

Views: 63

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can use an update join:

update users u1
join (
    select referral_id,
        count(*) cnt
    from users
    group by referral_id
    ) u2 on u1.id = u2.referral_id    
set u1.agg_referral = u2.cnt,
    u1.level = case 
        when cnt < 1
            then 0
        else ceil((- 5 + sqrt(25 + 8 * cnt)) / 2)
        end;

Demo

It finds times an id is referred and then join it with the table to update the counts and level.

The case statement produces 0 for cnt = 0 or less, 1 for cnt between 1 and 3, 2 for cnt between 4 and 7 and . . so on.

To learn more on how the formula works, see https://math.stackexchange.com/questions/2171745/find-group-number-for-a-given-number-from-groups-of-increasing-size

EDIT:

If your initial group size is value other than 3, use this to get the required equation:

ceil((1 - 2 * g + sqrt(4 * g * g + 1 - 4 * g + 8 * cnt)) / 2)

where g is the intial size of the group.

For g = 3, it solves to:

ceil( -5 + sqrt(25 + 8 * cnt) / 2 )

for g = 10, it solves to:

ceil( -19 + sqrt(361 + 8 * cnt) / 2 )

On how this function is made, see the link provided above.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

In MySQL, you can do this by counting the number of referrals for each id using a subquery. Then use that information in the update:

UPDATE users u JOIN
       (SELECT referral_id, COUNT(*) as cnt
        FROM users u
        GROUP BY referral_id
       ) ur
       ON u.id = ur.referral_id
    SET u.agg_referral = ur.cnt,
        u.level = (CASE WHEN ur.cnt BETWEEN 1 and 3 THEN 2 ELSE level END);

Upvotes: 1

Related Questions