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?

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

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

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

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.

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

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`);
Re: How to create daily earnings table for each user using PHP and MySQL? 80 80

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 :)

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

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

commented: You answered yourself. Use the insert command and only create the table once. -3
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.