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

SELECT post_id, COUNT(*) AS image_count
FROM wp_posts
WHERE post_content LIKE '%[image_href]%'
GROUP BY post_id
ORDER BY image_count DESC;

This query will first select all of the posts in the wp_posts table where the post content contains the [image_href] shortcode. The shortcode is used to insert an image into a WordPress post. The query will then count the number of times each image href is used in the post content. Finally, the query will order the results by the image count in descending order.

To run this query, you will need to connect to your MySQL database and execute the query. You can use a MySQL client such as MySQL Workbench or phpMyAdmin to execute the query.

Answer 2

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>";