How to automatically delete records by date every night??

[sivarts]

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.

[micah1701]

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 use
include('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.

[sivarts]

Thanks, micah1701. I appreciate the help.

[sivarts]

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.

[AndyB]

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.

[sivarts]

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 db
mysql_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)?

[AndyB]

[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]

[sivarts]

You are so right, AndyB
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?

[joking]

Error: No database selected with query DELETE FROM events_music WHERE event_date < '2006-08-29

Hi 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

[AndyB]

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

[sivarts]

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!!

[AndyB]

I don't see a database selection made in there anywhere ...

mysql_select_db($database_myconnection);

[sivarts]

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!