Jump to content

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


PNewCode

Recommended Posts

@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?

Link to comment
Share on other sites

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 by phppup
Forgot item
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

@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

Link to comment
Share on other sites

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); ?>

 

Link to comment
Share on other sites

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>

 

Link to comment
Share on other sites

@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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.