Faracus Posted February 27, 2008 Share Posted February 27, 2008 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 More sharing options...
justsomeguy Posted February 27, 2008 Share Posted February 27, 2008 Don't do everything in SQL, get the current balance and check it to make sure it's not going to go negative. Then you can use addition and subtraction in a SQL statement to do the update. Link to comment Share on other sites More sharing options...
Faracus Posted February 27, 2008 Author Share Posted February 27, 2008 ok, i'll see what I can do. Link to comment Share on other sites More sharing options...
Faracus Posted February 27, 2008 Author Share Posted February 27, 2008 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 More sharing options...
justsomeguy Posted February 27, 2008 Share Posted February 27, 2008 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 More sharing options...
Faracus Posted February 27, 2008 Author Share Posted February 27, 2008 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 More sharing options...
justsomeguy Posted February 27, 2008 Share Posted February 27, 2008 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 More sharing options...
Faracus Posted February 27, 2008 Author Share Posted February 27, 2008 i made sure I had the most recent code, and I added those two lines, but it is still just coming up with a blank screen, and when I check the database nothing has changed. Link to comment Share on other sites More sharing options...
justsomeguy Posted February 27, 2008 Share Posted February 27, 2008 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 More sharing options...
Faracus Posted February 27, 2008 Author Share Posted February 27, 2008 lol thanks, well I added that new bit of code, and it is still coming up blank :S. Link to comment Share on other sites More sharing options...
justsomeguy Posted February 27, 2008 Share Posted February 27, 2008 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 More sharing options...
Faracus Posted February 28, 2008 Author Share Posted February 28, 2008 there must be something wrong, as I am still getting a blank page. Link to comment Share on other sites More sharing options...
justsomeguy Posted February 28, 2008 Share Posted February 28, 2008 There's definately something wrong. Either you're not running the right file, you're not running it correctly, or PHP isn't installed on the server. Keep in mind you need to run this from a server, you can't just double-click on it. Link to comment Share on other sites More sharing options...
Faracus Posted February 28, 2008 Author Share Posted February 28, 2008 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 More sharing options...
justsomeguy Posted February 28, 2008 Share Posted February 28, 2008 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 More sharing options...
Faracus Posted February 28, 2008 Author Share Posted February 28, 2008 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 More sharing options...
justsomeguy Posted February 28, 2008 Share Posted February 28, 2008 There needs to be a semicolon after this line:$acct_no = intval($_POST['to']) edit3: isn't an line break supposed to be <br />? thats what i'd had to use for my other pagesOnly if it's XHTML, but your pages aren't using XHTML. Link to comment Share on other sites More sharing options...
Faracus Posted February 28, 2008 Author Share Posted February 28, 2008 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 More sharing options...
justsomeguy Posted February 28, 2008 Share Posted February 28, 2008 mysql_query("UPDATE bank SET balance = {$new_ammount} WHERE accnumber = " . intval($_POST['from']);Add another paren before the semicolon, I left one out. And leave the semicolon there, you need a semicolon at the end of any statement. Link to comment Share on other sites More sharing options...
Faracus Posted February 29, 2008 Author Share Posted February 29, 2008 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 More sharing options...
Faracus Posted March 2, 2008 Author Share Posted March 2, 2008 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 More sharing options...
justsomeguy Posted March 4, 2008 Share Posted March 4, 2008 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 More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.