Mysql query to dermine image use frequency in Wordpress?

Question

Would it be possible to build a mysql query to determine the frequency of an image being used in Wordpress?

So that it extracts and counts different image hrefs from the wp_posts?

Answer 1

thanks for tour quick response. sorry i forgot it makes much more sense to use php:

<?php

error_reporting(E_ERROR | E_PARSE);

$mysqli = mysqli_connect("host", "user", "pass", 'db');

$result = mysqli_query($mysqli, "SELECT id, post_content FROM `wp_posts` ORDER BY ID");

$images=array();
  while ($row = mysqli_fetch_row($result))
  {
    $dom = new domDocument; 
    $dom->loadHTML($row[1]);
    $img = $dom->getElementsByTagName('img');

    if ($img->length>0)
    {
        
        if (isset($images[basename($img[0]->getAttribute('src'))]))
        {
            $images[basename($img[0]->getAttribute('src'))]["count"]++;
            $images[basename($img[0]->getAttribute('src'))]['loc'][]=$row[0];
        }
        else
        {       
            $images[basename($img[0]->getAttribute('src'))]["count"]=1;
            $images[basename($img[0]->getAttribute('src'))]["url"]=$img[0]->getAttribute('src');
            $images[basename($img[0]->getAttribute('src'))]['loc'][0]=$row[0];
        }
    }   
    unset($dom);
  }

arsort($images);

print "<table>";

foreach($images as $index => $image)
{
    print "<tr><td>".$image["count"]."</td>
    <td><a href='".$image["url"]."'>$index</a></td><td>"; foreach ($image["loc"] as $loc) print "<a href='/?p=$loc'>$loc</a> "; print "</td><td><a href='".$image["url"]."'><img src='".$image["url"]."'></a></td></tr>\n";
}
print "</table>";