Help with checking 'balance' after 'wallet'

[Chrisj]

I am using a php web video script which allows Users to purchase videos successfully. The purchases are made from the amount available in the Users’ “wallet” (the User can also earn compensation, which gets added to his “balance”.). When there’s not enough available in the “wallet” for the purchase, the script checks and displays a message “not enough money”. I’d like help adding the ability where the script first checks the “wallet” amount, and if empty will then check the “balance” amount, and use a required amount from the balance, and if both are empty, then the “not enough money” appears. Here’s the portion of the code that I believe needs the modification:

// get cost video
    $db->where('name', 'video_play_price');
    $db_cost = $db->getOne('config');
    $video_cost = (float)$db_cost->value;

    $count_video = count($id_array);
    $user_id = $user->id;
    $wallet = (float)str_replace(',', '', $user->wallet);


	$amout = 0;
	foreach ($id_array as $id) {
            $video_id = (int)PT_Secure($id);

            // get video data
            $video = $db->where('id', $id)->getOne(T_VIDEOS);
			$amout += $video->video_play_price?$video->video_play_price:$video_cost;
	}

//   $amout = $video_cost * $count_video;

    $charge = ( $video_cost *0.50 );



    if ($wallet >= $amout) {

        //$new_wallet = (string)($wallet - $amout);
        $wallet = (string)($wallet - $amout);

        $db->startTransaction();

        $inserted_records = 0;
        foreach ($id_array as $id) {
            $video_id = (int)PT_Secure($id);


		// $uploader_amount = $video_cost - $charge; //100 - 20% = 80

            // get video data
            $video = $db->where('id', $id)->getOne(T_VIDEOS);



			$video_cost_new = $video->video_play_price?$video->video_play_price:$video_cost;

			$uploader_amount = ( $video_cost_new *0.50 );
            // add data to paid table
            $insert_buy = $db->insert('u_paid_videos', [
                'id_user' => $user_id,
                'id_video' => $video_id,
                'session_key' => $_SESSION['session_key'],
                'video_play_price' => (string)$video_cost,
                'video_title' => $video->title,
                'user_id_uploaded' => $video->user_id,
            ]);

            if ($insert_buy) { $inserted_records++; }
            //add wallet users' video
        $userwallet = $db->where('id', $video->user_id)->getOne(T_USERS);


        //$videouserwallet = $userwallet->balance+$video_cost;
        $videouserwallet = $userwallet->balance+$uploader_amount;
        $db->where('id', $video->user_id);
        $update_balance = $db->update(T_USERS, [
          // 'wallet' => $videouserwallet,
            'balance' => number_format($videouserwallet, 1, '.', ''),
        ]);
        }


        $db->where('id', $user_id);
        //$update_wallet = $db->update(T_USERS, [
        $update_wallet = $db->update(T_USERS, [
            'wallet' => $wallet,
        ]);


        if (($inserted_records == $count_video) && $update_wallet) {
            $db->commit();

            echo json_encode([
                'status' => 200
            ]);
            exit();
        } else {
            $db->rollback();

            echo json_encode([
                'status' => 400,
                'error' => 'Buy process error'
            ]);
            exit();
        }

    } else {

        echo json_encode([
            'status' => 400,
            'error_num' => 1,
            'error' => 'Not enough money'
        ]);
        exit();

    }

} else {

    echo json_encode([
        'status' => 400,
        'error' => 'Bad Request, Invalid or missing parameter'
    ]);
    exit();

 

[gw1500se]

So what have you tried and what is not working as expected?

[Chrisj]

Thanks so much for your message.

Currently, the script just checks the 'wallet' and displays 'not enough money' if no funds are available. As far as 'what have you tried', I'm looking for a suggestion, because I don't know what to try.

[mac_gyver]

this code contains no useful comments, making it difficult to determine what it is trying to do, which makes it impossible to make changes to it. the original author, and you as you are making changes to it, should put useful comments in the code that describe what it is doing and why. this is what i think it is trying to do -

<?php

if(true) // whatever the actual conditional logic is
{
	// get cost video
	// get the default video price, to use if there is no per video play price
	$db->where('name', 'video_play_price');
	$db_cost = $db->getOne('config');
	$video_cost = (float)$db_cost->value;

	// the number of submitted videos - used to determine if all records were inserted
	$count_video = count($id_array);
	$user_id = $user->id; // copy the user id for some reason
	
	// values shouldn't have thousands separators stored with them. formatting a number should only be done when it is displayed
	$wallet = (float)str_replace(',', '', $user->wallet);

	// add up the video prices
	$amout = 0;
	foreach ($id_array as $id) {
		
		// assuming this pt_secure function is actual secure, any $id in the following code should use $video_id instead
		$video_id = (int)PT_Secure($id);

		// get video data
		$video = $db->where('id', $id)->getOne(T_VIDEOS);
		// add the video play price if any, or the default price
		$amout += $video->video_play_price?$video->video_play_price:$video_cost;
	}

	// determine if the user has enough credits
	// it is here that you would include the balance amount
	if ($wallet >= $amout) {

		// you would calculate new wallet and balance amounts
		$wallet = (string)($wallet - $amout);

		// insert/update all the following queries as a group
		$db->startTransaction();

		$inserted_records = 0;
		foreach ($id_array as $id)
		{
			// again, all the rest of the code should use $video_id, not $id
			$video_id = (int)PT_Secure($id);

			// get video data
			$video = $db->where('id', $id)->getOne(T_VIDEOS);

			// use the video play price if any, or the default price
			$video_cost_new = $video->video_play_price?$video->video_play_price:$video_cost;

			// add data to paid table
			$insert_buy = $db->insert('u_paid_videos', [
				'id_user' => $user_id,
				'id_video' => $video_id,
				'session_key' => $_SESSION['session_key'],
				'video_play_price' => (string)$video_cost, // the cost at the time of purchase // this is the default video cost not the $video_cost_new
				'video_title' => $video->title, // storing the title is redundant since you can retrieve it from the source video information 
				'user_id_uploaded' => $video->user_id, // the user who uploaded the video. this is also redundant
				// this should include the datetime of the purchase - actually there should be a purchase/order table and a purchase/order_items table
			]);

			// count successful inserted records
			if ($insert_buy) {
				$inserted_records++;
			}
			
			//update the 'balance' of the user who uploaded the video
			// get the user's record
			$userwallet = $db->where('id', $video->user_id)->getOne(T_USERS);

			// credit the user 50% of the video cost
			$uploader_amount = $video_cost_new *0.50;

			// add to the balance
			$videouserwallet = $userwallet->balance+$uploader_amount;
			// update the record
			$db->where('id', $video->user_id);
			$update_balance = $db->update(T_USERS, [
				'balance' => number_format($videouserwallet, 1, '.', ''), // this is formatting with one decimal point only
			]);
		}

		// update the current user's wallet (and balance) amounts
		$db->where('id', $user_id);
		$update_wallet = $db->update(T_USERS, [
			'wallet' => $wallet, // you would include the new 'balance' too
		]);

		// if all the video records were inserted and the current user's wallet was updated, commit the changes
		if (($inserted_records == $count_video) && $update_wallet) {
			$db->commit();

			echo json_encode([
				'status' => 200
			]);
			exit();
		} else {
			$db->rollback();

			echo json_encode([
				'status' => 400,
				'error' => 'Buy process error'
			]);
			exit();
		}

	} else {

		echo json_encode([
			'status' => 400,
			'error_num' => 1,
			'error' => 'Not enough money'
		]);
		exit();
	}

} else {

	echo json_encode([
		'status' => 400,
		'error' => 'Bad Request, Invalid or missing parameter'
		]);
	exit();
}

if i have incorrectly deduced what any of these things are doing, make corrections to the comments before proceeding.

if this code had useful comments, so that the meaning of the line of code producing the $video_cost_new value would be in recent memory, there probably wouldn't be a mistake in the pricing value used in the insert query. the following line should use $video_cost_new -

'video_play_price' => (string)$video_cost, // the cost at the time of purchase // this is the default video cost not the $video_cost_new

 

next, the data design has one serious flaw and one that should be corrected -

1) the wallet and balance amounts should NOT be just columns in the user's row that get updated to change the value. by having just a value, you don't have any 'audit trail' should a programming mistake, duplicate form submission, or nefarious access alter a value incorrectly. you should instead have a separate 'account' table that gets a new row inserted for each transaction that affects the amount. this table would have all the who, what, when, where, and why information about each transaction ('wallet' or 'balance' would just be transaction types.) to get the total at any point in time for a user, you would just SUM() the +/- amounts for any particular user id. to get the either or both of the current 'wallet' or 'balance' amounts, you would group by the transaction type for any particular user id.

