Jump to content


Photo

How to automatically delete records by date every night??


  • Please log in to reply
12 replies to this topic

#1 sivarts

sivarts
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 03 July 2006 - 11:27 PM

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.


#2 micah1701

micah1701
  • Members
  • PipPipPip
  • Advanced Member
  • 613 posts
  • LocationEllington, CT USA

Posted 04 July 2006 - 02:05 AM

you just need a very simple PHP page, probably with just a single query on it.

something like:
<?
$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.
"Confidence in the face of risk."

#3 sivarts

sivarts
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 05 July 2006 - 09:38 PM

Thanks, micah1701. I appreciate the help.

#4 sivarts

sivarts
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 29 August 2006 - 06:52 PM

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.

#5 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 29 August 2006 - 06:56 PM

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.
Legend has it that reading the manual never killed anyone.
My site

#6 sivarts

sivarts
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 29 August 2006 - 07:15 PM

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:

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

#7 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

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

Legend has it that reading the manual never killed anyone.
My site

#8 sivarts

sivarts
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 29 August 2006 - 08:08 PM

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?

#9 joking

joking
  • Members
  • PipPip
  • Member
  • 29 posts
  • LocationLebanon

Posted 29 August 2006 - 08:39 PM

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
Just JoKinG

#10 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 29 August 2006 - 08:51 PM

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
Legend has it that reading the manual never killed anyone.
My site

#11 sivarts

sivarts
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 29 August 2006 - 10:41 PM

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

#12 AndyB

AndyB
  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 29 August 2006 - 10:53 PM

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

mysql_select_db($database_myconnection);
Legend has it that reading the manual never killed anyone.
My site

#13 sivarts

sivarts
  • Members
  • PipPip
  • Member
  • 22 posts

Posted 29 August 2006 - 11:18 PM

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!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users