Create EVENT on succes of $sql

[klemme]

Hi All,

I am hoping to finish this feature on a shopping cart soon - just one thing im not sure how to create.

Basically: 1 hour after a user has put the very first item into the shopping cart - (items are stored in the database) I want to create an event, that after one hour empties the cart table WHERE user_id = '$user_id';
All items in the cart are to be deleted, and then two other tables need to be updated based on the content of the basket.

1) Table "sizes" - needs to be updated, and have "antal" (means stock..) set to the number of that particular number that was in the cart before, and WHERE size = '$size'; So if the cart had 2 * Large, I need to update the "sizes" table with 2 * Large where product_id = $some_id;

2) Table "products" - Needs to be updated and set "stock" = the number of goods that was in the cart of that particular product_id. So if the basket had 3 items of product_id = 1, Then I need to add this back to the products table where product_id = $some_id;

_______________________________________________

I am doing this when the customer is viewing his cart - And it is exactly these 3 calls which needs to be executed after one hour of the first item inserted. - Only here these actions are triggered after a customer has clicked a link ("Remove all items from cart, red").
I need them to be executed if the customer left the site and the cart without deleting them

<?PHP // This happens if a link is clicked in the cart of the website:
// Delete from cart, and update "sizes" and "products":
$REMOVE_sql = mysqli_query($connection, "
DELETE FROM cart WHERE id = '".$FJERN_alt_kurv_id."' AND user_id = '".$user_id."'");
// Update products, and set stock = the number of goods deleted from the cart:
$plus_SQL_products = mysqli_query($connection, "
UPDATE products SET stock = stock + '".$FJERN_alt_antal."' WHERE produkt_id = '".$FJERN_alt_id."'");
// Update sizes and set the stock of the particular size = number of goods from the cart in that size:
$plus_SQL_sizes = mysqli_query($connection, "
UPDATE sizes SET antal = antal + '".$FJERN_alt_antal."' WHERE product_id = '".$FJERN_alt_id."' AND size = '".$FJERN_alt_str."'");
header('location: /indkoebskurv');
?>

So, if the user has items in the cart, and leaves the site without removing the items, I need the items to be autimatically removed after an hour, and execute these three calls.

I have read that I cant use parameters in an EVENT, but that I can call a stored procedure which has got parameters.

- It might be a little messy, but I hope I have made my intentions clear.

As I havent created events before, neither have I created stored procedures - and never even been close on combining those :-)

Can someone point me in the right direction on how to do this?

Regards, Klemme

[pritaeas]

I was wondering if you want create/delete events per cart. If so, wouldn't it be easier to run a scheduled task every 5 minutes, and update your tables with a last-used timestamp?

[klemme]

Yes it would have to be per user, which is per cart!

How do you think that could be done?

Would you use the unique user_id from the cart, along with a time_stamp for the first item put in the cart?

Could you go into further details on how to do this with an scheduled event?

It sounds like a better solution than what I thought of first!

[pritaeas]

The way I see it is like this:
1. When the cart is created you have an entry with a timestamp in your table.
2. Whenever the cart is updated, the timestamp is also updated.
3. When the cart is processed, you can set a flag to indicate it is not to be deleted.

Next to that you will have a scheduled task (in PHP or MySQL, doesn't really matter) which runs, say every 5 minutes. Every time it runs, it will check your cart table. For every entry for which the timestamp is older than an hour ago, it executes your queries.

You can do this with a MySQL event, but with my scenario, you only need one event (more on creating one here).

You could also make a PHP script that does this, and execute it through a CRON job (assuming you have acess to that).

[klemme]

I have these parameters i need to use, so I know exactly which rows to update:

I need to use the "product_id" from the item deleted in the cart after the event has run, and use that in the other queries.
I also need to know the number of products in the cart with that product_id, and that specific size in order to update the stock in the two other tables ("sizes","products").

In a mixed SQL/PHP with the parameters included it looks like this:

delimiter |

CREATE EVENT delete_cart_items
    ON SCHEDULE
      EVERY 5 MINUTES
    COMMENT 'Deletes cart items older than one hour from insert'
    DO
      BEGIN
	  
DELETE FROM cart WHERE added_time = added_time + 1 hour
          
UPDATE products SET stock = stock + '".$FJERN_alt_antal."' WHERE produkt_id = '".$FJERN_alt_id."'
		
UPDATE sizes SET antal = antal + '".$FJERN_alt_antal."' WHERE product_id = '".$FJERN_alt_id."' AND size = '".$FJERN_alt_str."'  

      END |

delimiter ;

Most likely wrong syntax.. - How do i get these parameters, because I cant use them in the event, can i? Shouldnt I select them "INTO" inside the event (BEFORE I DELETE FROM THE CART) and then run the update queries somehow?

Do you get my idea? :-)

[pritaeas]

The values you need are all in the cart table, or at least they should be (or linked to it).

[klemme]

Thats right, I'm just wondering how the syntax is on getting these values, and use them to update the other tables inside the event, but here is what I want to do at least:

I have 4 steps, marked by 1,2,3,4 in the event - sorry for the syntax, this is what i need help to, too..

delimiter |
 
CREATE EVENT delete_cart_items
    ON SCHEDULE
      EVERY 5 MINUTES
    COMMENT 'Deletes cart items older than one hour from insert'
    DO
      BEGIN
COMMENT 'Get the info from cart that I need for updating the sizes, and products table'
1) SELECT produkt_id, antal, str FROM cart WHERE added_date = added_date + 1 hour

COMMENT 'UPDATE stock in the products table, based on the values from cart'
2) UPDATE products SET stock = stock + cart.antal WHERE produkt_id = cart.produkt_id

COMMENT 'UPDATE stock in the sizes table, based on the values from cart' 
3) UPDATE sizes SET antal = antal + cart.antal WHERE size = cart.str AND product_id = cart.produkt_id  

COMMENT 'And at last, delete the items from the cart'
4) DELETE FROM cart WHERE added_time = added_time + 1 hour
      END | 
delimiter ;

Can someone help me out on writing this with the correct syntax? :-/

[hag++]

Why not just use sessions and define your own session handlers? When the session is destroyed (say.. after an hour of inactivity?) you can also remove all items from the cart. Take a look at this link: http://www.php.net/manual/en/function.session-set-save-handler.php