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

[PNewCode]

Also, I'm using a test user account to try to transfer to, which has a balance of 0 tokens starting out. And I'm sending from my own account, that has 141 tokens

[phppup]

I like to test one item at a time. This let's you know where problems originate.

If the subtraction is working, then now get the addition to adhere.

I would think the two updates should be in the same loop, this way all criteria is the same.

[PNewCode]

@phppupexactly. Thats where I get stuck. So everything else is working except for the adding one to the other users tokens. Thats what lead me here.
"gname" is the name of the selection that is being sent from the first page. So I would assume that would act as the name of the person that is getting the tokens. It's a drop down list that has all the current users. One gets selected, then click the button, and then it sends to the page with the script I posted. 

So while the rest is working to subtract one from the user logged in, perhaps having one added to someone else isn't possible?

[phppup]

OOPS!

Just realized the code error I made.

I made $gname = 20 which is WRONG.

$gname is a user name, I meant to add an increase of value by 20 for easy verification.

Sorry.

[PNewCode]

Haha it's okay. So should it just be the

$gname = $row["fname"];

instead?

[phppup]

It's very possible, but this is a clumsy methodology. Mac_gyver has the right idea.

I'm also running out of free time.

Have you tried replacing user_updated_token with a hardcoded number (like 20) just to validate that addition will occur?

[PNewCode]

@phppup I just tried that edit with the script you posted to try and no errors, but nothing done at all either.

I understand you're out of free time. I very much appreciate the effort you gave to help. I would also add that I went back to @mac_gyverreply to review it but I'm just simple cross-eyed trying to understand it. 

[phppup]

If the second UPDATE is to ADD, then it CANNOT be $sql_1 ALSO because you already have a variable of $sql_1.

So, perhaps that should be variable $sql_add throughout the second UPDATE.

Revert to the code that was working with the subtraction and then work out the kinks for the addition.

 

Edited January 13, 2023 by phppup
Forgot item

[PNewCode]

So if I replicated the entire script that connects and replaced it with gname to retrieve from the previous page, but changed it to sql_2
then.... 

I think my brain just melted

[phppup]

Get back to the withing script.

Add the $gname association in the SAME loop and create $sql_2 to ADD a token.

Don't forget to also call $sql_2

Gotta go for now.

 

[PNewCode]

@phppup Thank you for all the time you spent helping me I appreciate it a lot

[mac_gyver]

the following is an example showing the code and queries needed to accomplish this task -

<?php

// initialization

// these settings should be in the php.ini on your system -
error_reporting(E_ALL);
ini_set('display_errors', '1');

// Start the session
session_start();

// fake a logged in user
$_SESSION['user_id'] = 1;

// insure the current visitor is logged in
if(!isset($_SESSION['user_id']))
{
	// handle a non-logged in user here...
	header('location:index.php');
	die; // stop code execution
}

// use exceptions for database statement errors and only handle the exception for user recoverable errors
// based on the sql syntax error posted, you ARE using exceptions for database statement errors. do yourself a favor and remove all the existing database error handling logic since it is no longer being used and is just cluttering up your code.
require 'pdo_connection.php';

$post = []; // array to hold a trimmed working copy of the form data
$errors = []; // array to hold user/validation errors

