MySQL Forums
Forum List  »  Docs

Help with determining cost of goods sold
Posted by: Brizzle Mc' Nizzle
Date: August 08, 2012 03:19PM

It will always be the one with the lowest date. The date represents the day that the shipment was added to the site.

Maybe this will help out in some way. I didn't want to add this because it may have thrown people off putting it in the original post but the way I make this happen with my updateInventory() function, that updates the inventory when I make a sale (it subtracts the quantity that I sold from the inventory table), is with the out_date field. When it's no longer set as NULL it means that the inventory is at 0 and the row is no longer selected. I can't illustrate it with just one command so here is the whole PHP function with comments.

	public function updateInventory($sold) {

		/* Select all of the rows by date only where the out_date is null. The out_date represents 
		   the date that this row (shipment) sold out. (inventory became 0) */ 

		$sQuantity = $this->db->query('SELECT `quantity` FROM `inventory` WHERE `out_date` IS NULL ORDER BY `date` ASC LIMIT 1');
		$quantity = mysql_fetch_row($sQuantity);

		/* We don't want to subtract more inventory from a row than we have so if the 
		   sold amount is higher than the quantity left for this shipment, it moves on to the next row.
		   This is where the out_date comes into play. Once it is not set as NULL, it's no longer selected 
		   in the next pass through. */
		if($sold >= $quantity) {
			$update = $this->db->query('UPDATE `inventory` SET `out_date`=now(), `quantity`=(`quantity`-'.$quantity.')');
			if($update) {
				$sold = $sold - $quantity;
				if($sold > 0) {
				} else {
					return false;
			} else {
				return false;
		} else {

			/* If the sold amount is less than the quantity in the inventory table, just subtract the amount sold and return true */

			$update = $this->db->query('UPDATE `inventory` SET quantity=(quantity-'.$sold.') WHERE `out_date` IS NULL ORDER BY `date` ASC LIMIT 1');
			if($update) {
				return true;
			} else {
				return "arror";

Edited 2 time(s). Last edit at 08/08/2012 07:02PM by Brizzle Mc' Nizzle.

Options: ReplyQuote

Written By
Help with determining cost of goods sold
August 08, 2012 03:19PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.