Mysql query to update status on condition

Question

Databse table-

     id        created_date             timer     status 
      1    2013-02-25 16:40:45           1        Y  
      2    2013-02-25 16:40:45           2        Y  
      3    2013-02-25 16:40:45           0        Y

This is my database table.

created_date field is datetime and timer is varchar value (integer value)

what I want is to set status N for every day at 12:00 AM and when timer value is not 0 then set status=N by adding timer+created_date , all status change to N at 12:00 AM weather timer set or not.

@ChetterHummin i will make a query to update status at 12:00 AM but i don'y get how to deal with timer and update according to timer value . give me a clue . I am new in programing. — Vaibhav Agarwal, Feb 26 '13 at 07:42
Update timer set status = 'N' where created_date = '12:00:00' — Vaibhav Agarwal, Feb 26 '13 at 07:49

Mortalus · Answer 1 · 2013-02-26T08:04:09.260

0

Try this:

Update tableName
set status = 'N'
where time <> 0 and time(created_date) = '12:00:00'

Then to update the date with the time value assuming its in seconds you do this

Update tableName
set created_date= AddTime(created_date, timer)
where status = 'N'

edited Feb 26 '13 at 08:04

answered Feb 26 '13 at 07:45

Mortalus

10,574
11
67
117

how to add timer value in that to set status = 'N' – Vaibhav Agarwal Feb 26 '13 at 07:47
You want to add timer+created_date to which column? created_date or to timer ? and timer is in seconds or minutes ? – Mortalus Feb 26 '13 at 07:54
timer is in hours . want to update status after timer+created_date time is completed. and all status to N at 12:00 AM it does't depend on timer value. – Vaibhav Agarwal Feb 26 '13 at 07:58

score 0 · Accepted Answer · answered Feb 26 '13 at 07:49

0

you must take advantage of cronjob.

some helpful links are :

http://net.tutsplus.com/tutorials/php/managing-cron-jobs-with-php-2/

https://my.bluehost.com/cgi/help/168

answered Feb 26 '13 at 07:49

Dipesh Parmar

27,090
8
61
90

http://stackoverflow.com/questions/4421020/use-php-to-create-edit-and-delete-crontab-jobs – Dipesh Parmar Feb 26 '13 at 07:50
I want to make a query not running a crone . – Vaibhav Agarwal Feb 26 '13 at 07:52

Mysql query to update status on condition

2 Answers2