Jump to content

MySQL Updating


Faracus
 Share

Recommended Posts

how do I update a MySQL table where your subtracting, and adding on to a value already in the Database.To make what i'm doing clearer, i'm building a virtual bank for a game, and I need to subtract money from one account and then credit it to another account, making sure that the one it is being taken from does not fall below $0, and I just dont see how I could do that.So any help is greatly wanted. Thanks in advance.

Link to comment
Share on other sites

ok this is what I have come up, i'm pretty new at trying to do more than one thing ona page, and I would spread it over a number of pages, but I dont know how to carry the $_POST data over multiple pages, and I dont know how to forward through pages.

<?php$con = mysql_connect("localhost","user","password");if (!$con)  {  die('Could not connect: ' . mysql_error());  }    mysql_select_db("shackguys", $con);$query = "SELECT balance FROM bank WHERE accnumber = $_POST['to']";// Perform Query$result = mysql_query($query);// Check result// This shows the actual query sent to MySQL, and the error. Useful for debugging.if (!$result) {$message = 'Invalid query: ' . mysql_error() . "\n";$message .= 'Whole query: ' . $query;die($message);}while($row = mysql_fetch_array($result))  {if ($row['balance']>"$_POST['ammount']")  echo "$new_ammount = '$row['balance']-$_POST['ammount']'mysql_select_db('shackguys', $con);mysql_query('UPDATE bank SET balance = '$new_ammount'WHERE accnumber = '$_POST['from']')";else  echo "There is not enough money to cover the transfer from the account. Please Try again.";     }?>

Link to comment
Share on other sites

There were several syntax errors. With this:

if ($row['balance']>"$_POST['ammount']")  echo "$new_ammount = '$row['balance']-$_POST['ammount']'

First, you don't need quotes around the $_POST variable. You don't need quotes around any variable. Second, the next line of code is an echo statement. You're not trying to print that line of code, you're trying to execute it. So I removed the echo statement and the quotes around variable names.You don't need to select a database more then once on a page, or connect more then once. This query:

