Auto delete function

trawets

hi 🙂

I hope this is the correct place to post this question

I run the following script from cron on daily basis which archives my news items on a joomla based web page.

<?php 
// Check to see if content items need
// to be archived
// Loop through content table and 
// check to see if publish_down date is
// equal to current date

// Get connection information
include("mysql-info.php");

// Local Variables

// Get current Date and set
// to $currentDate
$currentDate = date( 'Y-m-d' );

// Connection information
$connection = mysql_connect( $server, $username, $password ) 
or die(mysql_error());

// Set database name
$db = @mysql_select_db( $database, $connection ) or
die(mysql_error());

// Select Statment to get content id and expiry date
// associated to that specific content
$sqlSelect = "SELECT id, publish_down FROM $table WHERE state = 1"; 

// Make sure connection was made
if ( $connection ) 
{
   // Store result of query
   $resultSelect = @mysql_query( $sqlSelect, $connection ) or die(mysql_error());

   // Loop through results
   while ( $row = mysql_fetch_array($resultSelect) ) 
   {
     // Get Database Row Values
     $contentId = $row['id'];
     $expiryDate = $row['publish_down'];

     // Split date so we can compare against
     // the date format: Y-m-d
     $array = split( " ", $expiryDate );

     // Check to see if expiry date is
     // equal to current date.  If so
     // then set state to -1
     if ( ( $array[0] != '0000-00-00' ) && ( strtotime($array[0]) <= strtotime($currentDate) ) )
     {
       // Issue Update query to update state to -1
       $sqlUpdate = "UPDATE $table SET state = -1 WHERE id = " . $contentId;

       // Issue Query
       $resultUpdate = @mysql_query( $sqlUpdate, $connection ) or die(mysql_error());

       // Check to see if insert statement worked
       if ( !( $resultUpdate ) )
       {
         echo "Error updating state status!";
       }
       else if ( $resultUpdate )
       {
         echo "Content ID: $contentId was archived!";
       }
     }
    }
}
{
  // Database Connection Failed
  echo "Connection Failed!";
}
?>

I am sure you will guys will know that this unpublish's content, I would if possible like to add an delete function, something along the lines of

$delday = date("Y-m-d", publish_down("-90 days")); 
mysql_query("DELETE FROM table WHERE date = '$delday'") or 
die(mysql_error());

which would allow me to delete from the db content that has been archived for 90 days or what ever time I choose
and the long and short of this question is how do I do this?, I don't know how. I understand there will be some conflicting code , I can follow what the code is doing but unfortunately I don't know what I need to change.
I have spent sinificant time searching for this or even a example script
ThankX

pete_bisby

I've never used joomla, so pardon my ignorance ....

So setting the state status to -1 will un-publish a news article. The code itself is very inefficient, because you're checking the date on every record. If your table has 10,000 records, that is 10,000 reads plus any updates it requires to un-publish news articles. It would be easier/quicker to perform an UPDATE query where the publish_down date is equal to today:

$strQuery = "UPDATE $table SET state=-1 WHERE state=1 AND publish_down=CURRENT_DATE";

You can then add a DELETE query to reomve all un-published articles that are older than 90 days:

$strQuery = "DELETE FROM $table WHERE state=-1 AND DATE_ADD(publish_down, INTERVAL '90' DAY) = CURRENT_DATE";

This would make you code run more quickly and efficiently, certainly if you have a lot of articles stored in the table.

trawets

Hi
Thanks for the reply

So setting the state status to -1 will un-publish a news article

as far as my understanding of the joomla content table and the script goes yes that is the case

The code itself is very inefficient, If your table has 10,000 records, that is 10,000 reads plus any updates

This is one of the main reasons I would like to trim the size of the content table, the site has been running for about 2 years and I have some 80,000 + records the majority of which haven't been and never will be read.

As for your suggestions I will try all this out on a back up site and let you know if I manage to delete the complete db :eek: or not, these things are a bit spooky
again thankX for your interesting and knowledgeable reply

trawets