Updating database without using button? PHP MySQL

Question

May I know If it is possible?
Updating MySQL Database without using button, I mean something like script.

Scenario:
I want to have running script that will search for a value that will meet the condition I want then it will automatically update the value.

Example:

I have the columns named - dateStarted, dateExpiration and status so if dateExpiration is today it will change the value of status from ACTIVE to INACTIVE.

PS: dateStarted + 30 days = dateExpiration

This is my ideal codes so far but don't know how I will call the data and update it without using button something like it must be running in background.

$dateExpired = date('Y-m-d', strtotime($dateStart. ' + 30 days'));
$dateExpired2 = date('Y-m-d', strtotime($dateStart. ' + 90days'));

$status = "FRESH";

if(strtotime($dateStart)<strtotime('-30 days')){
$status = "INACTIVE";
}

if(strtotime($dateStart)<strtotime('-90 days')){
$status = "DORMANT";
}

Thank you.

a trigger https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html — Funk Forty Niner, Nov 04 '15 at 00:23
I had thought of cron also actually, but wasn't 100% sure if that was the idea here. Seemed like a trigger was more fitting, least for MySQL that is. — Funk Forty Niner, Nov 04 '15 at 00:31
no, you need to create it yourself inside your SQL control panel — Funk Forty Niner, Nov 04 '15 at 00:37
see this page https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html — Funk Forty Niner, Nov 04 '15 at 00:38
I will try this thanks, but can you post this as answer so I can vote up this? — Edmhar, Nov 04 '15 at 00:40

score 2 · Accepted Answer · edited May 23 '17 at 11:58

2

In order to automatically update a database based on an event, you can use a MySQL trigger.

Example from the manual:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;

Reference:

https://dev.mysql.com/doc/refman/5.5/en/trigger-syntax.html

The trigger first needs to be create inside an SQL control panel.

If you wish to run a cron job, which may be an option for you, consult the following:

edited May 23 '17 at 11:58

Community

1
1

answered Nov 04 '15 at 00:44

Funk Forty Niner

74,450
15
68
141

Problem Solved, I will accept this as answer although I used CRON jobs. Thanks again sir! – Edmhar Nov 04 '15 at 03:09
1

@Edmhar I'm glad to hear it. I thought I'd add a few links in regards to cron jobs in my answer, just in case. *Cheers* – Funk Forty Niner Nov 04 '15 at 03:10
Yes, I used the links you provided as my references. Thanks again sir! – Edmhar Nov 04 '15 at 03:14

Updating database without using button? PHP MySQL

1 Answers1