mysql_query('UPDATE bank SET balance = '$new_ammount'WHERE accnumber = '$_POST['from']')";

Has the closing paren in the wrong place (it is inside the quote, it should be outside), and you start it with a single quote and end it with a double quote. That's not going to work. I changed that to use double quotes and fixed the paren at the end. I also changed the while loop into an if statement, you don't need to loop through, you just need one result. If there aren't any results then you can display a message saying that. I also used the intval function to convert some of the post values to numbers to make sure that people aren't trying to write SQL code in the form and screw up the database. I also changed mysql_fetch_array to mysql_fetch_assoc, you're trying to use an associative array, not an indexed array.You were also missing some brackets around an if statement when you do the update query. You also only update one account, you remove money from one account but don't add it to the other one.You'll also want to do some more validation, like make sure that the person who is logged in actually owns the account that is being debited, or else people could just use this form to transfer money from everyone else's account into their own.

<?php$con = mysql_connect("localhost","user","password");if (!$con){  die('Could not connect: ' . mysql_error());}mysql_select_db("shackguys", $con);$acct_no = intval($_POST['to'])$query = "SELECT balance FROM bank WHERE accnumber = " . $acct_no;// Perform Query$result = mysql_query($query);// Check result// This shows the actual query sent to MySQL, and the error. Useful for debugging.if (!$result) {  $message = 'Invalid query: ' . mysql_error() . "\n";  $message .= 'Whole query: ' . $query;  die($message);}if($row = mysql_fetch_assoc($result)){  if ($row['balance'] >= $_POST['ammount'])  {	$new_ammount = $row['balance'] - $_POST['ammount'];	mysql_query("UPDATE bank SET balance = '{$new_ammount}' WHERE accnumber = " . intval($_POST['from']);  }  else	echo "There is not enough money to cover the transfer from the account. Please Try again.";}else  echo "Account {$acct_no} not found";?>

Link to comment
Share on other sites

I put the code in to the website, but when I try and test it it's not working. And also, i realised that i'v only been deducting the money, and not actually putting it in to the other account, then I still have to make sure that it log's the transaction. All of which i thought I could have done, but obviously not.Is SQL the right app to use for this kind of work, or should I be doing it in something else?

Link to comment
Share on other sites

SQL isn't an application, it's a language used to communicate with databases. A database is the best thing to use for storing your data in this situation.

I put the code in to the website, but when I try and test it it's not working.
Why not? Make sure error messages are enabled. Add this to the top:ini_set('display_errors', 1);error_reporting(E_ALL);Also, make sure you have the most recent code. I've edited that last post several times, you may have an older version.
Link to comment
Share on other sites

Make sure those lines are on the top, they should be before any other lines of code. It doesn't make sense that you would see a blank page with nothing changing in the database. Assuming the value to transfer is non-zero, it should either update the database or show a message. You can add code to output a message if it does update as well, and it would be a good idea to remove the quotes around the balance value in the SQL query, I assume that value is a number and not a string.

if($row = mysql_fetch_assoc($result)){  if ($row['balance'] >= $_POST['ammount'])  {	$new_ammount = $row['balance'] - $_POST['ammount'];	mysql_query("UPDATE bank SET balance = {$new_ammount} WHERE accnumber = " . intval($_POST['from']);	echo "database updated, new balance is {$new_ammount} for account {$_POST['from']}";  }  else	echo "There is not enough money to cover the transfer from the account. Please Try again.";}

Also, just as an aside, it is spelled "amount".

Link to comment
Share on other sites

Now it definately shouldn't be blank, there should be an echo statement no matter what happens. You can add some more echo statements in to see what it's doing, but it should never be blank.

<?phpini_set("display_errors", 1);error_reporting(E_ALL);echo "starting<br>";$con = mysql_connect("localhost","user","password");if (!$con){  die('Could not connect: ' . mysql_error());}mysql_select_db("shackguys", $con);$acct_no = intval($_POST['to'])echo "account number to debit is {$acct_no}<br>";$query = "SELECT balance FROM bank WHERE accnumber = " . $acct_no;// Perform Query$result = mysql_query($query);// Check result// This shows the actual query sent to MySQL, and the error. Useful for debugging.if (!$result) {  $message = 'Invalid query: ' . mysql_error() . "\n";  $message .= 'Whole query: ' . $query;  die($message);}if($row = mysql_fetch_assoc($result)){  echo "found the account in the database, balance is {$row['balance']}<br>";  if ($row['balance'] >= $_POST['ammount'])  {	$new_ammount = $row['balance'] - $_POST['ammount'];	mysql_query("UPDATE bank SET balance = {$new_ammount} WHERE accnumber = " . intval($_POST['from']);	echo "database updated, new balance is {$new_ammount} for account {$_POST['from']}";  }  else	echo "There is not enough money to cover the transfer from the account. Please Try again.";}else  echo "Account {$acct_no} not found";?>

Link to comment
Share on other sites

I am running it from the server, and PHP is working because everything else is working, and i'v checked to make sure that it's going to the right file.Here is a copy of the HTML form:

<html><head><title>Bank Tools</title></head><body>In this section you can either transfer money from your account to another account, or take a payment from another account. Please note, that all transactions are logged, and any illegal activity's will lead to banned account's. Do not take other's money without permission.<br><h1>Transfering Money To Another Account</h1>Here you can transfer money from your account to another account.<br><form action="xferfrom.php" method="POST">Your Account Number: <input type="text" name="from" /><br>Your PIN: <input type="password" name="pin" /><br>Their Account Number: <input type="text" name="to" /><br>Ammount: <input type="text" name="ammount" /><br><input type="submit" /></form><br><br><h1>Transfering Money To Your Account</h1>Here you can take money from another person's account for payments of services.<br><form action="xferto.php" method="post">Your Account Number:<input type="text" name="to" /><br>Your PIN: <input type="password" name="pin" /><br>Their Account Number: <input type="text" name="from" /><br>Ammount: <input type="text" name="ammount" /><br><input type="submit" /></form><br><br><a href="http://shackguys.servegame.org/relax/">Relax Home</a>

and in both xferfrom.php and xferto.php I am running the same code, but it should do the same as I have just switched the to and from values.If you want to check out the page, it's http://shackguys.servegame.org/relax/bank/tools/ and i'm using:acc number: 1011112354 pin: 1111 username: test1acc number: 1011112355 pin: 2222 username: test2both accounts have $1,000You can see it is working by going to http://shackguys.servegame.org/relax/bank/login.html

Link to comment
Share on other sites

I don't know what to tell you. Try removing the first two lines that enable errors, but the very first line after that is an echo statement. Nothing should stop that from being printed. If it's not being printed then I'm not sure what to tell you, that doesn't make sense. View the source code of the blank page to see if there's anything there.When I try to use the page you sent I get a file not found error. Are you *sure* you're uploading the right file?

Link to comment
Share on other sites

well I just went and checked the server error log and i should have done this sooner I guess, but this is what's been showing up.[Thu Feb 28 12:13:57 2008] [error] [client 207.161.192.227] PHP Parse error: syntax error, unexpected T_ECHO in C:\\website\\htdocs\\Relax\\bank\\Tools\\xferto.php on line 17, referer: http://shackguys.servegame.org/relax/bank/tools/edit: also the source code is blankedit2: line 17 is: echo "account number to debit is {$acct_no}<br>";edit3: isn't an line break supposed to be <br />? thats what i'd had to use for my other pages

Link to comment
Share on other sites

ok, added the semicolon, but it's still coming up as blank, but there is a new error[Thu Feb 28 12:32:08 2008] [error] [client 207.161.192.227] PHP Parse error: syntax error, unexpected ';' in C:\\website\\htdocs\\Relax\\bank\\Tools\\to.php on line 34, referer: http://shackguys.servegame.org/relax/bank/tools/and line 34 is: mysql_query("UPDATE bank SET balance = {$new_ammount} WHERE accnumber = " . intval($_POST['from']);so I removed the semicolon and then it comes up with the error:[Thu Feb 28 12:33:25 2008] [error] [client 207.161.192.227] PHP Parse error: syntax error, unexpected T_ECHO in C:\\website\\htdocs\\Relax\\bank\\Tools\\to.php on line 35, referer: http://shackguys.servegame.org/relax/bank/tools/with line 35 being: echo "database updated, new balance is {$new_ammount} for account {$_POST['from']}";

Link to comment
Share on other sites

ok, sucess. not a blank screen and the money was deducted, now to just credit it to the other account then leave a log. Is there a way for it to post a log of the transfer to a external file that it just keep's adding to the top of?edit: i have manage to get it to credit the other account, now to find out how to make a log of the transaction.edit2: ok I thought I managed to get it to credit the other account properly, but......not so.edit3: I have the crediting of the other account working now, but it's not that stable. If there is not enough money in the one account, it still credit's the other account, and also it's touchy, here is the code I have.

<?php$con = mysql_connect("localhost","****","****");if (!$con){  die('Could not connect: ' . mysql_error());}mysql_select_db("shackguys", $con);$acct_no = intval($_POST['to']);echo "Account number to debit is {$acct_no}<br>";$query = "SELECT balance FROM bank WHERE accnumber = " . $acct_no;// Perform Query$result = mysql_query($query);// Check result// This shows the actual query sent to MySQL, and the error. Useful for debugging.if (!$result) {  $message = 'Invalid query: ' . mysql_error() . "\n";  $message .= 'Whole query: ' . $query;  die($message);}if($row = mysql_fetch_assoc($result)){  echo "Account Found.<br>";  if ($row['balance'] >= $_POST['ammount'])  {    $new_ammoun = $row['balance'] - $_POST['ammount'];    mysql_query("UPDATE bank SET balance = {$new_ammount} WHERE accnumber = " . intval($_POST['from']));    echo "Money Has Been Deducted";  }  else    echo "There is not enough money to cover the transfer from the account. Please Try again.";}else  echo "Account {$acct_no} not found";$con = mysql_connect("localhost","****","****");if (!$con){  die('Could not connect: ' . mysql_error());}mysql_select_db("shackguys", $con);$acct_no = intval($_POST['from']);echo "Account number to credit is {$acct_no}<br>";$query = "SELECT balance FROM bank WHERE accnumber = " . $acct_no;// Perform Query$result = mysql_query($query);// Check result// This shows the actual query sent to MySQL, and the error. Useful for debugging.if (!$result) {  $message = 'Invalid query: ' . mysql_error() . "\n";  $message .= 'Whole query: ' . $query;  die($message);}if($row = mysql_fetch_assoc($result)){  echo "Account Found.<br>";    $new_ammount = $row['balance'] + $_POST['ammount'];    mysql_query("UPDATE bank SET balance = {$new_ammount} WHERE accnumber = " . intval($_POST['to']));    echo "Money Has Been Credited";  }else  echo "Account {$acct_no} not found";  ?>

Link to comment
Share on other sites

Ok, i have a slightly different approach, how do I carry data over from one page to another, i'm guessing I use cookies and session's, but I don't know how I would use them.Like I am thinking I could prepare the data on a confirmation page, it can also get the balances from both accounts, and then when they confirm that the info is right it goes to the next page and then it updates the account, this saves having to split up the code like I have done, which then results in error's like I am getting, insted it just does an update using the values from the previous page, it makes it easier.

Link to comment
Share on other sites

You just copied and pasted everything twice, that's not the right way to do it. Also, there's no point in connecting to the same database twice on one page, you only need to connect once.

<?php$con = mysql_connect("localhost","****","****");if (!$con){  die('Could not connect: ' . mysql_error());}mysql_select_db("shackguys", $con);$acct_to = intval($_POST['to']);$acct_from = intval($_POST['from']);$amount = floatval($_POST['ammount']);echo "Account number to credit is {$acct_to}<br>";echo "Account number to debit is {$acct_from}<br>";$query = "SELECT balance FROM bank WHERE accnumber = " . $acct_from;// Perform Query$result = mysql_query($query);// Check result// This shows the actual query sent to MySQL, and the error. Useful for debugging.if (!$result) {  $message = 'Invalid query: ' . mysql_error() . "\n";  $message .= 'Whole query: ' . $query;  die($message);}if($row = mysql_fetch_assoc($result)){  echo "Debit Account Found.<br>";  $from_balance = $row['balance'];  $result = mysql_query("SELECT balance FROM bank WHERE accnumber = " . $acct_to);  if ($row = mysql_fetch_assoc($result))  {	echo "Credit Account Found.<br>";	if ($from_balance >= $amount)	{	  mysql_query("UPDATE bank SET balance = balance - {$amount} WHERE accnumber = {$acct_from}");	  mysql_query("UPDATE bank SET balance = balance + {$amount} WHERE accnumber = {$acct_to}");	  echo "Money Has Been Deducted";	}	else	  echo "There is not enough money to cover the transfer from the account. Please Try again.";  }  else	echo "Account {$acct_to} not found";}else  echo "Account {$acct_from} not found";?>

You can use this function to write to a file:http://www.php.net/manual/en/function.file-put-contents.phpThere is a flag called FILE_APPEND that you can use to append text instead of overwriting the file. It will also be best to use the LOCK_EX flag, which will require that you're using at least PHP 5.1. Otherwise you could miss transactions.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...