How to automatically delete records by date every night??
Posted 03 July 2006 - 11:27 PM
PHP newbie here. I am building an event listing service and would like to have records removed from the database after they have passed. I am assuming I will need to run a CRON job on the server every night, but what sort of PHP script will I need to write?
I also think that in the interest of safety, I would like to have the old records moved to another database first and then deleted every month. Can I set up something like this to run automatically??
Any suggestions will be greatly appreciated.
Posted 04 July 2006 - 02:05 AM
<? $today = date("YmdHi", time()); //or whatever date format you use include('connectionStringInfo.php'); mysql_query("DELETE FROM events WHERE eventDate < '$today' "); mysql_close(); ?>
then just set up a CRON to run every night around midnight. say, 23:59 or so.
Posted 29 August 2006 - 06:52 PM
Does anyone have an idea why this might be?? I have called my host and they say everything looks 'good' so basically they are no help to me. Hopefully someone here can help.
Posted 29 August 2006 - 06:56 PM
There's "something" wrong with the script you're using. Add some error_reporting() to it, add some mysql_error() reporting to it and/or post the script here - without revealing your database connection details.
Posted 29 August 2006 - 07:15 PM
Here's my script:
<?php // set varibles for todays date $today = date('Y-m-d'); // include connection info include('Connections/myConnections.php'); //select records to delete mysql_query("DELETE FROM events_music WHERE event_date < '$today' "); // close connection to db mysql_close(); ?>
Real simple. Just pulling some records from the DB based on a MySQL formatted date. I am running PHP/MySQL 5 on my local machine and the servier is running PHP4.4 and MySQL 4.1.20-standard-log with Apache version 1.3.37 (Unix) where I am running Apache 2.0.x under windows. As far as I can tell, this code is compliant.
Could you point me to some info where I could add some error reporting (sorry I am pretty new at this)?
Posted 29 August 2006 - 07:20 PM
<?php // set variables for todays date $today = date('Y-m-d'); // include connection info include('Connections/myConnections.php'); //select records to delete $query = "DELETE FROM events_music WHERE event_date < '$today' "; $result = mysql_query($query) or die("Error: ". mysql_error(). " with query ". $query); // report errors // close connection to db // mysql_close(); // not needed, connection closes when script ends ?>
Posted 29 August 2006 - 08:08 PM
now I get:
Error: No database selected with query DELETE FROM events_music WHERE event_date < '2006-08-29'
doesn't this usually mean I have a prob in the connection string? I can't seem to figure that out if it is the case since all my other Admin pages use the same connection.
Any other Ideas what that could mean?
Posted 29 August 2006 - 08:39 PM
Check if the path to your myConnections.php file is right and/or if it even exists.
If it does exist and the path is right, try to post the script here BUT CHANGE the password, username and name of your database ( replace them by xxxx )
Posted 29 August 2006 - 08:51 PM
Posted 29 August 2006 - 10:41 PM
$hostname_myconnection = "localhost";
$database_myconnection = "xxxxxxxx_xxxxx";
$username_myconnection = "xxxxxxxx_xxxxx";
$password_myconnection = "xxxxxxxx";
$myconnection = mysql_pconnect($hostname_myconnection , $username_myconnection , $password_myconnection ) or trigger_error(mysql_error(),E_USER_ERROR);
I am not having any other issues with pages that use this script, so I am totally lost. Also,The mixed case example I had above was something I had done just for the example. I am not using mixed case except for the connections directory folder (which again, no probs with the other 40 scripts that use it). All path names look correct and do exist. Thanks for the help!!
Posted 29 August 2006 - 11:18 PM
Here what I did - when I added the mysql_select_db to the connection script it gave all sorts of errors (on other oages using that connection script) so I just added it to the CRON script and presto! It works!!
I think I have been relying on DreamWeaver to do too for me. Thanks Gentlemen for your assistence!
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users