Updating Msql after 1 day

Need help with an engine or coding not on the list? Need help with a game or the website and forums here? Direct all questions here.
Post Reply
User avatar
Epiales
Posts: 1119
Joined: Thu Aug 15, 2013 1:38 am

Updating Msql after 1 day

Post by Epiales »

Hey all,

I have been working on a game, and now at the point that i"m creating packages that users can purchase. They are limited to certain times of the month, or limited to when I activate the package. When I activate the package, it inserts the datetime and 3 days after, the finish time.

Code: Select all

if($_POST['activatepack']){
    
$date = date('Y-m-d H:i:s');

$sql = "UPDATE dk_packages SET active='Yes', startdate='$date', enddate='$date'+INTERVAL 3 DAY WHERE package_id=1";
$query = mysqli_query($db_conx, $sql);    
$sql = "UPDATE dk_packages SET active='Yes', startdate='$date', enddate='$date'+INTERVAL 3 DAY WHERE package_id=2";
$query = mysqli_query($db_conx, $sql);    
$sql = "UPDATE dk_packages SET active='Yes', startdate='$date', enddate='$date'+INTERVAL 3 DAY WHERE package_id=3";
$query = mysqli_query($db_conx, $sql);
 
Now I have it setup where after the 3 days, it auto de-activates and no longer shows.

What I need though, is for it to update a row in the database after every 1 day until the activity is over with.

So let's say it starts at 12:00 on the 3rd and goes til 12:00 on the 6th.....

I need it to update the table row on the 4th at 12:00 and on the 5th at 12:00....

I have tried something like this, but not working:

Code: Select all

$sql = "UPDATE dk_pack_purch SET pack_1='No' WHERE charname='$charname' && pk1_starttime >= '$pk1_starttime' + INTERVAL 1 DAY)";
$querytime = mysqli_query($db_conx, $sql);
Below is the $pk1_starttime:

Code: Select all

$sql = "SELECT * FROM dk_packages WHERE package_id=1";
$user_query2 = mysqli_query($db_conx, $sql);
$numrows = mysqli_num_rows($user_query2);
$row = mysqli_fetch_array($user_query2, MYSQLI_ASSOC);
$pk1_starttime = $row['startdate'];
Any Ideas? Basically, when they purchase a package, it changes the package row to Yes..... and I need it to go back to No after 1 day so they can purchase it again. Basically can buy one package a day until the activity is over with.
Nothing fancy, but a work in progress!

http://gameplaytoday.net
Kira
Posts: 2
Joined: Wed Sep 09, 2015 6:16 pm

Re: Updating Msql after 1 day

Post by Kira »

I don't know if mSQL supports event scheduling. But you could schedule an event.

http://www.sitepoint.com/how-to-create-mysql-events/
https://dev.mysql.com/doc/refman/5.1/en ... rview.html

An alternative is to call a SP or run a query before checking which package a user can buy. But to me the flag seems like redundant data because you could calculate everything based on the enddate.
User avatar
hallsofvallhalla
Site Admin
Posts: 12031
Joined: Wed Apr 22, 2009 11:29 pm

Re: Updating Msql after 1 day

Post by hallsofvallhalla »

Make the update when the user logs in. The flag wont matter till then anyways.
User avatar
Kesstryl
Posts: 202
Joined: Sat Sep 22, 2012 5:27 am

Re: Updating Msql after 1 day

Post by Kesstryl »

Kira wrote:I don't know if mSQL supports event scheduling. But you could schedule an event.

http://www.sitepoint.com/how-to-create-mysql-events/
https://dev.mysql.com/doc/refman/5.1/en ... rview.html

An alternative is to call a SP or run a query before checking which package a user can buy. But to me the flag seems like redundant data because you could calculate everything based on the enddate.
Thank you for those links! Those are going to help me!
Post Reply

Return to “Advanced Help and Support”