Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm having problem with inserting in purchase table and updating facility table. Let's say, user made a purchase with product_id and product_quantity.

When user hit submit, I want to insert product_id and product_quantity into purchase table. At the same time, updating facility table with product_id and product_quantity that associated with it.

Here is my code

<?php
include 'dbconn.inc.php';
include 'functions.inc.php';

$sql1 = "SELECT * FROM facilities";

$res = $mysqli->query($sql1);
$facilities = array();

while( $row = $res->fetch_array(MYSQLI_ASSOC) ){

$facilities[]['id'] = $facilities_id;
$facilities[]['product_id'] = $facilities_product_id;
$facilities[]['product_current_quantity'] = $product_current_quantity;
}


$id = $mysqli->real_escape_string ($_POST['id']);
$purchase_id = $mysqli->real_escape_string( $_POST['purchase_id'] );
$facility_id = $mysqli->real_escape_string( $_POST['facility_id'] );
$product_quantity = $mysqli->real_escape_string( $_POST['product_quantity'] );



$sql1 = "UPDATE facilities
    SET

`product_current_quantity` = '$product_quantity + $product_current_quantity'


    WHERE $facility_id = $facilities_id AND $id = $facilities_product_id ";


$sql = "INSERT INTO purchases
( 
 `purchase_id`,
 `facility_id`, 
 `product_quantity`,,
 `product_id`
 )
VALUES
(

 '$purchase_id',
 '$facility_id',
 '$product_quantity',
 '$id'

 )";

$ok = $mysqli->query( $sql );

$ok1 = $mysqli->query( $sql );


if( $ok && $ok1){
redirect( 'new_purchase.php' );
} else {
echo 'Couldn\'t add new purchase';
}

I did some research and I think I need to use triggers. But I never work with triggers before. Any helps would be great. Thank you!

share|improve this question

2 Answers 2

Yes, a trigger could be a good approach, but first, you should look more closely at your code:

$ok = $mysqli->query( $sql );
$ok1 = $mysqli->query( $sql );

It seems fairly clear that the second line should be executing the $sql1 query, not re-executing the $sql query.

Your design also seems less than optimal, for a couple of reasons:

  • you're storing the same data in two different places in your database... the facilities table contains totals of product quantity, and that same information could be found by summing up the values found in the purchases table; information stored in more than one place is ripe for inconsistencies to creep in.

  • if changing the above isn't practical, consider also that you're fetching $product_current_quantity from the facilities table, then letting your application add that value to the value in the $product_quantity and then you're stuffing that value back into the facilities table... this practice will also cause problems for you soon, if it hasn't already.

What you should be doing in this case is letting the database update the values in place:

UPDATE facilities
   SET product_current_quantity = product_current_quantity + $product_quantity
 WHERE ...                       # ^^^^^ use the column, not a variable

The big problem here is one of improper handling of concurrency. Assume two concurrent accesses involving the same facility/product:

Start with product_current_quantity of, for example, 5. Request #1 will add 3 to the quantity and Request #2 will add 2 to the quantity... and these two requests happen at almost exactly the same time. The total should now be 5 + 3 + 2 => 10, right? But...

request #1 reads product_current_quantity = 5
request #2 reads product_current_quantity = 5 (#1's update hasn't happened yet)
request #1 adds its local "5" + 3 and writes "8"
request #2 has already read "5" also, so it writes 5 + 2 => "7"
database value is now "7" instead of 10.

Letting the database do the math, and not your application, avoids this, because even if the two updates arrive at exactly the same time, each one will atomically modify the column's value, and the final result will be correct.

Additionally, this seems like a very appropriate place to be using transactions, because everything done inside a single transaction stands (COMMIT) or falls (ROLLBACK) together.

And, yes, a trigger would work, too:

# change the statement delimiter from semicolon to another symbol, such as "$$"
DELIMITER $$
DROP TRIGGER IF EXISTS purchases_ai $$
CREATE TRIGGER purchases_ai AFTER INSERT ON purchases FOR EACH ROW BEGIN

UPDATE facilities
   SET product_current_quantity = product_current_quantity + NEW.product_quantity
 WHERE facility_id = NEW.facility_id 
   AND id = NEW.product_id;

END $$
# change the delimiter back
DELIMITER ;

In a trigger, the NEW and OLD aliases refer to the row in the table before and after the query that was just executed. NEW applies only to INSERT and UPDATE triggers, and OLD applies only to UPDATE AND DELETE triggers. Triggers can fire either BEFORE or AFTER the affected row is actually changed. In the example above, this trigger fires AFTER INSERT -- immediately after the insert of the record into purchases.

share|improve this answer
    
Thank you so much for replying. How can I let the database do the math for me? I just learned about MySQL and php less than 2 weeks. I have a lot more to learn. –  user1754822 May 4 '13 at 5:59
    
That is explained in the answer -- but I'll rephrase it a bit to make it less situation-specific: you can reference the current value of a column on the right-hand side of an assignment "=" equals operator in an update query, so, for example, in a table called payroll with a column called salary you can simply UPDATE payroll SET salary = salary + 5000 WHERE employee = 'sqlbot' to give me a pay increase. –  Michael - sqlbot May 4 '13 at 14:22
$data = array(
               'title' => $title,
               'name' => $name,
               'date' => $date
            );

$this->db->where('id', $id);
$this->db->update('mytable', $data); 
share|improve this answer
    
Hi @Chandra - welcome to Stack Exchange! Instead of providing only code, you should attempt to explain what this code does, or how it answers the question. Providing code-only answers won't likely help your future readers. –  Max Vernon Jul 11 at 13:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.