-1

I have this plan to set the column status as "Expired" when there's no uploaded file in column proof_of_payment within the set date in column date_expiration. (I need this function to expire the order.)

How to make it possible? Can I create condition in sql or manipulate it in php codes?

LIKE FOR AN EXAMPLE: enter image description here

Sanu0786
  • 571
  • 10
  • 15
Jaz
  • 55
  • 8
  • 1
    1. Either set up a cron using application code (php) to run at midnight, and update status accordingly 2. Or, create MySQL events again running every day on midnight I would prefer the method 1 – Madhur Bhaiya Oct 22 '18 at 07:03
  • @MadhurBhaiya All due respect. But my question is really specific based on the things I want to do and I have my examples to make it more specific. But thankyou for ur feedback! – Jaz Oct 22 '18 at 07:13

1 Answers1

2

ok you should first enable event_scheduler using your mysql command line:SET GLOBAL event_scheduler = ON; now lets create your schedule : -you want to update a column if the condition of comparing two other columns are met.i guess this syntax will handle the problem:

    CREATE EVENT test_event_04 ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP
    DO UPDATE table_name SET STATUS='expired' WHERE CURDATE()>date_expiration AND proof_of_payment IS NULL

just remember to change table_name to your real table name the schedule will run every day once created (EVERY 1 DAY STARTS CURRENT_TIMESTAMP )

faramarz
  • 84
  • 1
  • 11
  • 1
    check this link it may also help you https://www.sitepoint.com/how-to-create-mysql-events/ – faramarz Oct 22 '18 at 08:11
  • theres an error. I just turn on event and add events and paste all codes above. – Jaz Oct 22 '18 at 08:57
  • I also pasted it in sql. But led to error. Im not so good in sql please help – Jaz Oct 22 '18 at 08:59
  • did you change the your_table_name value to your real table name? – faramarz Oct 22 '18 at 09:09
  • 1
    @faramarz yes I did. Thankyou for your concern, instead of telling people should be like this or should be like that. Thankyou for understanding that everyone starts with a little knowledge about sql. And dont be like someone who burst out their stress through comments or any online platform. – Jaz Oct 22 '18 at 09:13
  • 1
    tnx bro . ill update my answer . im changing the syntax , this one will probably work for you – faramarz Oct 22 '18 at 09:30
  • @faramarz okay thankyouu im waiting – Jaz Oct 22 '18 at 09:35
  • 1
    @faramarz thankyou for not being so affected in just a small thing. Anyway, im just talking to you and not to anyone else. So you have a right to say all things you want. – Jaz Oct 22 '18 at 09:42
  • ok now try it . i guess this one will work . you could set EVERY 1 DAY to EVERY 1 MINUTE to test the result – faramarz Oct 22 '18 at 09:48
  • 1
    @faramarz it worked for me thankyou so much. Now i will use this code as basis and a piece to build somemore of these. Thankyou for giving example like this for me to understand how events works. – Jaz Oct 22 '18 at 09:49
  • 1
    you're welcome :) – faramarz Oct 22 '18 at 09:49