2) you should actually have two tables to hold the video purchase information. the first table should hold the unique/one-time information about the purchase. this will assign a purchase order id. the second table would hold the unique/one-time information about the items that were purchased, related back to the first table through the order_id.

lastly, i put comments in the code where it would need to be modified to do what you are asking. two are near the if ($wallet >= $amout) { conditional test (LOL amount is misspelled in the code.) the last one is in the update query (which should instead be one or two insert queries for the account table) for the current user's wallet (and balance) amounts.

Edited February 1, 2019 by mac_gyver

[Chrisj]

Thank you for your helpful reply. Much appreciated.

I've replaced my original file with your commented code. Much thanks for helping clarify what is going on there, and for the small changes that you made to the (now improved) code.

I am still looking for a solution where the script first checks the “wallet” amount, and if empty will then check the “balance” amount, and use a required amount from the balance, and if both are empty, then the “not enough money” appears. This was suggested, but I failed at integrating it into the code:

if($wallet->balance() + $balance->balance() >= $amount) {
    $walletAmount = min($amount, $wallet->balance();
    $balanceAmount = ($walletAmount < $amount) ? $amount - $walletAmount : 0;
    // could then create deduct() methods to extract funds
    $wallet->deduct($walletAmount);
    $balance->deduct($balanceAmount);
}
else {
    // handle not enough money here
}

any guidance with that would be very helpful.

In regard to an 'account' table, thanks for that great advice. The script currently does have u_paid_videos table, as you can see at: // add data to paid table. Would expanding this table to include more be satisfactory? I've attached a picture of it's structure (all though time_date just shows NULL ).

Any additional comments/direction/advice is welcomed.

[Chrisj]

I have now set-up a separate 'account' table named 'purchases' (see attached image). What should the 'order_id' structure be?

Any additional guidance will be appreciated

[mac_gyver]

Quote

In regard to an 'account' table, thanks for that great advice. The script currently does have u_paid_videos table, as you can see at: // add data to paid table. Would expanding this table to include more be satisfactory? I've attached a picture of it's structure (all though time_date just shows NULL ).

no. the u_paid_videos table holds the information about the items(videos) in the order, but it contains redundant data. it has nothing to do with the accounts table i mentioned. everything i stated has to do with database normalization (you can research what that means) and is about properly organizing the data, so that you are only storing the data you need and are not repeating values in multiple places.

you are keeping track of two different type of things, 1) information about items/videos that are being purchased (as already stated needs an orders and an order_items table), and 2) user's money/credits.