// post method form processing
if($_SERVER['REQUEST_METHOD'] === 'POST')
{
	// input(s) - gname, this should be the selected user's id - e.g. gid or similar
	
	// trim all the input data at once
	$post = array_map('trim',$_POST); // if any input is an array, use a recursive trim call-back function here instead of php's trim
	
	// validate the input data
	// the selected user's id
	if($post['gid'] === '')
	{
		$errors['gid'] = 'You must select a user';
	}
	else if($post['gid'] == $_SESSION['user_id'])
	{
		$errors['gid'] = 'You cannot select yourself';
	}
	else
	{
		// find if the selected user exists
		$sql = "SELECT COUNT(*) FROM users WHERE id=?";
		$stmt=$pdo->prepare($sql);
		$stmt->execute([ $post['gid'] ]);
		if(!$stmt->fetchColumn())
		{
			// if you see this error, it is either due to a programming mistake or someone submitting data values that are not from your code
			$errors['gid'] = 'The selected user does not exist';
		}
	}
	
	// if no errors, use the data
	if(empty($errors))
	{
		// since it is doubtful the OP will change what they are doing, the following updates the value in the token column

		// start transaction
		$pdo->beginTransaction();
		
		// subtract 1 token from the current user, if they have enough tokens
		$sql = "UPDATE users SET token=token-1 WHERE token > 0 AND id=?";
		$stmt=$pdo->prepare($sql);
		$stmt->execute([ $_SESSION['user_id'] ]);

		if($stmt->rowCount())
		{
			// the UPDATE query affected the row, token was > 0
			// add 1 token to the recipient user
			$sql = "UPDATE users SET token=token+1 WHERE id=?";
			$stmt=$pdo->prepare($sql);
			$stmt->execute([ $post['gid'] ]);
		}
		else
		{
			// the UPDATE query didn't affect the row, token is not > 0
			$errors['token'] = "You don't have enough tokens";
		}
		// if you are here, no database errors/exceptions occurred, commit the transaction
		$pdo->commit();
	}

	// if no errors, success
	if(empty($errors))
	{
		$_SESSION['success_message'] = "You have successfully gifted a token";
		die(header("Refresh:0"));		
	}
}

// get method business logic - get/produce data needed to display the page
$sql = "SELECT id, username FROM users ORDER BY username";
$stmt = $pdo->query($sql);
$all_user_data = $stmt->fetchAll();

// html document starts here...
?>

<?php
// test for, display, and clear any success message
if(!empty($_SESSION['success_message']))
{
	echo "<p>{$_SESSION['success_message']}</p>";
	unset($_SESSION['success_message']);
}
?>

<?php
// test for and display any errors
if(!empty($errors))
{
	echo '<p>'.implode('<br>',$errors).'</p>';
}
?>

<?php
// display the form, repopulating form fields using any data in $post
?>
<form method='post'>
<select name='gid'>
<option value=''>Select a user</option>
<option value='234'>An invalid user</option>
<?php
foreach($all_user_data as $row)
{
	$sel = isset($post['gid']) && $post['gid'] == $row['id'] ? 'selected' : '';
	echo "<option value='{$row['id']}' $sel>{$row['username']}</option>";
}
?>
</select><br>
<input type='submit'>
</form>

this code renamed the session variable, because many things have 'id's, what exactly is the id stored in the session?

uses the much simpler and more modern PDO database extension.

[PNewCode]

@mac_gyver Your example opened up a new thought for me. Perhaps my issue is that the page that is sending the "gname" is sending a user name, instead of the user id to add the token to?
In the drop down menu on the first page (I didn't put that pages script in this thread), I name the drop down menu "gname", which is a list of the users in the database.

The person gifting the token chooses the name and clicks "send"

Then it goes to the page that I posted originally.

So from your reply, that is making me think that my issue could be that I'm sending a name instead of an id.

Thank you very much for that example. Am I close in what I'm getting from it? 
Oh, and the id being stored in the session is the person that is logged in, and is giving the token to someone else

[PNewCode]

Here is the page that has the drop down and sends to the next page. This purpose is to start the session and collect the names to put in the drop down menu

 

<html>
<title> </title>




<?php

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





// Start the session
session_start();

  $hostname_l = "removed for posting this";
  $username_l = "removed for posting this";
  $password_l = ")jremoved for posting this";
  $dbname_l = "removed for posting 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";
}
?>









<?php  
	$server="removed for posting this";
	$userid ="removed for posting this";
	$Password = "removed for posting this";
	$myDB = "removed for posting this";





   $con = mysqli_connect($server,$userid,$Password,$myDB);

	if (mysqli_connect_errno()) {
		# code...
		 echo "Failed to connect to MySQL: " . mysqli_connect_error();
	}
 

?>
<header> 


<form action="insert.php" method="post" autocomplete="off">


	
<p align="center"> </p>
<p align="center"><font face="Verdana, Arial, Helvetica, sans-serif" color="#CCCCFF"><b><font color="#FFFF00">Send 
  A Playhouse User A Token<br>
  <i><font color="#CCCCCC">This Uses 1 Token<br>
  <br>
  </font></i></font></b></font></p>

<div align="center">



<style>


.button {
  background-color: #4CAF50; /* Green */
  border: none;
  color: white;
  padding: 8px;
  text-align: center;
  text-decoration: none;
  display: inline-block;
  font-size: 25px;
  margin: 4px 2px;
  cursor: pointer;
}

