MySQL update subtracting 2 instead of 1

[SF23103]

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.";

?>

[mac_gyver]

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.

[ginerjm]

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.

[SF23103]

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!