1 Replies - 717 Views - Last Post: 06 April 2013 - 07:51 PM Rate Topic: -----

#1 eeveemaria20  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 03-April 13

How to set expiration date in php?

Posted 05 April 2013 - 06:57 PM

$startdate = date('Y-m-d H:i:s'); 
		 echo $startdate."</br>"; 
		 $offset = strtotime("+1 day");
		 $enddate = date("Y-m-d H:i:s", $offset);
		 echo $enddate."</br>"; 

 


$newamount = $availablequantity - $quantity;
if($enddate > $startdate) {
if($availablequantity != 0) {	
		
$sql = mysql_query("UPDATE size SET quantity ='$newamount' WHERE id ='$item_id'") or die (mysql_error());

}
 //$sql = mysql_query("UPDATE size SET quantity = WHERE size ='$item_id'") or die (mysql_error());		
	
		} 
else if($startdate < $enddate) {
	
	$sql = mysql_query("UPDATE size SET quantity ='$availablequantity' WHERE id ='$item_id'") or die (mysql_error());
	$sql = mysql_query("DELETE FROM reservations WHERE size_id ='$item_id'") or die (mysql_error());
	}
	}
}



Can anyone help me how to return the product if no transaction has been made.. :(/> What I must use in order for the system to automatically expire the product and return it to the size table. please help me.

This post has been edited by e_i_pi: 05 April 2013 - 09:05 PM
Reason for edit:: Please use the code tags when posting code


Is This A Good Question/Topic? 0
  • +

Replies To: How to set expiration date in php?

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3712
  • View blog
  • Posts: 5,965
  • Joined: 08-June 10

Re: How to set expiration date in php?

Posted 06 April 2013 - 07:51 PM

Hey.

Two things, first of all:

  • Your $startdate and $enddate are hard-coded there, meaning that $enddate will always be larger than $startdate, thus making the IF condition pointless. - Is this meant to be like this? Are these values perhaps being fetched from somewhere else and this is just hard-coded for our benefit?

  • Your IF and ELSEIF clauses are the exact same condition:
    $end > $start == TRUE
    $start < $end == TRUE
    
    ($end > $start == $start < $end) == TRUE
    
    


Now then. If I am understanding you correctly, you have a table that lists available amounts of certain products, and you have a table where units of those products can be reserved. Right? - Your problem, then, is how you can automatically expire reserved products and have them be added back to the number of available units.

You can entirely avoid this problem with a slight shift in tactics. Instead of having an "available" amount that is reduced and increased as products are reserved, sold and expired, have an "overall total" value that lists all units that have been made available throughout the existence of the product, and then count how many items have been sold or reserved.

For example, consider if you had this data:
Table: products
+----+-------------+
| id | stock_total |
+----+-------------+
|  1 |          20 |
|  2 |          55 |
+----+-------------+

Table: reserved
+----+------------+--------+---------------------+
| id | product_id | amount | date_reserved       |
+----+------------+--------+---------------------+
|  1 |          1 |      2 | 2013-04-03 22:00:00 |
|  2 |          2 |      1 | 2013-04-05 18:00:00 |
|  3 |          2 |      3 | 2013-04-06 05:00:00 |
|  4 |          1 |      1 | 2013-04-07 03:00:00 |
+----+------------+--------+---------------------+

Table: sold
+----+------------+--------+
| id | product_id | amount |
+----+------------+--------+
|  1 |          2 |      1 |
|  2 |          2 |      3 |
|  3 |          1 |      2 |
|  4 |          2 |      1 |
+----+------------+--------+


There you have a products table that lists the "stock_total", which is the total sum of all the stock you ever had for that product. It is never reduced, only added to as more of that unit becomes available. - You then have a "reserved" and a "sold" table, which lists the reserved and sold units of a product. In the reserved table, you have the exact datetime when the reservations is made. This reservation date can then be used to calculate, in SQL, whether or not a reservation has expired.

To get the available stock for a product, you then calculate the sum of both the sold units, and the active reservations, and subtract that from the stock_total. That'll give you the number of units that have not been sold and are not reserved. There is no need to ever have code "expire" reservations and rebalance the stock number, because all that is calculated automatically in SQL.

A SQL query to do that could look like this:
SELECT 
    p.id,
    p.stock_total - SUM(r.amount) - SUM(s.amount) AS stock_available
FROM products AS p
LEFT JOIN reserved AS r
    ON  p.id = r.product_id
    AND r.date_reserved + INTERVAL 1 DAY > NOW()
LEFT JOIN sold AS s
    ON p.id = s.product_id    
GROUP BY p.id



Do you see what I mean?
If this SQL query isn't clear to you, I suggest you look into how JOINs work in SQL.
Was This Post Helpful? 3
  • +
  • -

Page 1 of 1