Currently, I have this php code where the "//add data to paid table" executes successfully.
(and successfully adds the '$site_add_amount' into the 'site_amount' table column):

$site_add_amount = $video_cost_new *0.50;

// add data to paid table
$insert_buy = $db->insert('paid_videos', [
'id_user' => $user_id,
'video_play_price' => (string)$video_cost_new,
'id_video' => $video_id,
'site_amount' => $site_add_amount,
'time' => $time_start
]);

However, I'm trying to get 'site_add_amount' to be totaled/grouped in the db, each time the code is executed, so it shows a continuous total, of every 'site_amount'. (Also, I have a field in the db table named: 'site_total').

any suggestions/guidance/help is appreciated

Might need more details, but to me it sounds like the site_total could be grabbed by a query whenever needed of the paid_videos table, rather than doing some calculation and incrementing after each insert, e.g.:

select sum(site_amount) from paid_videos where . . .

(Not sure what the where clause would be, depending on how you want to limit which rows you want to total?)

    chrisj Also, I have a field in the db table named: 'site_total'

    Are you trying to show the total of all transactions, or site-wide transactions per user? Honestly, in either case I'd say what you have now is bad database design.

    chrisj However, I'm trying to get 'site_add_amount' to be totaled/grouped in the db, each time the code is executed, so it shows a continuous total, of every 'site_amount'.

    This should be done in your code, not the database. Granted, you can absolutely SUM it as NogDog stated, but you can also SUM the site_amount values across the entire database by using a small sub-query. If you're doing by user, just add the user_id as the WHERE clause in said sub-query. And you're not cluttering the database with redundant or inaccurate information.

    For instance, if you do actually have a column named site_total, unless you update that column on every single record in your table every single time you add a new record, every value of site_total except the very last is wrong.

          Write a Reply...