Jump to content

MySQL update subtracting 2 instead of 1


SF23103
Go to solution Solved by ginerjm,

Recommended Posts

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

?>
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

  • Solution

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.

Link to comment
Share on other sites

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!

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.