How can I check that the file is present in database entry and also in files folder?

Question

I want to write a script where I want to check whether the files are present in files folder of drupal/sites/default/files and is it existing in the database table file_managed.

        $sql=db_query("SELECT * FROM `file_managed` WHERE `filename`LIKE '%.pdf' OR `filename`LIKE '%.doc' OR `filename` LIKE '%.doc' AND `timestamp` < UNIX_TIMESTAMP('2014-01-01')")->fetchAll();

function file_destination($destination, $replace) {

  if (!file_destination($uri, FILE_EXISTS_ERROR)) {
    switch ($replace) {
      case FILE_EXISTS_REPLACE:
        // Do nothing here, we want to overwrite the existing file.
        break;

      case FILE_EXISTS_RENAME:
        $basename = drupal_basename($destination);
        $directory = drupal_dirname($destination);
        $destination = file_create_filename($basename, $directory);
        break;

      case FILE_EXISTS_ERROR:
        // Error reporting handled by calling function.
        return FALSE;
    }
  }
  return $destination;

i am not able to find any way to get the fields delete.

Answer 1

You can use file_destination() drupal function to check if file already exists in sites/default/files with second parameter as FILE_EXISTS_ERROR.

if (!file_destination($uri, FILE_EXISTS_ERROR)) {
  // The file exist
  // Do something
} 

Answer 2

function deleteunrecoredfile()
{
  $results = db_query("SELECT * FROM `file_managed` WHERE `filename`LIKE '%.pdf' OR `filename`LIKE '%.docx' OR `filename` LIKE '%.doc' AND `timestamp` < UNIX_TIMESTAMP('2014-01-01')")->fetchAll();

  foreach ($results as $result) {
        $path =  drupal_realpath($result->uri);
       if(!file_exists($path)) {
          db_delete("file_managed")
              ->condition("fid",$result->fid)
              ->execute();

      }
  }
}