Add data to secondary table

PHP
1

The php webscript I’m trying to modify has a form, with this html line:

<input id="wallet" name="wallet" type="text" placeholder="" class="form-control input-md" value="{{USER wallet}}">

and I believe that amount gets posted to the ‘users’ table > ‘wallet’ column via the ‘wallet’ line in this code:

            if (empty($errors)) {
                $update_data = array(
                    'username' => PT_Secure($_POST['username']),
                    'wallet' => PT_Secure($_POST['wallet']),
                    'email' => PT_Secure($_POST['email']),
                    'gender' => PT_Secure($gender),
                    'country_id' => PT_Secure($_POST['country']),
                    'active' => PT_Secure($active),
                    'admin' => PT_Secure($type),
                    'age' => $age
                );

I’d like guidance with adding the ‘wallet’ amount to another table.
I look forward to ant assistance.

2

I’m not an ant, but I think the first thing you should do is to locate a section of existing code that takes an item of data and puts it into a table. The code you posted builds up an array as it shows, but it does not include the part that posts it into a database table, that must be somewhere else.

Once you’ve identified that code, have a read of it and see whether you can figure out how to write the required data into another separate table.

1 Like
3

Thanks for your reply.
I’ve followed your suggestion and found this code in that same file:

    if (empty($error)) {
        $insert_data    = array(
            'user_id'   => $user_id,
            'amount'    => PT_Secure($_POST['amount']),
            'email'     => PT_Secure($_POST['email']),
            'requested' => time(),
            'currency' => $currency,
        );

        $insert  = $db->insert(T_WITHDRAWAL_REQUESTS,$insert_data);
        if (!empty($insert)) {
            $data['status']  = 200;
            $data['message'] = $lang->withdrawal_request_sent;
        }
    }

So, I added this to that file:

				$insert = $db->insert('transact',
				'id_user' => $user_id,
				'username' => $username,
				'wallet' => $wallet
				);

in an attempt to add user id, username and wallet to another table named ‘transact’, without success.

Any additional assistance is welcomed.

4

What does this mean? What happens, or doesn’t happen? Presumably you’ve checked what values are in those variables at the point that you have used them for the insert, and that they are suitable for the columns you are inserting them into?

Slightly O/T, but what’s the idea of storing the user-id and user-name in this extra table? Surely you can get the username using a JOIN if you need it? Duplicating data isn’t a good idea for all sorts of reasons.

5

Much thanks again.
Without success means no data was displayed in the ‘transact’ table > ‘wallet’ column.
However, the file always successfully adds the ‘wallet’ amount to the ‘users’ table. The ‘wallet’ column in 'users table and the ‘wallet’ column in ‘transact’ table have the same structure, if that’s what you’re asking when you ask if they are ‘suitable’.

I look forward to any additional suggestions

6

And what about the other two variables? Are they correct for the columns, too?

And there’s this:

8

Thanks again,.
Yes, the other two variables are correct for their respective columns, too.
Sorry, I’m not clear on your ‘presumably’ question.
Currently, this populates ‘wallet’ and ‘username’ columns, in ‘transact’ table:

	$insert = $db->insert('transact', [
				'id_user' => $user_id,
				'username' => $user_data->username,
				'wallet' => $user_data->wallet
				]);

however, the amount that appears in ‘wallet’ column in ‘transact’ table is always one number less that the amount that appears in the ‘wallet’ column of the ‘users’ table.
I’m not sure why.
I look forward to any further assistance.

9

I mean - are the values in those variables correct at the point that you have used them for the insert? Or are you presuming that they must be, because you use one or all of them somewhere else in the code? The fact that it now almost works when you use other variables suggest that this might have been contributing to the problem.

OK, so it’s basically working now then? Or does it leave $user_id blank? If it does, what is the value of that variable at the point you call the function? If it is leaving it blank, I’d suggest that you should alter the table layout to not allow it, as it seems like an important column.

By “one number less”, do you mean that the value has decreased by 1 (i.e. that in one table it stored for example 5, but in the transact table it stores 4), or that it has one fewer digits, i.e. in one table it stores 45.23, but in the other it stores 45.2? If it’s the latter, I seem to recall you did some work on the number of decimal places you were storing numbers to, in another thread.

If it’s the former, all you can do is look at the code that runs between where it is inserted into the users table and where you insert it into the transact table. Something must be altering the value.

11

Thanks again for your replies.
This has worked successfully:

				$insert = $db->insert('transact', [
				//'id_user' => $user_id,
				//'id_user' => $user_data->user->id,
				'id_user' => $user_data->user_id,
				'username' => $user_data->username,
				'wallet' =>$_POST['wallet']
				]);

except the ‘id_user’ still shows NULL

I’m sorry, but I’m not clear on part of your reply, it sounds like you’re saying alter the table to not allow something important.

Additionally, the amount entered into the html form field replaces whatever is in the ‘wallet’ column, with each form submit. Ultimately, I’m looking to modify it so that (instead of the entered amount replacing the amount in the ‘wallet’ column) the entered amount adds to (or subtracts) whatever is in the ‘wallet’ column. But, I’m not sure if I should start another posting for that help.

I welcome any comments. thanks again.

12

That completely misses the point of transactions. Do you think your bank has a column somewhere with your balance in it? They don’t. What they do have is a long list of all transactions that occurred on your account and then the sum of all those makes up your balance. Sure, they may cache some of it to speed things up, but that’s how these things work.
If you only keep the value you can never explain why it has that value. If you keep all transactions you can.

Also, why is the wallet used from $_POST? That suggests that if I modify the value in the form (which is easy using modern day developer tools) I can get stuff for less than their actual price?

1 Like
13

Thanks for your reply/insight.
I agree with what you’re saying.
Maybe I’m explaining my modify request unclearly, but I’m trying to get assistance to modify so I “have is a long list of all transactions that occurred…”, as you have described (better than me) that’s what the ‘transact’ table is for. Currently, it only displays the balance/total amount in its ‘wallet’ column. I’d like a field to add or subtract so users > wallet has ongoing balance and ‘transact’ > wallet has each transaction. Any help with that is what I’m looking for.

Regarding, “also, why is the wallet used from $POST…” I’m sure it important to know that the Form we’re discussing only appears to Admin via the Admin panel.

I look forward to additional comments/suggestions.

14

Why?

That is important yes. In that case you don’t have to worry about people hacking the form (as much) :slight_smile:

15

I was suggesting that you modify the table to not allow you to post a new row where the id_user column is blank. That way, your whole insert would fail, rather than allowing invalid data to be stored, presuming your site would consider a transaction with no user-id to be invalid.

So not terribly successfully then. What is the value of $user_data->user_id just before you run this new bit of code? Not what should it be, but what is it when you display it or log it in your debug log? This is what I was asking before - what’s in the variables you use? You keep changing them.

For the first part, you can modify the code so that you retrieve the current balance, add the new figure to it, and write it back, or you can do it within the query. I don’t know the database class you’re using, whether it’s possible to do it via a query. For the second part, that’s what your code here should do, once you figure out why the user-id value is blank.

Please, please, please - don’t call them both “wallet”, that will be confusing to anyone else who has to work with the system, especially if one “wallet” column contains a balance, and the other contains a transaction amount. Give them proper, meaningful names.

1 Like
16

Thanks again for the helpful replies.
So, I have gotten this

'id_user' => $user_data->id,

to populate the id_user column with the correct user id.

Now, it is my understanding that I need to “retrieve the current balance, add the new figure to it, and write it back”, so, I believe you mean something like the code below, plus the html entered value = the new wallet balance? something like that?:

$db = "SELECT wallet FROM users";

but, I don’t know how to tie it back to what’s entered into the html form:

<input id="wallet" name="wallet" type="text" placeholder="" class="form-control input-md" value="{{USER wallet}}">

I look forward to any further assistance

17

You’re heading in the right direction. Think about that query though - you will need to specify which wallet you need to retrieve - you don’t want all entries in that table, you just want one specific one.

You already have code to take the information from the html form, and put it into a variable, because your code is writing it into two tables now. You also know how to put a value back into a table using an UPDATE query, because you’re doing that as well. You must have code somewhere that adds the value of two variables together because your whole system has transactions and balances all over the place. You already have all the building blocks.

There’s also the note from @rpkamp about whether it’s a good idea to store a balance in your table. It’s surprisingly easy for this to go out of sync with the sum of the transactions. You might want to read up on MySQL transaction handling.

18

No, they must read up on MySQL transaction handling before you start doing this. We’re talking about people’s money here. There is no such thing as trying some stuff and hope for the best when it comes to actual money are paying for your product. That stuff must be correct. At least to keep a good reputation for your product, if nothing else.

The more I think about this the more I think it’s a really bad idea to implement this in the current code. There is no encapsulation of any kind in the code, so you can be fairly certain this transact table is being accessed in multiple spots throughout the code. Just forgetting to change one spot can have disastrous consequences.

Unless the architecture of transactions is improved I would strongly recommend OP against doing this.

The fact that the software doesn’t seem to have any automated tests doesn’t help either.

Also, the OP has never answered the question as to why they want this. If we knew this we might be able to provide alternate solutions.

19

Thanks for your reply.

I look forward to additional guidance:

    $db = SELECT wallet FROM users WHERE user_id = '??';
    $walletBalance = $user->wallet;
    $newAmount= $walletBalance + $wallet;
    $query = UPDATE users SET walletBalance='$newAmount' WHERE user_id = '';
20

No! Don’t do it like that! When you do that you run into trouble if two transactions are done at the same time.

Suppose my wallet balance is 10 and the following happens at the exact same time:

  • User A adds 5 to my balance
  • User B adds 6 to my balance

So, first user A:

What is the current balance? 10
Okay, I’ll add 5, so the new balance is 15
Database, set balance to 15

Now user B:
What is the current balance? 10 (because user A has not written their new result yet!)
Okay, I’ll add 6, so the new balance is 15
Database, set balance to 16

So the balance should have been 10 + 5 + 6 = 21, but instead 5 got lost in hyperspace and the balance is only 16.

Like I said in my previous post, you must read up on how stuff like this works. Don’t just throw random code at it.

21

Some confusion in the column names there - is there a column called wallet and a column called walletbalance in your users table? If not, I’m not sure where $wallet comes from.

In any case, you can

newvalue = value of new transaction
update users set walletbalance = walletbalance + newvalue where user_id = ?

all in a single query. That probably reduces the chance of the scenario that @rpkamp talks about, but you should really tie it into a transaction, which will mean that if for some reason the wallet transaction isn’t stored, then the balance won’t be updated either.

22

Thanks for your reply.
No, there is no column called walletbalance.
Any additional help with this is appreciated:

		$db = SELECT wallet FROM users WHERE user_id = 'user_id';
		$wallet = $user->wallet;
		$newvalue = $wallet + wallet;
		$sql = "UPDATE users SET wallet = wallet + $newvalue where user_id = user_id";