the orders table should have at a minimum, columns for - an id (auto-increment, produces an order_id), user_id (who placed the order), date_time (when the order was placed), and a status column (indicates what state the order is in.) i'm not sure what the session key is, but if it needs to be stored per order it would go in the orders table.

the order_items table should have at a minimum, columns for - an id (auto-increment), order_id (what order the item is part of), item_id (the id of the item/video), quantity (this code is just a cart script and in general should support any quantity of an item), purchase_price (the price at the time of purchase), and a status column (indicates the status for each item in the order.)

the account table should have at a minimum, columns for - an id (auto-increment), user_id (who the account belongs to), amount (the + or -- amount), transaction_type (wallet, balance), and date_time (when the transaction was made.)

Edited February 3, 2019 by mac_gyver

[Chrisj]

Thanks for your reply.

I appreciate your time you've taken to explain. It all makes sense.
It is  a lot for me to implement, although I'm working on it.

However, the first step for me, I believe, would be to get help with adding in these things you've commented, please, before I start improving my tables.:
// it is here that you would include the balance amount
// you would calculate new wallet and balance amounts
// you would include the new 'balance' too

any guidance, examples of what's needed there, will be greatly helpful.

[Chrisj]

 

In my account 'wallet' I have 3, and in 'balance' I have 3, which equals a total of 6. I tried to purchase a total of 5, with this code added: 

