Cron Job to check the creation date of a MySQL Database then delete it

[spinner0205]

I need to know what command to use for a cron job that checks the creation date of a database entry and if it is older than 30 days, it is removed. How can this be done? Also, I would need to know what to put in the php form to add in the creation date automatically to the database when the user submits it.

 

Thanks in advance! 

[spinner0205]

Here is the form part of the index.php file:

<form enctype="multipart/form-data" action="scripts/admin_save_sm_db.php" method="post">

			<h3><i>Auth. Type</i></h3>
                <input name="authtype"/><br />
                <div class="form_help">Must be 'steam' or 'ip'</div><br />

			<h3><i>Steam ID or IP Address</i></h3>
			<input name="identity" /><br />
			<div class="form_help">Steam ID or IP Address of the player you wish to add. <br />
                STEAM ID must be in the format STEAM_0:X:XXXXXXXX. Visit www.steamidfinder.com</div><br />

			<h3><i>Admin Flags</i></h3>
                <input name="flags"/><br />
                <div class="form_help">Must include 'o' for donator and any other flags applicable, no spaces.</div><br />

			<h3><i>Name</i></h3>
                <input name="name" value="Donator"/><p>
                <div class="form_help">Name of the new admin. Optional</div><br />

			<h3><i>Immunity Level</i></h3>
                <input name="immunity" value="0"/><p>
                <div class="form_help">Immunity level between 0 and 99.</div><br />

			<h3><i>User Email Address</i></h3>
			<input name="user_email"/><br />
			<div class="form_help">The email address of the user for security purposes.</div><br />
                
			<input name="Submit" type="submit" value="Submit" class="form_submit" />
		</form>

 

And the admin_save_sm_db.php file which performs the actions:

<?php
session_start();
if(!isset($_SESSION["username"]))
{
   header('Location: login.php');
   exit;
}
@require("../sm_admin_db.php");
$authtype = $_POST['authtype'];
$identity = $_POST['identity'];
$flags = $_POST['flags'];
$name = $_POST['name'];
$immunity = $_POST['immunity'];
$user_email = $_POST['user_email'];
$expire = $_POST['expire'];
$link = mysql_connect(_HOST,_USER,_PASS);
mysql_select_db(_DB);
$sql = mysql_db_query(_DB,"INSERT INTO "._TBL." (`authtype`, `identity`, `flags`, `name`, `immunity`, `user_email`) VALUES ('$authtype', '$identity', '$flags', '$name', '$immunity', '$user_email')") OR die(mysql_error());
$okay = mysql_affected_rows();
if($okay > 0) {

header( "Location: ../index.php?value=success" );

} else {

header( "Location: ../index.php?value=fail" );
}
mysql_close($link);
?>

[spinner0205]

Bump, any ideas?

[tomfmason]

A simple shell script could be something like this

#!/usr/bin/env bash
#get the unix timestamp from 30 days ago
ENDTIME=`date --date="30 days ago" +%s`
#delete all records that are 30 or more days old. 
mysql -u user -p'password' -e "DELETE FROM your_table WHERE UNIX_TIMESTAMP(date_field) <= $ENDTIME;"

 

You will obviously need to replace "your_table" and "date_field" with the relevant table name and date field.

 

Be careful with the above script as it will delete all records that are 30 or more days old. You may want to edit the where clause to suit your specific needs.