.button {border-radius: 25%;}
</style>


<style>
input[type="select"], option {

  background-color : #CCCCFF; 

}

input::placeholder{
  color: #660000;
}

input, select, textarea{
    color: #660000;
}

textarea:focus, input:focus {
    color: #660000;
}

::-webkit-input-placeholder { /* Edge */
  color: #660000;
}

:-ms-input-placeholder { /* Internet Explorer 10-11 */
  color: #660000;
}

::placeholder {
  color: #660000;
}
</style>




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




<style>
.box1 {
    background-color: rgba(0,0,0,.3);
    color: #fff;
}
</style>
    <font face="Verdana, Arial, Helvetica, sans-serif" size="3"><font color="#FFFFFF">

    <input type="hidden" name="fname" style="font-size:18pt;" value="<?php echo $fname; ?>" required />
    <input type="hidden" name="tokens" style="font-size:18pt;" value="1" required />
    </font></font> </div>


<div id="box" align="center"> 
  <div class="column" align="center"></div>
  <div class="column" align="center"> 
    <div class="clear" align="center"></div>
    <font face="Verdana, Arial, Helvetica, sans-serif"><label class="first-column ">Choose 
    A Playhouse User<br>
    </label></font> 
    <div class="second-column" align="center"> 
      <p align="center"> 
          <select name="gname">
            <option>Choose A Playhouse Member</option>
          <?php 

					$sqli = "SELECT * FROM users";
					$result = mysqli_query($con, $sqli);
					 while ($row = mysqli_fetch_array($result)) {
					 		# code...
					 	echo '<option>'.$row['fname'].'</option>';
					 }	
 

					?> 
        </select>
      </p>
      <p align="center">
        <input type="submit" name="submit2" class="button" value="Send token" />
      </p>
      <p align="center"><a href="main.php"><img src="cancel.png" width="190" height="55" border="0"></a></p>
    </div>
    <div class="clear" align="center"></div>
    <div class="clear" align="center"></div>
  </div>
  <div class="column" align="center"></div>
</div>

</form>

</body>
</html>

<?php mysqli_close($con); ?>

 

[Barand]

I'd go with a "token_transaction" table and store token accumulation/disposal in that instead of updating totals.

TABLE: token_transaction                                            transaction types
+----+---------+------------+---------------------+-----+           +---------------+---------------+
| id | user_id | trans_type | trans_time          | qty |           |  type         |   quantity    |
+----+---------+------------+---------------------+-----+           +---------------+---------------+
|  1 | 1       | earn       | 2023-01-03 15:25:20 |  10 |           |  earn         |    +ve        |
|  2 | 2       | earn       | 2023-01-03 15:25:20 |  10 |           |  receive      |    +ve        |
|  3 | 2       | donate     | 2023-01-04 10:15:50 |  -1 |           |  spend        |    -ve        |
|  4 | 3       | receive    | 2023-01-04 10:15:50 |   1 |           |  donate       |    -ve        |
|  5 | 1       | donate     | 2023-02-04 12:00:00 |  -1 |           |  cancel       |    -ve        |
|  6 | 4       | receive    | 2023-02-04 12:00:00 |   1 |           +---------------+---------------+
|  7 | 1       | donate     | 2023-01-14 14:49:29 |  -3 |
|  8 | 8       | receive    | 2023-01-14 14:49:29 |   3 |
|  9 | 8       | donate     | 2023-01-14 14:51:13 |  -2 |
| 10 | 1       | receive    | 2023-01-14 14:51:13 |   2 |
+----+---------+------------+---------------------+-----+

When a donation is made, two transactions are generated, both with identical timestamps.

• negative quantity for the donor
• positive quantity for the recipient

When you need to know how many tokens a user has, just SUM their quantities in the transactions. For example, given the transactions above

SELECT user_id
     , username
     , COALESCE(SUM(qty), 0) as tokens
FROM users u 
     LEFT JOIN
     token_transaction t USING (user_id)
GROUP BY username;

+---------+----------+--------+
| user_id | username | tokens |
+---------+----------+--------+
|       4 | blitzen  |      1 |
|       1 | comet    |      8 |
|       2 | cupid    |      9 |
|       5 | dancer   |      0 |
|       7 | dasher   |      0 |
|       3 | donner   |      1 |
|       6 | prancer  |      0 |
|       8 | vixen    |      1 |
+---------+----------+--------+

 

