Comapre directory contents to DB query result?

[Pikachu2000]

In a nutshell, I need to query a db column containing image multiple file names per field, then compare them. The image file names are stored in the db as a pipe-delineated string. If a file exists in the images directory, but is not in the db query result set, it should be deleted. This doesn't need to happen on a schedule or anything like that.

Here is what I have so far, but I'm not quite there and could use a little help. There are echo() and var_dump() statements in the code for debugging, but the output volume is pretty large. I can post it if anyone thinks it will help. The result I'm getting is that the array_diff() result is not a true reflection of the differences between the db and the directory. It seems every file is listed in the array_diff() result, so I haven't tested any type of actual unlink() statement yet.

<?php
require("conn.php");

if($handle = opendir('images/'))
	{
	echo("Directory handle: $handle \n");
	echo("Files: <br>\n");
	$num = 0;
	while( false !== ($file = readdir($handle)))
		{
		if($file != "." && $file != "..")
			{
			$filenames[] = $file;
			echo("$file <br>\n");
			echo( $num );
			}
		$num ++;
		}
	var_dump( $filenames );
	echo("<br><br>");
	closedir($handle);
	}
$q1 = "select image from listings where (image is not NULL)";
$r1 = mysql_query($q1);
if(mysql_num_rows($r1) > 0)
{
while($a1 = mysql_fetch_array($r1))
	{
	$imarray[] = $a1[image];
	}
	$result = array_diff($imarray, $filenames);
}
	echo("\$a1= :<br>");
	var_dump($a1);
	echo("<br><br>");
	echo("Result of var_dump array_diff= <br>\n");
	var_dump($result);
	echo("<BR><BR>result of print_r(\$result)<br><br>");
	print_r($result);

?>

[bpat1434]

Would it not be easier to reverse that? Query the database first, then use [man]file_exists/man to check if the file is in the specified path, if not add the row ID to the delete query, otherwise move on...

<?php
require("conn.php"); 
$path = './images/';  // Path to images (if needed)

$query = "SELECT id, image
FROM `listings`
WHERE (image IS NOT NULL)";
$result = mysql_query($query);

if(!$result)
  die('No result set returned.');

while($row = mysql_fetch_array($result))
{
  $files = explode('|', $row['image']); // Get all the images separated file1|file2|file3

  foreach($files as $file)
  {
    if(!file_exists($path.$file))
        $del[] = $file;
  }
  if(count($files) == $count($del))
    $sql = "DELETE FROM `listings` WHERE id='".$row['id']."'";
  else if(count($del) >=1)
  {
    $keep = array_diff($files, $del);
    $sql = "UPDATE `listings` SET `image`='" . implode('|', $keep) . "'";
  }

  $rslt = mysql_query($sql);
  if(mysql_affected_rows($rslt) ==0)
    echo 'Failed DB update. '.mysql_error();
  mysql_free_result($rslt);
}

?>

That would seem to work better wouldn't it?

Edit I had the wrong function there by accident, and I didn't use the $sql var... so now it's the right function and I use the $sql var 😉

[Pikachu2000]

I think I may have over complicated in the OP. What I am trying to accomplish is to delete any files from the images directory which aren't referenced in the image fields of the db. Kind of as an alternate directory cleanup method in case of script failure, db data corruption or other unforseen issues.

In other words, if the filename doesn't exist in the db query result, it should be rm'd from the directory.