Help with a cronjob sql auto update

[sKoop]

Hi guys.. first of all my name is Robert im a bit newbie and just learning bit by bit PHP  it's great to have find this community and really hope and appreciate if someone could help me with the following:

So basically i want to make a cronjob that once every hour updates the value X from the table Z..

The value X i would want it that every hour changes to 1.. and then after 1 more hour changes to 0.. and so on all day long

I would really appreciate if someone can show me what code should i use exactly so that it does this.. i can do the DB connection and set up the cronjob properly but i really dont know what the code should look like to do the updates i said..

 

Thanks and any help is greatly appreciated !

 I hope i gave enough info with what i want to do .. if not please ask i will try to explain better

Thanks !

[Barand]

A couple of methods spring to mind

1 ) toggle the value between 0 and 1 ...

UPDATE tableZ
SET x = CASE 
            WHEN x = 1 THEN 0 
            ELSE 1
        END;

   or

UPDATE tableZ
SET x = (x = 0);

 

2 ) set it based on the time

UPDATE tableZ
SET x = HOUR(NOW()) MOD 2;

Of course the queries may need a WHERE clause unless you want to apply the change to every record in tableZ.

Edited December 8, 2019 by Barand

[sKoop]

A ok sir thank you very much.. 1 more quick question i would have if you may please.. i am pretty bad at this sorry :))

So lets say i have the following table Z:

ID         value

1              0

2              0

 

Now..  i tried in my cronjob i did "UPDATE Z SET value=1" i did this set a cronjob for the first 30 minutes of an hour .. then the next 30 minutes i can just add another cronjob that sets the value 0.. ( i found this to be easier i think that using a toggle because i am noob )

So everything is fine.. the above command updates my values.. but i want only for ID 1 to do the value change not all.. how can i do that please ?

What do i need to change or add in the above command to only update the ID 1 ? I tried with a WHERE but i dont think i placed it right it didnt work..

 

Thanks sir

 

Edited December 8, 2019 by sKoop

[Barand]

UPDATE tableZ
SET x = (x = 0)
WHERE id = 1

 

[sKoop]

10 minutes ago, Barand said:

UPDATE tableZ
SET x = (x = 0)
WHERE id = 1

 

This command works perfectly !! Thank you very much for your help

[requinix]

I'm partial to x = 1 - x.

Are you sure you need a cronjob for this? Very many "I need a cronjob to do X" problems can be resolved in a way that doesn't need a cronjob...

[Barand]

If it toggles every hour then the time-based value does not require a cron job. The value is available anytime you need it

SELECT HOUR(NOW()) MOD 2 as value