Php Mysql Remove from one row and add to another. I'm stuck

[PNewCode]

Hello. This is what I have working so far, and this works without a problem (see after for why I'm posting this)
Right now, this file will add information to 2 different databases (one of them serves as a backup, the other can be edited as needed)
This also removes one "token" from the user in the session.
Perfect! Thats what I need it to do.

HOWEVER, what I also need it to do, is add a token to the user defined by "gname" in this same transaction.

In the page that is sending the info to the below scripting, the person logged in (fname) selects a user to give a token to (gname)

Any thoughts from all of you amazingly smart people here, on what to add to this to make it work would be appreciated. I seem to hit a brick wall.

In case anyone wants to know "why" I'm doing this... here is the break down
A user is logged in. They decided they would like to give a different user one of their tokens (because they are just a nice person). So they send one to them. At the same time, this transaction is being sent to 2 more databases. One of the databases will be cleared out each day. The other one will store all the transactions as a way to look up the history of transactions. (it's the long way around, but I'm old and this is the easiest way for me to do this)

What I tried:
Besides the endless alterations from stuff I googled that didn't work, I also tried to duplicate the linke for -1 and change it to +1 but it didn't do anything (even when I changed it to gname)

 

<?php




error_reporting(E_ALL);
ini_set('display_errors', '1');





// Start the session
session_start();

$servername = "Deleted To Post This";
$database = "Deleted To Post This";
$username = "Deleted To Post This";
$password = "Deleted To Post This";

$servernameS = "Deleted To Post This";
$databaseS = "Deleted To Post This";
$usernameS = "Deleted To Post This";
$passwordS = "Deleted To Post This";



// Create connection
$conn = mysqli_connect($servername, $username, $password, $database);
$connS = mysqli_connect($servernameS, $usernameS, $passwordS, $databaseS);


  
// Check connection
if (!$conn) {
      die("Connection failed: " . mysqli_connect_error());
}

if (!$connS) {
      die("Connection failed: " . mysqli_connect_error());
}
 
echo " ";


    $fname1 = $_POST['fname'];
    $tokens1 = $_POST['tokens'];
    $gname1 = $_POST['gname'];
 

    $fname = mysqli_real_escape_string($conn , $fname1);
    $tokens = mysqli_real_escape_string($conn , $tokens1);
    $gname = mysqli_real_escape_string($conn , $gname1);


      $hostname_l = "Deleted To Post This";
  $username_l = "Deleted To Post This";
  $password_l = "Deleted To Post This";
  $dbname_l = "Deleted To Post This";
  
   $conn_l = mysqli_connect($hostname_l, $username_l, $password_l, $dbname_l);
  if(!$conn_l){
    echo "Database connection error".mysqli_connect_error();
  }
  $user_id = $_SESSION['id']; 
  $sql_l = "SELECT * FROM users WHERE id = '$user_id'";
  
  


$result = $conn_l->query($sql_l);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
 $user_token =  $row["token"];
 
 $fname =  $row["fname"];
  
  }
} else {
  echo "0 results";
}
 if($user_token >= 1){
     
 }else{
     
     echo "<h2 style='text-align:center;color:white'>You don't have enough tokens </h2><br><center><a href='moretokens.php'><img src='buytokens.png' border='0'></a><center>

<br><br><br><br><br><br><br><br><br><br>

<center><a href='main.php'><img src='imgs/cancel.png' border='0'></a><center>

";
     die();
 }


