• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to update the stock after every purchase ?problems

#1
[eluser]Keloo[/eluser]
Hi, I'm currently working on a shopping site using CI.
What I'm trying to achieve at the moment is that after each purchase of an item stored in the DB the stock should be dynamically updated.
For instance, every product has a limited stock of n items, and each time someone purchase an item from that stock, the stock should grow less.
Let's say I have in my stock 100 apples, and someone buys 10, so the stock will be 90, and so on, until it will be empty.

I was thinking of something like this:

Code:
function update_stock() {
        $results = $this->db->get('products')->result();
        
        foreach($results as $row){
            for($i=$row->stock; $i >= 0; $i--){
                
                $data = array(
                    'stock' => $i
                );
                
                $this->db->update('products',$data);
            }
        }
    }
But it's not working properly because after a purchase the stock it's updated to 0.
I hope I made myself clear enough. I hope I can get a little help with this one.

#2
[eluser]louisl[/eluser]
You don't seem to be selecting your product by ID.

Get the remaining stock of a single product by ProductID then take your sold quantity from it and update again it using the ProductID.

Something like this.

Code:
$QuantitySold = 10;
$ProductID = 1; //or whatever

$sql = 'SELECT '
        'Stock '
            .'FROM Products '
                .'WHERE '
        .'ProductID = ?';

$params[] = $ProductID;

$query = $this->db->query($sql, $params);

if ($query->num_rows() > 0) {
    
    $row = $query->row_array();
    
    $Stock = $row['Stock'] - $QuantitySold;

}

$sql = 'UPDATE Products SET '
        'Stock = ? '
            .'WHERE '
        .'ProductID = ?';

$params[] = $Stock;
$params[] = $ProductID;

$query = $this->db->query($sql, $params);


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.