auto decrement?

[Sharkadder]

Ok i have a mysql database and was simply wondering if it is possible to auto decrement a value within a daily basis?So for example a column called "subscription length" will go down by 1 each day until it reaches 0 for all records under that column.Is this possible to do this? If so how do i go about doing it? I have looked on the net but not found many examples relating to it.The site is created also using php/html if i may need to do some sort of trigger function.thanks

[Synook]

You could use Cron to run a PHP script every day that performs the query.

[jeffman]

If you store a timestamp for each subscriber, either for the date of subscription or the expiration date, you should be able to evaluate what you want every time you access data for that user. You wouldn't need an auto-decrement thing.

[Sharkadder]

so what you mean is i create something to say when they sign up it displays the date signed up and in another column the date it expires. Once that is done i do some sort of take away from expiry date to sign up date? I don't quite get you here about this timestamp.I can already display the data of when the subecription would expire but you always get the odd one person who cannot be bothered to count how long is left, hence why i thought that it would be good to display the amount of days are remaining.I think a cron job may do it but i'll have to look into how i'd go about updating a column with supplying any update function with all records as i have only ever updated each record individually before.thanks, any other things you think i should know or examples i can work off or that may help then just post.

[boen_robot]

I can already display the data of when the subecription would expire but you always get the odd one person who cannot be bothered to count how long is left, hence why i thought that it would be good to display the amount of days are remaining.

How you store data in your database is irrelevant to the user. You can perform the calculation between the current date and expritity date for the user and show them that. i.e. instead of showing them "Your account expires at 03 September 2008", tell them "Your account expires in 1 day(s)" (after calculating 2008-09-03 - 2008-09-02). Since DATE is a MySQL datatype, calculation of such data can be done by the DB engine in a natural fashion with something like

SELECT TO_DAYS(expirityDate - CURRENT_DATE()) FROM account_info

