Title: IDX Garbage Collection for Beginners
Author: Promethyl
Catagory: Code Which Makes Servers Happy
Date: Tuesday, July 22 2003

Garbage collection is not just for street anymore. Given enough time, eventually your IDX will expand to the point where it will become too large for the server to handle and unmanageable.

While not the most optimized program -- I wrote it under duress -- it provides the bare essence of garbage collection.

The steps involved:

1. Establish a link to the database.
2. Get the tables.
3. Look through the directories.
4. When an image is found:
1. extract the MLS number
2. Find any listings associated with that MLS number.
3. If none exist, delete listing
5. Provide error reporting.
6. Repeat.
7. Provide executive summary of changes.

The example source code below shows this in effect on a linux box in PHP/mySQL: 

<?
// garabage collection / orphan detection and eratication routine

include('db.php');
if (!$db) $db = 'databasename';
$tables = gtables();
$directories = Array(0,1,2,3,4,5,6,7,8,9);
$basedir = '/path/to/images/mls';
$board = 'boardofreators';

main();

function main() {
 global $directories, $sys_total;

 foreach ( $directories as $d ) {
  $gcollected += gcdir( $d );
 }
 echo "\nTotal number of files ".$sys_total.", orphans: ".$gcollected. ".\n";
}

function gcdir($d) {
 $od = $d;
 global $basedir, $board, $sys_total;
 $d = dir($basedir."/".$d);
 //echo "Handle: ".$d->handle."<br>\n";
 //echo "Path: ".$d->path."<br>\n";
 echo "\n\nChecking ".$d->path." for orphans: \n";
 while (false !== ($entry = $d->read())) {
     $f++; // files inc
     if (!stristr($entry,'.jpg')) { continue; }
     $mlsnumber = str_replace('.jpg','',$entry);
     $mlsnumber = str_replace($board,'',$mlsnumber);
     if (!is_numeric($mlsnumber)) { $mlsnumber=substr($mlsnumber,0,strlen($mlsnumber)-1); }
     if (mlsexists($mlsnumber)==0) {
      $orphaned++;
      echo $mlsnumber." ";
          gcpurge($od.'/'.$entry);
     }
 }
 $d->close();

 echo "\n".$f . ' files, ' . $orphaned . " orphans found.\n";
 $sys_total += $f;
 return $orphaned;

}

function mlsexists($mlsnumber) {
 global $tables;
 $mlscount=0;
 foreach ($tables as $t) {
  $result = sql_query('select * from `'.$t.'` where mls='.$mlsnumber. ' limit 1');
  if (mysql_num_rows($result) > 0) { $mlscount++; }
  @mysql_free_result($result);
 }
 return $mlscount;
}

function gtables() {
 global $db;

 $result = mysql_list_tables($db);
   
    if (!$result) { print "DB Error, could not list tables\n";
        print 'MySQL Error: ' . mysql_error();         exit;    }
   
    while ($row = mysql_fetch_row($result)) {
        $tables[] =$row[0];
    }

    mysql_free_result($result);
    return $tables;
}

function gcpurge($f) {
 global $basedir, $debug;
 if ($debug) echo 'Purging '.$f;
 if ( unlink ( $basedir.'/'.$f ) ) { return 1; }
 return 0;
}

?>