Jump to content

How to automatically delete records by date every night??


sivarts

Recommended Posts

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

  • 1 month later...
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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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)?
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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!!
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.