if($wallet >= $amout) {
   // take money first from wallet
} elseif ($wallet + $balance >= $amout) {
   // take money first from wallet and/or balance
} else {
		echo json_encode([
			'status' => 400,
			'error_num' => 1,
			'error' => 'Not enough money'
		]);

} else {

I saw 
"Something went wrong. Please try again later!!"

 

any additional help is appreciated

[Chrisj]

The "Something went wrong. Please try again later!!" is in a pop-up dialog box, and comes from the script.js file.

Also, when I comment out those additional lines of code, the script gets no error.

 

 

[Chrisj]

So, I just tried this without success:
 

Code:

	if ($wallet >= $amount) {
		$wallet = (string)($wallet - $amount);
		} elseif ($wallet + $balance >= $amount) {
		$balance = (string)($balance - $amount);
		$db->startTransaction();

etc....

By ‘without success’, I mean that the same message appears as if just the ‘wallet’ doesn’t have enough “Not Enough Money” - when I test - by having 3 in ‘wallet’ and 3 in earnings (equals a total of 6) but try to purchase something that costs 5.

Any suggestion, guess or guidance will be appreciated.

[Barand]

try something like this

$amount = 5;
$wallet = 3;
$balance = 3;

if ($wallet >= $amount) {
    $wallet -= $amount;
}
elseif ($wallet + $balance >= $amount) {
    $amount_remain = $amount - $wallet;
    $wallet = 0;
    $balance -= $amount_remain;
}
else {
    echo "Insufficient funds<br>";
}
echo "W: $wallet<br>B: $balance";

 

[Chrisj]

Thanks for your reply. Much appreciated.

I have tried your suggestion without success.

I see a pop-up dialog box  that displays: "Something went wrong. Please try again later!!" (from the script.js file):

 

function PT_MultipleBuyVideo() {
    var checked = getSelectedVideos();
    if (!checked) { return false; }

    swal({
        title: "",
        type: "info",
        html:"Simply proceed to purchase " + countSelectedVideos() + " video(s) at a total cost of " + countTotalCredits() +" credits",
        showCancelButton: true,
        cancelButtonText: "Close",
        customClass: 'sweetalert-lg',
        confirmButtonText:'Proceed'
    }).then(function(){

        $.ajax({
            url: PT_Ajax_Requests_File() + 'aj/buy-video',
            type: 'POST',
            dataType: 'json',
            data: {id:checked},
        }).done(function(data){
            if (data.status == 200) {
                for (var i = 0; i < checked.length; i++) {
                    var button = $("button[data-action='multiple_select_button'][data-id='" + checked[i] + "']")
                    buttonMultipleSelectingStyle(button, 'purchased');
                }

                swal({
                    title: "Success",
                    type: "success",
                    html:"",
                    showCancelButton: true,
                    cancelButtonText: "Close",
                    customClass: 'sweetalert-lg',
                    confirmButtonText:'Go To Video(s)'
                }).then(function(){
                    window.location.href='/paid-list';
                });

            } else {
                if (data.error_num == 1) {
                    swal(
                        'Error!',
                        'Not enough money(test)',
                        'error'
                    );
                } else {
                    swal(
                        'Error!',
                        'Something went wrong. Please try again later!',
                        'error'
                    );
                }
            }
        }).fail(function() {
            swal(
                'Error!',
                'Something went wrong. Please try again later!!',
                'error'
            );
        })
    });
}

The only thing that shows 'not enough money' from 'wallet' or "success' (when there is enough) is this:

		if($wallet + $balance >= $amount) {
		$wallet = (string)($wallet - $amount);
		 //}elseif    $wallet = 0; {
		$balance = ($balance - $amount);

		$db->startTransaction();

Any other ideas will be appreciated

 

 

[Barand]

Try reading your own code. Go through it line by line and check the value of each variable after each line executes. Does it look right to you? (you should end up with 0, 1, 5)

                                                        vales after executing line
                                                       -----------------------------
                                                       wallet     balance    amount
                                                          3          3         5

if($wallet + $balance >= $amount) {                       3          3         5
        $wallet = (string)($wallet - $amount);           -2          3         5
         //}elseif    $wallet = 0; {
        $balance = ($balance - $amount);                 -2         -2         5

 

[Chrisj]

Thanks for your reply. 

This seems to work successfully, where a purchase uses the amount in ‘wallet’ first, and then uses the amount in ‘balance’ if there is not enough in ‘wallet’:

however, if all videos cost 2 (or higher), and there is “1” left in the ‘wallet’, it will never get used. I am looking to see how I can make more like if ‘wallet’ is zero then deduct from ‘earnings’ (or wallet + balance = amount)…

I have tried this revision without success:

		// Check if user has enough wallet amount to purchase video
		if($wallet >= $amount){

			$wallet = (string)($wallet - $amount);
			$db->where('id', $user_id);
			$update_wallet = $db->update(T_USERS, [
				'wallet' => $wallet
			]);

			} else {

			if($wallet < $amount && $wallet + $balance >= $amount){

			$wallet = (string)($wallet - $amount) + $balance = (string)($balance - $amount);

							$db->where('id', $user_id);
							$update_user_balance = $db->update(T_USERS, [
								'balance' => $balance
								]);
							$db->where('id', $user_id);
							$update_wallet = $db->update(T_USERS, [
							'wallet' => $wallet
							]);
						}
			}

I’m trying to say this;
if what’s in the ‘wallet’ is less than the amount AND $balance has more than or equal to the amount:

if($wallet < $amount && $wallet + $balance >= $amount){

 

then proceed to deduct whatever is left in 'wallet' to satisfy the amount + the rest of the amount from $balance:

$wallet = (string)($wallet - $amount) + $balance = (string)($balance - $amount);

 

but, apparently I need to say this better in code:
“then proceed to deduct whatever is left in 'wallet' to satisfy the amount + the rest of the amount from $balance”

any additional help is greatly appreciated.

 

Edited February 8, 2019 by Chrisj

[mac_gyver]

you have been given working logic (here and phpbuilder, which i just verified that both work) to test the amount, wallet, and wallet2/balance/earnings (you keep changing what it is called) and to calculate new wallet and wallet2/balance/earnings amounts, yet the code you continue to 'try' is using neither method. are you even reading the replies you are getting?

next, in the code i reviewed and added comments to here, there's no logic to get the starting wallet2/balance/earnings value. wouldn't that be a necessary step to get the code to work?

btw - your existing code has an UPDATE query at the correct point within the transaction/rollback logic. you would want to use the new wallet and wallet2/balance/earnings amounts in that existing single query, per the comment i added at that point, rather than to start adding more queries before the start of where the transaction/rollback logic starts.

Edited February 11, 2019 by mac_gyver

[Chrisj]

Thanks again for your reply.

Yes, I've been reading. I've been given a lot of advice and tried many things without success (including what was provided here and elsewhere).
But, some of the explanation I don't understand, like this sentence "use the new wallet and balance amounts in that existing single query, per the comment i added at that point, rather than to start adding more queries before the start of where the transaction/rollback logic starts". 

Earlier in this thread I was told "Try reading your own code. Go through it line by line and check the value of each variable after each line executes". Based on that, as far as I can see, this should work, but shows 'not enough money:

 

} else {

//     4    +    4      >=    6
if ($wallet + $balance >= $amount) {

//   8  =               4    +    4
$balance = (string)($balance + $wallet);

//   2   =              8    -    6
$balance = (string)($balance - $amount);
$wallet= '0';

$db->where('id', $user_id);
$update_user_balance = $db->update(T_USERS, [
'balance' => $balance
]);

}
}

 

any additional guidance is appreciated

 

 

[Chrisj]

Hello McGyver,

I have re-read your great advice (thank you again), regarding the "two tables". I  am trying to understand more clearly what you've advised, and trying to improve what is already there (rather than re-create everything). Would it be possible that you could message me so I might be able to ask you some more specifics directly?

Thanks again for your kind posting replies. I look forward to your positive response.

Many thanks again

 

[Chrisj]

There are several db tables ( phpmyadmin ) working with the web php script that I’m using (but did not write).

The ‘user’ table has many fields, but pertaining to purchases it has these fields: ‘ip_address’ ‘username’ ‘wallet’ and ‘balance’.

The ‘paid_videos’ table has these fields: id_user, video_play_price, id_video, user_id_uploaded, video_title, earned_amount, time_date, short_id, session_key
video_id, time.

The ‘transact’ table has these fields: username, id_user, amount, balance, wallet, wal_bal, user_id_uploaded, earned_amount, time_date.

When a purchase is made a single row is populated in the ‘paid videos’ table and a single row is populated in the ‘transact’ table.

Additional info: when a purchase is made, an amount of 50% of the price (‘earned_amount’) appears in uploader’s (user_id_uploaded) ‘earned_amount’ field,
and gets added to the uploader’s ‘balance’ field.

And that amount is also reflected in ‘amount’ by a negative number, and reduces the purchaser’s ‘wallet’ or ‘balance’ by that same amount.
Also, wal_bal is total of wallet and balance.

I am looking for comments/suggestions for improvement. And/or besides improvement, what am I missing?

[mac_gyver]

Quote

Posted January 31

chrisj, what is the overall reason you have been beating on this script (and the previous phpmotion script) for so long?

1. is this for a collage course assignment?
2. is this for self-learning?
3. do you think that having a video sharing script will help you to make money?
4. some other reason?

the main reason i ask is, most of your posts seem like you just want someone else to think/write code for you, and your only involvement is to be a 'proxy typist' pushing keys on a keyboard. this is not how programming help works, doesn't result in any learning, and doesn't result in much progress.

you need to be able to define for yourself what the code/data should do, then through learning enough php/sql/html/css/javascript, be able to design, write, test, and debug the code needed to produce an application or make changes to an existing application.

you need to sit down with paper/pencil or an open text document, and list out the top-level work-flow (steps) that need to occur, from the point where someone first visits your site through to the point where they watch a purchased video. you would then define what the user interface (UI) looks like for each step. you would then define what input data, what processing, and what result or output is needed to accomplish each step.

the part of the process where you are at in this thread is when the user has hit the submit button during the 'checkout' step. regardless of this being for physical or digital items or using real money or credits, the processing is the same/similar. you would store/convert the cart items in to a 'pending' (unpaid) order. when payment is confirmed, you would update the order status to 'paid' and either trigger the shipment of physical items or make digital items available for downloading/viewing. since you are using credits that are being managed on your site,  as far as the order status, you would determine if the user has enough credits in their account, update the order status to 'paid', deduct the amount from the user's account, add the earned amount to the video owner's account, and add the reminder to the system's account. all the database queries that modify data as part of this step, must be part of the same database transaction, so that if any of them fail, they all fail and roll-back.

to manage the amounts, each video (which can have its own price, resulting in a different earned amount, and a different remainder for the system) that's purchased should have a separate set of records inserted into the account table. here is the starting definition for the account table -

On 2/3/2019 at 2:21 AM, mac_gyver said:

the account table should have at a minimum, columns for - an id (auto-increment), user_id (who the account belongs to), amount (the + or -- amount), transaction_type (wallet, balance), and date_time (when the transaction was made.)

since the time when i wrote that, i think it would be a good idea to add the video purchase id (auto-increment integer primary index from the paid_videos table) so that the account records are related to the video purchase record they correspond to.

so, what would be inserted into the account table foreach(){} video that is purchased -

1. a row with the purchaser's id (id_user), the negative of the video purchase price, a transaction type id (for a purchase), the last insert id from the row that was inserted into the paid_videos table, and the current datetime.
2. a row with the video owner's id (user_id_uploaded), the positive earned amount for the video, a transaction type id (for an earned amount from a purchase), the last insert id from the row that was inserted into the paid_videos table, and the current datetime.
3. a row with the system's id, the positive remainder between the video purchase price and the earned amount, a transaction type id (for a system share of a purchase), the last insert id from the row that was inserted into the paid_videos table, and the current datetime.

the transaction_type_id in the above means that you need a 'transaction type' table, with an id column and a name column.

to get the account total for any user id, you would just SUM() the total column, and group by the user_id column, for any single, list, or all users (depending on the WHERE clause used.) to get the account total for any/each transaction type, you would additionally group by the transaction type id column.

Edited November 20, 2019 by mac_gyver