The donation code would then look like this

<?php
session_start();
include 'db_inc.php';                 //   USE YOUR OWN 
$pdo = pdoConnect();                  //   PDO CONNECTION CODE

#
# Ensure user is logged in
#
    if (!isset($_SESSION['user_id'])) {
        header("Location: login.php");
        exit;
    }

$errors = [];
    
#
# Get user's current token quantity
#
    $res = $pdo->prepare("SELECT sum(qty) as wealth
                          FROM token_transaction
                          WHERE user_id = ?
                         ");
    $res->execute([ $_SESSION['user_id']]);
    $wealth = $res->fetchColumn() ?? 0;
    
#
#  If data was POSTed, process it
#
    if ($_SERVER['REQUEST_METHOD'] == 'POST')  {
        
        $post = array_map('trim', $_POST);
        
        if ($post['recipient']=='') {
            $errors[] = 'You must specify a recipient';
        }
        if ($post['qty']==0) {
            $errors[] = 'You did not specify how many tokens';
        }
        elseif ($post['qty'] > $wealth) {
            $errors[] = 'You have insufficient tokens';
        }
        
        if (!$errors) {
            $now = date('Y-m-d H:i:s');
            $pdo->beginTransaction();
            try {
                $stmt = $pdo->prepare("INSERT INTO token_transaction
                                       (user_id, trans_type, trans_time, qty)
                                       VALUES (?, ?, '$now', ?)
                                      ");
                
                $stmt->execute( [ $_SESSION['user_id'], 'donate', -$post['qty'] ] );   // donor transaction
                $stmt->execute( [ $post['recipient'], 'receive', $post['qty'] ] );     // recipient transaction
                $pdo->commit();
            }
            catch(PDOException $e) {
                $pdo->rollBack();
                throw $e;
            }
            header("Refresh: 0");   // reload the page
            exit;
        }
    }

#
# FUNCTIONS
#
function userOptions(PDO $pdo, $current)
{
    $opts = "<option value=''>- select user -</option>\n";
    $res = $pdo->query("SELECT user_id
                             , username
                        FROM users
                        ORDER BY username     
                       ");
    foreach ($res as $r) {
        $sel = $r['user_id'] == $current ? 'selected' : '';
        $opts .= "<option $sel value='{$r['user_id']}'>{$r['username']}</option>\n";
    }
    return $opts;
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
<title>sample</title>
<meta charset="utf-8">
<script type='text/javascript'>

</script>
<style type='text/css'>
    body {
        font-family: arial;
    }
    header {
        background-color: #006EFC;
        color: white;
        padding: 16px;
    }
    #form1 {
        width: 500px;
        margin: 32px auto;
        padding: 16px;
        border: 1px solid gray;
    }
    #info {
        width: 500px;
        margin: 32px auto;
        padding: 16px;
        background-color: yellow;
    }
    label {
        display: inline-block;
        background-color: gray;
        color: white;
        padding: 8px;
        width: 120px
        
    }
 </style>
</head>
<body>
<header>
    <h1>Token Donation</h1>
</header>
<div id='info'>
    <?php
        if ($errors) {
            echo "<p>" . join('<br>', $errors) . "</p>\n";
        }
        else {
            echo "<p>You currently have <b>$wealth</b> tokens</p>\n";
        }
    ?>
</div>
<form method='POST' id='form1'>
    <label for='recipient'>Donate to</label>
    <select name='recipient' id='recipient'>
        <?= userOptions($pdo, $post['recipient']??'') ?>
    </select>
    <br><br>
    <label for='qty'>How many?</label>
    <input type='number' name='qty' id='qty' min='0' max='<?=$wealth?>' value='<?= $post['qty'] ?? 1 ?>'>
    <br><br>
    <input type='submit'>
</form>
</body>
</html>

 

[PNewCode]

@Barand Thank you for the input and advice. The problem with that is I can't alter the database and restructure it because there are many many pages that already use this database with the structure it has, and adding new tokens when they are purchased. The only issue I'm having is making it so a person can send a token that they already have, to a different user. Everything else already works. It even works to deduct one from the person sending it and sending the transaction details to the other databases. It's the pesky part of getting the token added to the other user that has me stumped