$sql = "INSERT INTO nametable (fname, tokens, gname) VALUES ('$fname', '$tokens', '$gname')";
if (mysqli_query($conn, $sql)) {
    
    
      
 
 
$user_updated_token = ($user_token) - 1;

  $sql_l = "UPDATE users SET token='$user_updated_token' WHERE id=$user_id";



if ($conn_l->query($sql_l) === TRUE) {
  echo "";
} else {
  echo "Error updating record: " . $conn_l->error;
}





} else {
      echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

if (mysqli_query($connS, $sql)) {
      echo " ";
} else {
      echo "Error: " . $sql . "<br>" . mysqli_error($connS);
}




$id = mysqli_insert_id($conn);








mysqli_close($conn);
mysqli_close($connS);
?>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<body link="#CCCCFF" vlink="#CCCCFF" alink="#CCCCFF">


<script>
document.addEventListener('contextmenu', event => event.preventDefault());
</script>


<style type="text/css">
body {background:none transparent;
}
</style>





<div align="center">
  <table width="100%" border="0" cellspacing="0" cellpadding="0">
    <tr> 
      <td valign="top" height="84" colspan="7"> 
        <div align="right"> 
          <p><br>
          </p>
        </div>
        <div align="center">
          <p>&nbsp;</p>
          <p>&nbsp;</p>
          <p><img src="../requests/imgs/completed.png" width="500" height="300"></p>
          <p><font face="Verdana, Arial, Helvetica, sans-serif" color="#FFFFFF">Now 
            Sending You Back To Token Gift Options<br>
            </font><font face="Verdana, Arial, Helvetica, sans-serif"><br>
            <br>
            </font></p>
        </div>
      </td>
    </tr>
  </table>
</div>

 

Edited January 13, 2023 by PNewCode

[benanamen]

This is really no different than what a banking app would do. Instead of money you have tokens. You are going to want to learn about Mysql Transactions.

https://www.mysqltutorial.org/mysql-transaction.aspx

[PNewCode]

@benanamenthank you for that reply and for the link to look over. I don't understand how to translate that into adding what I have to make it work though. 

[phppup]

If I'm following this, it seems that as long as the fname donor has > 0 tokens, you would UPDATE the row WHERE fname is found and decrease the number of tokens by 1.

Then, do the same for the gname to increase the token quantity by 1

Edited January 13, 2023 by phppup
Typos

[PNewCode]

@phppup exactly. I just can't seem to figure out how to do that. So far it successfully subtracts from the gifter. But I don't know how to get it to add to the giftee
If the gifter has 0, then it tells them they don't have enough tokens to give any away. That part works too so far

Edited January 13, 2023 by PNewCode

[mac_gyver]

50 minutes ago, PNewCode said:

the person logged in (fname) selects a user to give a token to (gname)

the logged in person is known from the $_SESSION['id'] variable, which your code should validate before using, i.e. this page requires a logged in user for it to do anything, and your code should test to make sure that the current visitor is logged in. the form should submit just the id of the selected recipient. any data that you store should use the user ids, not the name/username. this will result in the least about of storage and the fastest queries.

why are you making 3 different database connections? if these databases are on the same server, make one connection, then just specify the database name in the query.

you should be using exceptions for database statement errors and only catch and handle the exception for user recoverable errors, such as when inserting/updating duplicate or out or range user submitted data. in all other cases simply let php catch and handle the exception, where php will use its error related settings to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) you can then eliminate the existing error handling logic, since it will no linger get executed upon an error, simplifying the code. note: in the latest php versions, a mysqli connection error always throws and exception, so that the existing error handling logic you have won't ever be executed upon an error.

use a prepared query when supplying external, unknown, dynamic values to a query when it gets executed. this simplifies the sql query syntax, provides protection for all data types, not just strings, and if you switch to the much simpler and more modern PDO database extension, simplifies the php code.

don't copy variables to other variables for nothing, just use the original variables. this is just a waste of time typing.

you should NOT maintain a single number in a column to keep track of the number of tokens. this does not provide an audit trail that would let you detect if a programming mistake, accidental key press/button click, or nefarious activity cause the value to change. you should instead insert a row of data for each transaction that affects a value. to get the current amount, just sum() the quantity from all the rows of data belonging to the current user.

don't bother closing database connections in your code since php will automatically destroy all resources when your script ends.

your post method form processing code should -

1. detect if a post method from was submitted.
2. keep the form data as a set in an array variable, then reference elements in this array variable throughout the rest of the code.
3. trim all the input data at once. after you do item #2 on this list, you can accomplish this with one single line of code.
4. validate all inputs, storing user/validation errors in an array using the field name as the array index.
5. after the end of the validation logic, if there are no errors (the array will be empty), use the form data.
6. if after using the form data (which could result in more errors), if there are no errors, redirect to the exact same url of the current page to cause a get request for the page.
7. if you want to display a one-time success message, store it in a session variable, then test, display, and clear that session variable at the appropriate location in the html document.
8. if there are errors at step #5 or #6, the code will continue on to display the html document, where you would test and display any user/validation errors, and re-display the form, repopulating form fields with the submitted form data.
9. any dynamic value that you output in a html context should have htmlentities() applied to help prevent cross site scripting.

 

 

[PNewCode]

Another way to break it down is.. lets say...

Bob has 15 tokens
Bob uses this to give Sally 1 token
When it gets to this page that I have in the post, then 1 token is taken away from bob (fname) and one token is given to Sally (gname)
Now Bob (fname) has 14 token
And now Sally (gname) has 1 token

[phppup]

I only see ONE statement for UPDATE.

You need one for ADDING and ANOTHER to subtract.

[PNewCode]

@phppupcorrect. The one for subtract is working (which is what I have now)
I'm just missing something because when I replicate that to add one, I can't seem to get it to go to the "gname" (which isn't shown in my post because it didn't work. I wanted to only show what I have currently working)

[PNewCode]

@mac_gyver hey there. I put in the original post the reason for the different databases. I don't want access given to the one that holds the information over long periods of time, given to anyone but me. So the other one serves as a daily use by me and others. And like I said, I know it's the long way around but it's all I know how to do right now. I'm not as experienced as you. All that other stuff you posted, which I'm sure is logical and well said, is greek to me as I am only a few months into learning this. So the "why do you want to do this"... stuff doesn't help. I can't learn from that. But thank you very much I do appreciate the time you took to type that out for knowledge 

