sivarts Posted July 3, 2006 Share Posted July 3, 2006 Hi,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. Quote Link to comment Share on other sites More sharing options...
micah1701 Posted July 4, 2006 Share Posted July 4, 2006 you just need a very simple PHP page, probably with just a single query on it. something like:[code]<?$today = date("YmdHi", time()); //or whatever date format you useinclude('connectionStringInfo.php');mysql_query("DELETE FROM events WHERE eventDate < '$today' ");mysql_close();?>[/code]then just set up a CRON to run every night around midnight. say, 23:59 or so. Quote Link to comment Share on other sites More sharing options...
sivarts Posted July 5, 2006 Author Share Posted July 5, 2006 Thanks, micah1701. I appreciate the help. Quote Link to comment Share on other sites More sharing options...
sivarts Posted August 29, 2006 Author Share Posted August 29, 2006 I've done what micha1701 suggested and it works great on my local machine however, I cannot get it to run on the server usuing CRON or even my running the script from my browser. 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. Quote Link to comment Share on other sites More sharing options...
AndyB Posted August 29, 2006 Share Posted August 29, 2006 If it won't run when addressed directly, we know why it won't run as a cron task.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. Quote Link to comment Share on other sites More sharing options...
sivarts Posted August 29, 2006 Author Share Posted August 29, 2006 You'll have to forgive me. I am fairly new to the whole new to the whole world of PHP (starting to love it though)Here's my script:[code]<?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 dbmysql_close();?>[/code]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)? Quote Link to comment Share on other sites More sharing options...
AndyB Posted August 29, 2006 Share Posted August 29, 2006 [code]<?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?>[/code] Quote Link to comment Share on other sites More sharing options...
sivarts Posted August 29, 2006 Author Share Posted August 29, 2006 You are so right, AndyBnow 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? Quote Link to comment Share on other sites More sharing options...
joking Posted August 29, 2006 Share Posted August 29, 2006 Error: No database selected with query DELETE FROM events_music WHERE event_date < '2006-08-29Hi sivarts,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 )JoKinG Quote Link to comment Share on other sites More sharing options...
AndyB Posted August 29, 2006 Share Posted August 29, 2006 Personally, I'm always wary of mixed case folder names or script names. They work fine with Windows which is case-insensitive but *nix systems are case-sensitive so myConnections.php is not the same as myconnections.php Quote Link to comment Share on other sites More sharing options...
sivarts Posted August 29, 2006 Author Share Posted August 29, 2006 Here's the connection info:<?php# FileName="Connection_php_mysql.htm"# Type="MYSQL"# HTTP="true"$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!! Quote Link to comment Share on other sites More sharing options...
AndyB Posted August 29, 2006 Share Posted August 29, 2006 I don't see a database selection made in there anywhere ... mysql_select_db($database_myconnection); Quote Link to comment Share on other sites More sharing options...
sivarts Posted August 29, 2006 Author Share Posted August 29, 2006 You're right. That really confuses me. 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! 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.