(assuming the table was called "account_info" and the column having the expirity date was called "expirityDate". Note that I haven't done this, but with a quick glance at the MySQL manual, that seems to be it.)

[Sharkadder]

thanks for the reply, i have tried some simular code but all it seems to do is display the code and not the actual outcome, here is the code i have been trying:$db = mysql_connect($dbHost,$dbUser,$dbPass); // Connection Codemysql_select_db($dbname,$db); // Connects to database$query = "SELECT Username, Boxtype, Sublength, Subleft, Startdate, Expirydate FROM satellitebox WHERE Username = '$username'";$subremain = "SELECT TO_DAYS(Expiritydate - CURRENT_DATE()) FROM satellitebox WHERE Username = '$username'";$result = mysql_query($query, $db);if(mysql_num_rows($result)){$row = mysql_fetch_assoc($result);$newline = "<br />";// If the login information is correct do the following;echo "Username : {$row[username]} ".$newline;echo "Box Type : {$row[boxtype]} ".$newline ;echo "Subscription Length : {$row[sublength]} ".$newline ;echo "Subsciption Length Remaining : $subremain ".$newline ;echo "Start Date : {$row[startdate]} ".$newline ;echo "Expiry Date : {$row[Expirydate]} ".$newline ;}else {echo "You currently have no satellite box purchases/subscriptions";}It is all working apart from when i use the $subremain variable to equal the value from the database :S, as i say, all i get is the code displayed after "subscription length remaining"When i try creating a seperate result called $subresult for the $subremain variable it just comes out blank when i try echoing $subresult and no value, even if the Expirydate was set at 00:00:00 it should still display todays date which is oddthanks

[smartalco]

You don't have any place where it is executing the $subremain query, you would need to change the code to something like

$query2 = "SELECT TO_DAYS(Expiritydate - CURRENT_DATE()) FROM satellitebox WHERE Username = '$username'";$subremain = mysql_query($query2, $db);

[Sharkadder]

ok i have got a step further, now i seem to get a message saying this when i display it in my browser:Subsciption Length Remaining : Resource id #5 i am not sure what resource id #5 means, maybe somebody can explain this for me, anyways my code now is as follows:$db = mysql_connect($dbHost,$dbUser,$dbPass); // Connection Codemysql_select_db($dbname,$db); // Connects to database$query = "SELECT Username, Boxtype, Sublength, Subleft, Startdate, Expirydate FROM satellitebox WHERE Username = '$username'";$query2 = "SELECT TO_DAYS(Expirydate - CURRENT_DATE()) FROM satellitebox WHERE Username = '$username'";$subremain = mysql_query($query2, $db);$result = mysql_query($query, $db);if(mysql_num_rows($result)){$row = mysql_fetch_assoc($result);$newline = "<br />";// If the login information is correct do the following;echo "Username : {$row[username]} ".$newline;echo "Box Type : {$row[boxtype]} ".$newline ;echo "Subscription Length : {$row[sublength]} ".$newline ;echo "Subsciption Length Remaining : $subremain ".$newline ;echo "Start Date : {$row[startdate]} ".$newline ;echo "Expiry Date : {$row[Expirydate]} ".$newline ;}else {echo "You currently have no satellite box purchases/subscriptions";}whats going wrong this time do you reckon? the code looks ok to me

[justsomeguy]

You're printing $subremain, which is the return value from mysql_query. The mysql_query function returns a MySQL result resource. When you try to print something like that it just prints the resource ID. You need to use a function like mysql_fetch_array to get the row of data and then print the item in the row that you're interested in. You probably want to give the column name an alias:$query2 = "SELECT TO_DAYS(Expirydate - CURRENT_DATE()) AS days FROM satellitebox WHERE Username = '$username'";And then refer to that field using the name, e.g. $row['days'].With your other query you are building the query and sending it to the server to get the result set back, checking to see if it returned records, and getting the row before printing it. With the second query you're sending the query to the server and getting the result set back, and just trying to print that without getting a row or checking to make sure it returned a row.

[Sharkadder]

i tried your idea justsomeguy and again it's not working, the value just comes up as blank now with nothing in it, here is the code i have:$db = mysql_connect($dbHost,$dbUser,$dbPass); // Connection Codemysql_select_db($dbname,$db); // Connects to database$query = "SELECT Username, Boxtype, Sublength, Subleft, Startdate, Expirydate FROM satellitebox WHERE Username = '$username'";$query2 = "SELECT TO_DAYS(Expirydate - CURRENT_DATE()) AS days FROM satellitebox WHERE Username = '$username'";$subremain = mysql_query($query2, $db);$result = mysql_query($query, $db);if(mysql_num_rows($result)){$row = mysql_fetch_assoc($result);$newline = "<br />";// If the login information is correct do the following;echo "Username : {$row[username]} ".$newline;echo "Box Type : {$row[boxtype]} ".$newline ;echo "Subscription Length : {$row[sublength]} ".$newline ;if(mysql_num_rows($subremain)){$row2 = mysql_fetch_array($subremain);}echo "Subsciption Length Remaining : {$row2['days']} ".$newline ;echo "Start Date : {$row[startdate]} ".$newline ;echo "Expiry Date : {$row[Expirydate]} ".$newline ;}else {echo "You currently have no satellite box purchases/subscriptions";}when i execute that code i get "subscription length remaining: " and nothing after it, do i need to do anything at all new to the database or should that be fine with the expiry date field in the format 2008-09-30thanks

[boen_robot]

Try to select just the Expirydate first... like I said, I haven't done this, so I may be wrong. If you get THAT, the next logical step is to use a calculation like mine or similar to get the days remaining instead of the Expirydate.

[Sharkadder]

ok i've kind of got it working although it's inaccurate, today is the 3rd of september and the expiry date is the 30th, 30-3 = 27 but it says 28 days left.here is what i am doing now:$query2 = "SELECT Expirydate - CURRENT_DATE() AS days FROM satellitebox WHERE Username = '$username'";i guess i could always add a +1 onto the expiry date or -1 on the current_date although not very practicalEDITi've just realised what might be going wrong, i tried doing just current date and it says the 2nd, i think my sql is set up for american time and not GMT, that is another thing i'd need to know how to change. I'll have to look into it but i think the actual code does work. Some info on how i can use the convert_tz function for use in my code would be good, when i try it yeah it don't work right, here is what i tried to get the time to gmt:$query2 = "SELECT DATE_FORMAT(CONVERT_TZ(timestamp,'GMT','EST'),'%M %e, %Y') Expirydate - CURRENT_DATE() AS days FROM satellitebox WHERE Username = '$username'";It doesn't compile right and i get error messages about the row in the table

[Sharkadder]

ok just letting people know i've not got much further with thisd sorting the date out thing, as far as i am aware the server time is 6 hours behind here in the UK, i have been told that i am/they are not allowed access to change the sql time zone from system to GMT, i have tried the code i posted above and many other attempts, i have even tried putting the dates into php variables and then trying to subtract, but for some reason php handles it differently to what mysql does, i.e. it doesn't work even though it's in the same format.So here is query2:$query2 = "SELECT Expirydate - CURRENT_DATE, Expirydate - CURRENT_DATE() + INTERVAL 6 HOUR AS days FROM satellitebox WHERE Username = '$username'";what do i need to do to get the CURRENT_DATE variable to be +6 hours? do i need to use DATETIME or NOW variable instead? when i tried that it didn't display the days remaining once echoed in php, i guess i could always get some formula to work out the date, hours and seconds once i know a function that may work, even if it is one that gives me the value in seconds. At the minute "days" just appears blank once echoed in php.thanks, sorry i don't mean to be a pain but i'm so close to getting this sorted now

[boen_robot]

I personally hate to do time zone calculations manually. Just make your SQL server, PHP and DB data to be in the same time zone. Then, if your application is used in multiple time zones, once you've fetched the data, use PHP to adjust the timezone to whatever the clients' time zone is. Use something like date_timezone_set() for that.

[Sharkadder]

EDITSorted it, this is what i have done:$query2 = "SELECT DATE_FORMAT(Expirydate,'%Y%m%d') - DATE_FORMAT(DATE_ADD(CURRENT_DATE,INTERVAL 8 HOUR),'%Y%m%d') AS days FROM satellitebox WHERE Username = '$username'";ok so query 2 is the calculation with the time zone sorted out by adding an 8 hour interval, i found out with was 8 hours by selecting the basic now() function instead of the calculation to begin with. I put now on an interval of 8 hours and it displayed the time here in the uk once echoed in php.I then realised that the interval was working so i inputted my calculation, once done it showed up as 26, this meaning 26 days remaining. i then put the interval on 24 hours and it showed a day less because of the date_add function adding values to the current date.So all is fine and working.may thanks for the help

[Sharkadder]

just a quick update, as you may of noticed and laughed at, the code i posted above doesn't work, it will work for that 1 month but after that it starts counting in hundreds, i have also realised adding a days interval onto a date doesn't work, only if you put it as 24 hour, then it will add a date on respectively, otherwise it doesn't work.I can now see why you lot are telling me that timezones are a pain in the backside, they don't work properly, trying to add/subtract days and then convert the time zone is very hard to do.So to sum up i have finally given up after 2 days SOLID trying to get this working, for the sake of 8 hours difference i think i shall leave it for now. The date_timezone_set variable is for php5 and not 4 which i believe i am using. Even if i had been using php5, other timezone functions i have tired have not worked when communicating with mysql, just for php.I guess if i really really wanted to, i could have a faff about converting the whole date into seconds, then subctract the seconds and convert them back into days (seconds/60/60/24) is the formula but then i would come across the problem if somebody subscribes into the next year, the calculations would be off. With mysql you can only determine the seconds from the start of the year until the date you specify, so for a date the same in 2009 it would be the same seconds as a date the same but in 2008. Rather than risk things not working i am back to square one i am afraid. I'm not really prepared to spend another 2 or 3 days writing a manual date formula calculation for subscriptions going into the next year with php as it would take me a while, although it would be nice.

[boen_robot]

Why not upgrade to PHP5? Problematic host? Ask them first... if they can't give you that, find another host. It's about time to ditch PHP4.

[Sharkadder]

well actually i chose php4 instead because a function i am using wouldn't work with php5 or something, i forget now off top of my head what it was, but they do allow me to use php5.So if i read you right, if i convert over to php5 and use the date_timezone_set() variable then i will be able to convert all date and time, even variables equaling mysql date values into GMT and not GMT -7 by placing it at the top of my php page?

[boen_robot]

well actually i chose php4 instead because a function i am using wouldn't work with php5 or something, i forget now off top of my head what it was, but they do allow me to use php5.

There are no removed PHP4 functions in PHP5 that I know of. Not in the core anyway. There are only incompatible extensions (like the XSLT and DOMXML extensions to name some), but that's another story, and besides, such always have PHP5 equivalents (XSL and DOM for the above mentioned).

So if i read you right, if i convert over to php5 and use the date_timezone_set() variable then i will be able to convert all date and time, even variables equaling mysql date values into GMT and not GMT -7 by placing it at the top of my php page?

um... not at the top, after you fetch the result from the query. As I said, make PHP, SQL and the DB data in the same time zone. Use date_timezone_set() only when you fetch dates from MySQL, and this function will adjust the timezone to whatever you specify, and will adjust the date if it must.

[Sharkadder]

ok thankd dude, i'll convert php over to 5, when that is done i shall have a bash at the timezone change, if i get stuck or accomplish what i need i will come back. But when i last told a variable to equal a date and applied a timezone change it didn't work for the date, when it struck 12 midnight it was still 8 hours behind. Let's see how this functin goes.thanks

[Sharkadder]

ok i think i have now got it all sorted, i changed the php on my web server to 5, i have then re-writeen my code and now i just use the 1 query, i figured they was no need digging myself into a hole by creating many queries.So now basically all the days are calculated with php and not mysql, next i put some conditions in to make it more watertight, i have done some if statements to check that the current date/start date is not greater than the expiry date on both conditions for the subscription length and the length remaining. If the current date equals or goes over the expiry then the value will change to 0 subscription length, if the current date goes above the expiry for length remaining, then the word "expired" will then be displayed.I have tried putting the timezone to Bangkok time as it is 8 hours ahead here in uk so the next day should appear, with date_timezone_set it didn't but when i tried putenv("TZ=Europe/London") ; it seemed to make a difference.So my dates now seem to change in accordance with the new time, i.e. Europe/London, it seems that php4 just couldn't handle what i was trying to do, i would check it come midnight tonight but the chances of me being home aren't strong, so i will have to confirm it tomorrow, but it all looks ok to me. Since i am now using php's date functions with mysql data which php4 must not of let me do, all seems ok so far.Here is my code if your interested to know, many thanks, i'll probably edit this post come monday morning on how it is working, providing you don't spot any potential errors with my coding.thanks$db = mysql_connect($dbHost,$dbUser,$dbPass); // Connection Codemysql_select_db($dbname,$db); // Connects to database$query = "SELECT Username, Boxtype, Sublength, Subleft, Startdate, Expirydate FROM satellitebox WHERE Username = '$username'";$result = mysql_query($query, $db);if(mysql_num_rows($result)){$row = mysql_fetch_assoc($result);$newline = "<br />";// If the login information is correct do the following;echo "Username : {$row[username]} ".$newline ;echo "Box Type : {$row[boxtype]} ".$newline ;putenv("TZ=Europe/London") ;$DateTimeZone = timezone_open ( 'Europe/London' ); //date in the past.$date1 = "{$row[startdate]}";$dateTime = date_create( $date1);date_timezone_set( $dateTime, $DateTimeZone );//current date.$date2 = "{$row[Expirydate]}";//calculate the difference in seconds.$difference = abs(strtotime($date2) - strtotime($date1));//calculate the number of days$days = round(((($difference/60)/60)/24), 0);if ($date2 > $date1){echo "Subscription Length : $days days ".$newline ;}if ($date2 <= $date1){echo "subscription Length : 0".$newline ;}//date in the past.$date01 = "{$row[Expirydate]}";//current date.$date02 = date("Y-m-d", time());//calculate the difference in seconds.$difference1 = abs(strtotime($date02) - strtotime($date01));//calculate the number of days$days01 = round(((($difference1/60)/60)/24), 0);if ($date01 > $date02){echo "Subsciption Length Remaining : $days01 Days ".$newline ;}if ($date01 <= $date02){echo "Subsciption Length Remaining : Expired ".$newline ;}echo "Start Date : {$row[startdate]} ".$newline ;echo "Expiry Date : {$row[Expirydate]} ".$newline ;}else {echo "You currently have no satellite box purchases/subscriptions";}

[Sharkadder]

ok all is well, just ticked 11:55 and no date change, i refreshed every minute until midnight, when it got to midnight the days did indeed go down by one in accordance with gmt, i was actually surprised it was so in time with the time on my laptop.many thanks for all the help people, that is my last post on this case, the code i posted above does the trick nicely.thanks