How to create daily earnings table for each user using PHP and MySQL?

[FarrisFahad]

I let users earn money through my site, and I want to record their daily earnings.

I want to record their earnings starting from 00:01am

I don't know how to do this using PHP and MySQL.

I know how to create, update, etc database. But I am streuggling with PHP time.

I don't know how to create a database row for each user at the start of the day and then update their earnings until the end of the day.

Here is what I have come up with ...

<?php

$time = '00:01';
$day = 11;
$month = 3;
$year = 2018;

strtotime("{$month} {$day} {$year} {$time}");

// The above function will return a timestamp

What do you think?

[AndrisP]

php function time() also return timestamp but what about it with a table create?

[FarrisFahad]

I want to calculate the earnings of all users every day. But I don't know how to track them daily. I know how to program PHP and MySQL, but I don't know how professionals do it.

[AndrisP]

If you wanti to create new table each day then you can create stored MySQL procedure e.g.

delimiter $$
create procedure `user_earning`(in p_name varchar(30), in p_earn decimal(6,2), out p_sum decimal(6,2))
begin
    set @user_name=p_name, @earn=p_earn, @tb_name = concat('daily_earning_',current_date);

    set @sql_table_create = concat('create table if not exists `',@tb_name,'` (
        `id` int primary key not null auto_increment
        ,`user_name` varchar(30)
        ,`earn` decimal(6,2)
        ,unique key(`user_name`)
    ) engine innodb default charset utf8 collate utf8_general_ci;');
    prepare stmt1 from @sql_table_create;
    execute stmt1;
    deallocate prepare stmt1;
    commit;

    set @sql_table_insert = concat('insert into `', @tb_name, '`(`user_name`,`earn`) 
    values (?,?) on duplicate key update `earn` = `earn` + values(`earn`);');
    prepare stmt2 from @sql_table_insert;
    execute stmt2 using @user_name, @earn;
    deallocate prepare stmt2;
    commit;

    set @sql_data_select = concat('select `earn` into @earn_sum from `', @tb_name, '` where `user_name` like ?');
    prepare stmt3 from @sql_data_select;
    execute stmt3 using @user_name;
    deallocate prepare stmt3;
    set p_sum = @earn_sum;
end; $$
delimiter ;

and then call from php script:

$sql = "call `user_earning`(:bv_user_name, :bv_add_earn, @p_sum);
select :bv_user_name as 'user', :bv_add_earn as 'add', @p_sum as 'sum';";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':bv_user_name', $user, PDO::PARAM_STR, 30);
$stmt->bindParam(':bv_add_earn', $earn, PDO::PARAM_STR, 6);
$user = $user_name;
$earn = $add_sum;
$stmt->execute();
$rowset = 0;
do {
    if($rowset===1){
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
    }
    ++$rowset;
    while ($stmt->nextRowset());
}
print_r($result);

This will create 30 tables each month (365 tables each year). Do you need it?
Other solution make table e.g.

create table if not exists `user_earning` (
    `id` int primary key not null auto_increment
    ,`earn_date` date
    ,`user_name` varchar(30)
    ,`earn` decimal(6,2)
    ,unique key(`earn_date`,`user_name`)
) engine innodb default charset utf8 collate utf8_general_ci;

and update with simply:

insert into `user_earning`(`earn_date`,`user_name`,`earn`) 
    values (current_date, :bv_user_name, :bv_add_earn)
    on duplicate key update `earn` = `earn` + values(`earn`);

[FarrisFahad]

Thank you @AndrisP,

I have found a great solution ...

I used Cronjobs to excute a php file each day. Thank you very much for the help :)

For those who have the same problem. Here is what I did:

1. Learn about Cronjobs, it's very simple just like htaccess but for timly tasks.
2. Write your PHP file path in the cronjob command.
3. Create a record everyday for all users.

You also should know that your hosting provider may have a cron option that can create and delete crons.

Farris :)

[XxRedSteelxX]

What if I have to insert records in a single table instead of creating tables everyday?