vigiw Posted September 26, 2006 Share Posted September 26, 2006 Hello, I would like to improve my current method of using and expiring data sent to my MySQL database using PHP forms. I am running a weather forecast & weather alerts system. Currently the forecaster (person submitting the forms) has to manually type in the time of expiration, using the MySQL TIMESTAMP format:YYYY-MM-DD HH:mm:ss I will run a query in phpMyAdmin from time to time to hunt down rows of data that have already expired, and those that have already expired, using the correct format, will be erased using this query. That is working. However, am I able to allow the user to put in a number for this field, such as "6". And this will allow the data to expire in 6 hours, or however many hours the user has set it to? If I can do this, please explain how, because this would be a wonderful upgrade.Thank you! If you have more questions about what I mean, I will gladly answer them here. ;D Quote Link to comment Share on other sites More sharing options...
sanfly Posted September 26, 2006 Share Posted September 26, 2006 I will try not to get too convoluted here, but here goesin the form, they select the expiry from a drop down or whateveruse [url=http://nz.php.net/manual/en/function.strtotime.php]strtotime()[/url] to convert to unix timestamp and add the timeeg: strtotime("+ 6 hours"); or strtotime("+ " . $_POST['expiry_time'] . " hours");and use [url=http://nz.php.net/manual/en/function.date.php]date()[/url] to get it back into the correct format$expiry_datetime = date("Y-m-d G:i:s", strtotime("+ 6 hours")); Quote Link to comment Share on other sites More sharing options...
vigiw Posted September 26, 2006 Author Share Posted September 26, 2006 Ok thank you!!One issue-- this is working but I think this is the line that needs to be changed:$expiry_datetime = date("Y-m-d G:i:s", strtotime("+ 6 hours"));That works, it makes the expire time +6 hours, but how can I make it not be 6, but whatever the user defines in the field?Thanks again! Quote Link to comment Share on other sites More sharing options...
sanfly Posted September 26, 2006 Share Posted September 26, 2006 Something along the lines of the highted text[quote author=sanfly link=topic=109611.msg442031#msg442031 date=1159308364]eg: strtotime("+ 6 hours"); or [color=red]strtotime("+ " . $_POST['expiry_time'] . " hours");[/color][/quote] Quote Link to comment Share on other sites More sharing options...
Fehnris Posted September 26, 2006 Share Posted September 26, 2006 Hi Vigiw,First off, do you know if your web hosting plan offers cron jobs? If you have access to website settings using cPanel then you usually have an icon called 'Cron Jobs'. Not sure if you know what this is so ... basically it is a way to automate script execution on your website.It would be possible to write a simple PHP script that you could get to execute on a given minute/hour/day by setting up a cron job with details of when you want the script to execute. You could set up a cron job to execute every 30 minutes or less if you wanted.The other issue with your users entering a number instead of an expiry date could be easy to do aswell. What I would probably do though is still keep the expiration date field the same in the database rather than add a new field for a number of 'hours'. Would mean your existing records wouldnt have to be changed to include a value for the numbers field.If you were to add something like the following to your form processing page it would give you both a form post date and an expiry date in mysql timestamp format.Having the user only enter a number instead of an expiry date though does mean you would need to add a bit more PHP coding to your file that processes the form and enters the data into the database. Wouldnt be to hard to convert the date they posted the form + number of hours into an expiration date before entering the expiration date into the database.[code]$hours = $_REQUEST['hours']; //the input hours field from the form of the calling page.if ($hours == ""){$hours = 0; //checking for an empty string input - the user just submitted the form without specifying a value for hours.}$postdatetimestamp = time(); //creates UNIX timestamp for post date (current time).$expiretimestamp = $postdatetimestamp + ($hours * 3600); //creates UNIX timestamp for post date plus so many hours (expiry time).$postdatemysqltimestamp = date("Y-m-d H:i:s", $postdatetimestamp); //formats UNIX timestamp to MYSQL timestamp for post date.$expiremysqltimestamp = date("Y-m-d H:i:s", $expiretimestamp); //formats UNIX timestamp to MYSQL timestamp for expiry date.[/code]..... connection to MYSQL database to add record including '$postdatemysqltimestamp' as the Post Date and '$expiremysqltimestamp' as the Expiry Date.Hope all that helps you :) Quote Link to comment Share on other sites More sharing options...
vigiw Posted September 27, 2006 Author Share Posted September 27, 2006 Thanks for all of your help, everyone! I figured out how to do the hour thing. I based it off of sanfly's script, I just had to adjust some things. ;DBut, yes, my server does allow Cron Jobs. However, I have never used them and do not know how to. Unfortunately my control panel is really not extensive. My CP is vDeck and when I click Cron Jobs it tells me to type in the script path from /home and it allows me to select a day the script can run and the time. For days, I can click all, but for times I can only select one time.I would be interested in erasing all old data as soon and as much as possible. Could you please explain to me how this can be done-- to a beginner? :-[Thanks! ;D Quote Link to comment Share on other sites More sharing options...
Fehnris Posted September 27, 2006 Share Posted September 27, 2006 Sure.Not sure what options you have with vDeck but with cPanel you have fields to select 'Minute', 'Hour', 'Day', 'Month', 'Weekday'. How does vDeck let you enter the time? Sorry, I have had no experience with vDeck.usually with cron jobs you have 5 characters representing the values of the fields about for example:-minute hour day month weekday * * * * *having a cross for each of these would mean the script would run every minute of every hour of every day. To have it run every hour, on the hour you would specify 0 * * * *.Once you have the cron sorted out all you would need to keep on top of your old records would be a php script to access the MYSQL database and remove any records that have an expiry date older than the current date. Something like:-[code]<?$currentdate = date("Y-m-d H:i:s");$databaseconnection = mysql_connect ("$HOST", "$USERNAME", "$PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());mysql_select_db ("$DATABASENAME");$querystring = "DELETE from <Database Table> where <expiry date field name> <= '$currentdate'";$result = mysql_query("$querystring");?>[/code]Upload your new PHP script file to your webspace and enter the path to it under the cron job path that you setup. That should be it. Quote Link to comment Share on other sites More sharing options...
vigiw Posted September 27, 2006 Author Share Posted September 27, 2006 For Cron Jobs for "Hour" I am able to select an hour from 0-23, there is no "all". There is no "minute" field. There is only hour and day by day of week. Monday-Sunday, with an additonal "all". So the most I can do it is every 24 hours. >:(Is there a way to do it other than using the control panel?Also:When I edit the necessary field names and database login information below, will this be all I need for the script file to run properly?<?$currentdate = date("Y-m-d H:i:s");$databaseconnection = mysql_connect ("$HOST", "$USERNAME", "$PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());mysql_select_db ("$DATABASENAME");$querystring = "DELETE from <Database Table> where <expiry date field name> <= '$currentdate'";$result = mysql_query("$querystring");?>Thanks! Quote Link to comment Share on other sites More sharing options...
Fehnris Posted September 27, 2006 Share Posted September 27, 2006 Im not sure of other ways to enter a cron job. You could message your web hosting provider to ask if they offer a way to select more options.That script should be all you would need to delete any records with an expiry date/time older than the current date/time. Quote Link to comment Share on other sites More sharing options...
vigiw Posted September 27, 2006 Author Share Posted September 27, 2006 Since Crons may not be enough.. is there a way to make this file available in public_html so I can run it in my browser? I tried that but it is just a blank page.Suggestions?Thanks! Quote Link to comment Share on other sites More sharing options...
Fehnris Posted September 27, 2006 Share Posted September 27, 2006 You are correct in that you can run it yourself and chances are, the script worked when you ran it in your browser. The reason you get a blank page is because the script doesnt output anything. You could do a check that its working by querying your database to find entries that the expiry date is older than the current date. Then run the script from your browser.Finally check the database to see if the records, found previous to running the script, have been erased. Quote Link to comment Share on other sites More sharing options...
vigiw Posted September 27, 2006 Author Share Posted September 27, 2006 Ok I'll see if it works....Also,Is there a way to edit my submit file so I can also have a minutes field?Here is the beginning of my code for my submit file, I am only going to display the relevant lines:[code]strtotime("+ " . $_POST['field5'] . " hours");$field5 = date("Y-m-d G:i:s", strtotime("+ " . $_POST['field5'] . " hours"));[/code]field5 is the field that the user enters the amount of hours in and has the expiration date/time/etc. in it.Thank you! Quote Link to comment Share on other sites More sharing options...
Fehnris Posted September 27, 2006 Share Posted September 27, 2006 [code]strtotime("+ " . $_POST['field5'] . " hours " . $_POST['<name of your minutes field>'] . "minutes");$field5 = date("Y-m-d G:i:s", strtotime("+ " . $_POST['field5'] . " hours " . $_POST['<name of your minutes field>'] . "minutes"));[/code]should do the trick. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.