List orphaned and missing media (attachments)

test.php

<?php

require 'wp-blog-header.php';

if(isset($_GET['delete_post'])){
  wp_delete_post($_GET['delete_post']);
  die;
}

?>

<style>body { font-family: monospace; } form { display: inline-block; margin: 0; }</style>

<?php

$query = "

SELECT a.ID as ID, a.post_title as ATTACHMENT, m.meta_value as PATH, a.post_parent as PARENT, p.post_title as OWNER
FROM $wpdb->posts as a
LEFT JOIN $wpdb->posts as p
ON a.post_parent = p.ID
LEFT JOIN $wpdb->postmeta as m
ON a.ID = m.post_id
WHERE a.post_type = 'attachment' AND m.meta_key = '_wp_attached_file'
ORDER BY ID

";
 
$result = $wpdb->get_results($query, OBJECT);

$uploads = wp_get_upload_dir();

$missing = 0;
$orphans = 0;

echo 'Missing    Orphan         Name<br>';

foreach($result as $row){
  ?>
    <?php
      $path = "{$uploads['basedir']}/{$row->PATH}";

      if($is_IIS){
        $path = utf8_decode($path);
      }

      $exists = file_exists($path);
      if(!$exists){
        echo '[x]';
        $missing++;
      } else {
        echo '[ ]';
      }

      echo '        ';
      
      $orphan = $row->PARENT != 0 && !$row->OWNER;
      if($orphan){
        echo "[x]";
        $orphans++;
      } else {
        echo '[ ]';
      }
      
      echo '        ';

      if(!$exists || $orphan){
        echo "<form action=\"?delete_post={$row->ID}\" method=\"post\"><a href=\"?delete_post={$row->ID}\" onclick=\"this.parentElement.submit(); return false;\">[t]</a></form>";
        echo ' ';
      } else {
        echo '    ';
      }

      if($exists){
        echo "<a href=\"{$uploads['baseurl']}/{$row->PATH}\">$row->ATTACHMENT</a>";
      } else {
        echo "<span title=\"{$uploads['baseurl']}/{$row->PATH}\">$row->ATTACHMENT</a>";;
      }

      echo " ($row->ID)";
    ?>
    <br>
  <?php
}

?>

<h1>Missing: <?= $missing ?>, orphans: <?= $orphans ?></h1>