[phppup]

Does the row have a column for gname??

Or does gname = fname at that point?

After you get out working, I would suggest a review of @mac_gyver comments.

[PNewCode]

@phppup no there isn't a column name for gname
gname is the name of the form field that is being sent to this page that I posted.
So on the page that has the form, they select from a drop down list of current users. I named that drop down list "gname"

In the database, there is just fname (for the name) and tokens (for the amount of tokens they use)
of course there are other columns too but they don't matter for this project

[PNewCode]

Just something to add... this is only for use with a small group of 25-30 members. It's not for a public thing where I have to worry about too much mischief and stuff. It's just a small group of people that have some fun with some goofy games we play with each other (not even as good as games you buy or download lol)

[phppup]

Well, you cannot ADD to a column that does not exist, right?

So at some point, you need to tell the SQL statement that $gname = fname so that the correct for value is increased by 1.

Edited January 13, 2023 by phppup
Typos

[PNewCode]

@phppup okay I think I"m following you. So right now, it doesn't know where to put it because there's no column for it. So... would this work?
Or am I at least close? 

 

$user_updated_token = ($user_token) - 1;
  $sql_l = "UPDATE users SET token='$user_updated_token' WHERE id=$user_id";

$gname =  $row["fname"];
$user_updated_token = ($user_token) + 1;
  $sql_l = "UPDATE users SET token='$user_updated_token' WHERE id=$gname";

 

[phppup]

It seems to me that you may want to restructure your process (especially if your learning) because it's easier to learn to code correctly than to learn bad habits.

And it's easier to find and resolve issues on smaller projects than large.

What @mac_gyver is providing you may be advanced information, but it is accurate.

If a user of your small group tells you that their balance is incorrect, you currently have no recourse but to shrug your shoulders and respond that your code is full-proof (not very reassuring to a disgruntled user).

Mac's method would offer an additional table for transactions that would allow you to say, " You had this many tokens on DATE and you received X many on these dates from user 1, use 2, etc. You also gave to these people on these dates, and this is my evidence to support the inquiry."

More advanced, but much more useful.

[phppup]

Looks like it would.

Why not give it a shot?

[PNewCode]

@phppup you're correct, though I have that in place already. When they use a token right now (to play a game) then that transaction with a balance is already in the database under a different table. It also shows what the balance was before and after each transaction. I also have it set up already where I can email them that information

[phppup]

Did you get it working?

(If it's good please mark the solution)

PS: you may want to review and consolidate your tables to eliminate redundancy.  Streamlining is good.

[PNewCode]

@phppup Nope it didn't work. I got this

Warning: Trying to access array offset on value of type null in insert.php on line 113

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 in insert.php:119 Stack trace: #0 insert.php(119): mysqli->query('UPDATE users SE...') #1 {main} thrown in transfer/insert.php on line 119

Which tells me that... purple? haha

[phppup]

I guess the first question is what exact code you added and where to trigger these errors.

You are getting no errors prior to these added lines?

[PNewCode]

Right. Before adding this, there were no errors. Here is the exact code I just added
 

$gname =  $row["fname"];
$user_updated_token = ($user_token) + 1;
  $sql_l = "UPDATE users SET token='$user_updated_token' WHERE id=$gname";

And line 119 is

if ($conn_l->query($sql_l) === TRUE) {

And the cluster around that with what I added is
 

$user_updated_token = ($user_token) - 1;

  $sql_l = "UPDATE users SET token='$user_updated_token' WHERE id=$user_id";




$gname =  $row["fname"];
$user_updated_token = ($user_token) + 1;
  $sql_l = "UPDATE users SET token='$user_updated_token' WHERE id=$gname";



if ($conn_l->query($sql_l) === TRUE) {
  echo "";
} else {
  echo "Error updating record: " . $conn_l->error;
}

 

[PNewCode]

I should add, that those other 2 databases that the info is sending to, is also a record of these transactions too

[phppup]

I'd try this for starters:

////$gname =  $row["fname"];

$gname = 20; //this will give you an easy way to spot the added value and see that it increases 

$user_updated_token = ($user_token) + 1;
  $sql_l = "UPDATE users SET token='$user_updated_token' WHERE id=$gname";

I don't use PDO, some in not sure about 

if ($conn_l->query($sql_l) === TRUE)

//would == be more accurate than ===

And are you repeating this query? You already have it once, don't you?

[PNewCode]

@phppup that got rid of the errors. But there was no function done for the tokens. None subtracted or added anywhere. If I remove that update completely then it will subtract one still though. So the issue is still how to make that happen and also add one to the other one. Head scratcher for certain.

I'm not sure what PDO is to be honest. I just grabbed that bit of code from another page that was working and stuck it in there. Seemed to do the trick for making the rest work so far.