SF23103 Posted December 21, 2015 Share Posted December 21, 2015 Hello all, I am having an issue with updating my database, and subtracting $seats_taken from a number in my database available_seats. When I run this script below, it subtracts 2 from available_seats instead of 1. Any ideas why it's doing that? <?php $seats_taken = "1"; // using this as an example $class_name = "Math"; // using this as an example if (isset($class_name) && $seats_taken > 0 && $seats_taken <= 11) { include "/path/to/login.php"; $db = new mysqli('localhost', $username, $password, $database); // Connect to DB using required login info if($db->connect_errno > 0) { die('Unable to connect to database [' . $db->connect_error . ']'); } unset($username);// put these variables back to null unset($password);// put these variables back to null unset($database);// put these variables back to null $sql = "UPDATE form_data SET available_seats = available_seats - $seats_taken WHERE class_name = '$class_name'"; $result = $db->query($sql); if(!$result = $db->query($sql)){ // if there is an error in running the query, show error message. die('There was an error running the query [' . $db->error . ']'); } $db->close(); } else echo "There was a problem updating the available seats."; ?> Quote Link to comment https://forums.phpfreaks.com/topic/299941-mysql-update-subtracting-2-instead-of-1/ Share on other sites More sharing options...
mac_gyver Posted December 21, 2015 Share Posted December 21, 2015 this is a reply from the end of your last thread - browsers have a habit of requesting pages twice, for several different reasons. you could also have have an error in some of your client side code (submitting a form via ajax and not stopping the browser from submitting it as well) or server side code that's causing it. in any case, you should not be modifying a count in a database column to track quantities of things. you should add a record to a database table for each 'transaction' that adds or subtracts a quantity, like what your bank or credit card company does. this record would would have columns for who (a user_id) caused the change in the quantity, the item_id, the quantity - a positive or negative value, a datatime when the records was inserted, and a status/memo column to record the type of transaction or a comment about the transaction. the initial quantity on hand would also be entered by inserting a row in the table. to get the quantity at any point in time, you would just SUM() the quantity column value for any item_id. your php code is running the query twice. if you don't see the lines in your code that are doing that, i don't think you are looking at your code. Quote Link to comment https://forums.phpfreaks.com/topic/299941-mysql-update-subtracting-2-instead-of-1/#findComment-1528411 Share on other sites More sharing options...
Solution ginerjm Posted December 21, 2015 Solution Share Posted December 21, 2015 You probably don't realize that your first call to query() runs the query and so does your second. The if statement is a wise thing to do to evaluate if your query runs, but don't run it the first time because the if is going to run it again. Dump the first call. And of course, as Mac_gyver says, one doesn't maintain a total in a database. One designs it to record the details and than obtains a count/total/sum when on needs it by doing a quick summary query. Does your system register the occupants of the seats in each class? If so, then you already have your answer for total seats used. Quote Link to comment https://forums.phpfreaks.com/topic/299941-mysql-update-subtracting-2-instead-of-1/#findComment-1528412 Share on other sites More sharing options...
SF23103 Posted December 21, 2015 Author Share Posted December 21, 2015 Thank you. Sorry, I stopped following the last thread after it was solved. Your explanation as to not maintaining a total in the database makes sense. Unfortunately, I am trying to add a small functionality to a much larger already-designed database. At some point I will have to re-design it if I am going to add any more functionality. I also see where it's running the query twice. Thank you! Quote Link to comment https://forums.phpfreaks.com/topic/299941-mysql-update-subtracting-2-instead-of-1/#findComment-1528415 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.