Counting Posts per User per Month in WordPress

Had an interesting problem land on my plate today. A corner-office type wanted to know how many posts per month each of our authors had written. Sounds like a round of layoffs are coming, eh?

I hadn’t written any SQL in a LONG time but I managed to finally figure it out. Certainly it was faster than trying to add things up by hand (we have 17 brands we need this data for). Figured I’d save the queries in case I ever need them again. I am not saying this is the most efficient way to do this — I’m no DBA — but for a 1 time thing, it worked.

SELECT t.year_month, users.user_nicename, users.user_email, count(post_author)
SELECT DISTINCT(EXTRACT(YEAR_MONTH FROM `post_date`)) as `year_month`
FROM wp_posts
WHERE post_date > "2021-01-01 00:00:01"
ORDER BY `year_month` ASC
) as t,
wp_users users,
wp_posts posts
posts.post_author = users.ID AND
EXTRACT(YEAR_MONTH FROM posts.post_date) = t.year_month AND
posts.post_status = 'publish' AND
posts.post_type IN ('post', 'company', 'download', 'event', 'page', 'podcast', 'product', 'webcast')

GROUP BY posts.post_author, t.year_month
ORDER BY posts.user_nicename, t.year_month ASC

In my case the request was for the past 2 years, so we’re starting at 2021-01-01.

The EXTRACT(YEAR_MONTH FROM `post_date`) was a new syntax for me.

So the posts.post_type IN line will have to be customized for your site; it depends on what content types you want to count. For this site we have a few custom content types. For a “vanilla” wordpress site IN (‘post’, ‘page’) should work.

This query sorts by author first, so you have 24 (since I’m grabbing 24 months of data) rows for the first user, then 24 for the next, and so on.

SELECT t.year_month, users.user_nicename, users.user_email, count(post_author)
SELECT DISTINCT(EXTRACT(YEAR_MONTH FROM `post_date`)) as `year_month`
FROM wp_posts
WHERE post_date > "2021-01-01 00:00:01"
ORDER BY `year_month` ASC
) as t,
wp_users users,
wp_posts posts
posts.post_author = users.ID AND
EXTRACT(YEAR_MONTH FROM posts.post_date) = t.year_month AND
posts.post_status = 'publish' AND
posts.post_type IN ('post', 'company', 'download', 'event', 'page', 'podcast', 'product', 'webcast')

GROUP BY t.year_month, posts.post_author
ORDER BY t.year_month ASC

Same data, different format. This one orders by date, so you get a row for each author for Jan 2021, then a row for each author for Jan 2022 and so on.

Building a Feed from WordPress’s REST API

For reasons not worth elaborating on, I was tasked with building a feed based on WordPress’s REST API (as opposed to pulling in an RSS feed). This was a new experience for me.

The default URL to grab posts is /wp-json/wp/v2/posts, so for example

That is sufficient if you just want the text of the posts you want to pull in, but you probably want some kind of images too, right? Perhaps whatever you’re using for a thumbnail. I don’t actually use featured images here so this blog is a terrible example, but suffice to say that /wp-json/wp/v2/posts will pull in links to the attachment page, but not the actual image URLs.

So you’ll find something like wp:featuredmedia blah blah href:

You could then do another call to that URL and get all the data, including the URL to the image, but there’s a slightly easier way: append ?_embed to the end of your URL:

Now you’ll have an _embedded node in your JSON that holds what you need, but gotta dig down a few levels
_embedded -> wp:featuredmedia -> 0 -> media_details -> sizes -> thumbnail {for example} -> source_url

If you’re like me, some of those special characters will throw you off. Here is a working example:

$mediaUrl = $obj->_embedded->{'wp:featuredmedia'}[0]->media_details->sizes->{'post-thumbnail'}->source_url;

$mediaURL now holds the URL to your thumbnail image.

$obj is the json object. In this case I was grabbing a custom image size called post-thumbnail

Putting wp:featuredmedia inside quotes inside curly braces was new to me.

Everything else is pretty easy. $obj->link for the URL of the post, $obj->title->rendered for the post title, $obj->content->rendered for the post content, and so on.

I highly suggest using Firefox to look at these json files since it formats stuff for you. Or snag a json viewer extension for your favorite browser.

By the way, by default you’ll get 1 page of 9 posts. You can control how many posts you get by appending a per_page value.

So say I wanted the most recent 3 posts (and I want the embed stuff):

Here’s the full documentation for /posts:

Adding Fonts to the Divi Builder Menu

The company I work for has been migrating a lot of sites to Divi, the WordPress Theme. I’m not really a fan of Divi but I also wasn’t in the meetings when the decision was made so… I’ve had to start learning Divi.

The other day a designer asked about using custom fonts on a site. In this case it was a TrueType Font. I uploaded the actual file to the server and added the font family via CSS:

@font-face {
font-family: 'KansasNew-Regular';
src: url('/wp-content/themes/divi-child/assets/fonts/KansasNew-Regular.ttf');

So basic stuff, but the user wanted to be able to pick the font from the font drop down in the Divi Builder:

Divi Builder Font Selector

This was trickier but after some searching I found the solution at Ville Teikko’s site. As is so often the case with WordPress it comes down to using a filter. Teikko says you need to bundle your font into a web-font kit but, maybe since my needs were very simple (a single typeface) I got away with not doing that.

Here’s the filter that worked with the CSS above:

add_filter('et_websafe_fonts', 'load_divi_custom_font', 10, 2);

function load_divi_custom_font($fonts) {

$custom_font = array('KansasNew-Regular' => array(
'styles' => '400,700',
'character_set' => 'latin',
'type' => 'serif',
'standard' => 1

return array_merge($custom_font,$fonts);

Knock on wood, so far it seems to be working. I’ll update the post if we run into any issues over time.

Controlling load order of enqueued WordPress scripts and stylesheets

Another in a long line of stuff that is easy to do once you find out how to do it.

I was working on a WordPress theme and I wanted to incorporate UI Kit into this theme. I was trying to be a good WordPress citizen and so was enqueueing both UI Kit’s CSS and my main CSS. I’m honestly not convinced this is necessary for a theme you’re building for a specific site but what the heck.

My concern was, UI Kit has a lot of default styles that I was intending to over-ride [we weren’t organized enough for me to go with a custom UK Kit theme] in my style.css file, so it was important that style.css get loaded last.

So, we need to do two calls for the enqueueing. First we load up UI Kit’s CSS and javascript files:

function mytheme_scripts()
// UI Kit
wp_enqueue_style('uikit', get_template_directory_uri() . '/css/uikit.min.css');
wp_enqueue_script('uikit', get_template_directory_uri() . '/js/uikit.min.js');
wp_enqueue_script('uikit_icons', get_template_directory_uri() . '/js/uikit-icons.min.js');

add_action( ‘wp_enqueue_scripts’, ‘mytheme_scripts’ );

Then we make a second add_action call to enqueue style.css, Note the trailing 99 in the add_action. That represents the priority, or order files will be loaded. The default is 10. 99 might be overkill but if we ever need to add something between the two we have ample ‘room’ to do so.

add_action('wp_enqueue_scripts', function(){
wp_enqueue_style('main-style', get_template_directory_uri() . '/style.css');
}, 99);

If we view source of the page it looks like this. WordPress still prioritizes style sheets before js, so style.css gets included after UI Kit’s css but before UI Kit’s javascript:

<link rel='stylesheet' id='uikit-css' href='https://dev.sample.local/wp-content/themes/sample/css/uikit.min.css?ver=5.3.3' type='text/css' media='all' />
<link rel='stylesheet' id='main-style-css' href='https://dev.sample.local/wp-content/themes/sample/style.css?ver=5.3.3' type='text/css' media='all' />
<script type='text/javascript' src='https://dev.sample.local/wp-content/themes/sample/js/uikit.min.js?ver=5.3.3'></script>
<script type='text/javascript' src='https://dev.sample.local/wp-content/themes/sample/js/uikit-icons.min.js?ver=5.3.3'></script>

Simple enough. One of those things where it took me longer to realize I could add a priority to add_action than it did to make the change.

One thing to try when Wordfence breaks your server

I opened up my local copy of one of our sites today and… white screen. WTF, it was working fine the last time I used it. I hit reload and now I’m getting a server not found error. That’s weird.

So I check the log files and see this:

[Mon May 07 15:09:14 2018] [notice] Child 167276: Starting thread to listen on port 80.
[Mon May 07 15:09:16 2018] [notice] Parent: child process exited with status 255 — Restarting.
[Mon May 07 15:09:17 2018] [notice] Digest: generating secret for digest authentication …
[Mon May 07 15:09:17 2018] [notice] Digest: done
[Mon May 07 15:09:19 2018] [notice] Apache/2.2.31 (Win32) DAV/2 mod_ssl/2.2.31 OpenSSL/1.0.2e mod_fcgid/2.3.9 mod_wsgi/3.4 Python/2.7.6 PHP/7.1.7 mod_perl/2.0.8 Perl/v5.16.3 configured — resuming normal operations
[Mon May 07 15:09:19 2018] [notice] Server built: May 6 2016 10:19:53
[Mon May 07 15:09:19 2018] [notice] Parent: Created child process 50416
[Mon May 07 15:09:20 2018] [notice] Digest: generating secret for digest authentication …
[Mon May 07 15:09:20 2018] [notice] Digest: done
[Mon May 07 15:09:21 2018] [notice] Child 50416: Child process is running
[Mon May 07 15:09:21 2018] [notice] Child 50416: Acquired the start mutex.
[Mon May 07 15:09:21 2018] [notice] Child 50416: Starting 64 worker threads.
[Mon May 07 15:09:21 2018] [notice] Child 50416: Starting thread to listen on port 80.
[Mon May 07 15:09:23 2018] [notice] Parent: child process exited with status 255 — Restarting.

This repeated over and over. Apache was starting but when I attempted to load the site, it would crash. If I tried to load something simple that didn’t involve wordpress, everything worked fine.

Long story short, something had become foobar’d with Wordfence. Not blaming the plugin itself, I think I munged something up when I imported a fresh SQL dump. I usually don’t pull over wordfence directories but maybe I screwed up, who knows?

The fix is simple enough.

Step 1:
Delete everything in \public_html\wp-content\wflogs

Step 2:
Truncate all the wp_wf* tables in your database.

Bam, problem hopefully solved. Wordfence will recreate the files you delete and re-fill that tables as it needs to. And your local will start to work again. Huzzah!

Now if it happens again, you might have a bigger issue. What turned out to be the problem in my case was that Wordfence was exhausting Apache’s memory, essentially. This only happened for me in a MAMP environment; I never ran into the issue with my Docker-based locals.

I fixed it by tweaking the Apache ThreadStackSize by inserting this into http.conf:

ThreadStackSize 8388608

Since I was on MAMP I had to edit a template. See this post for more on dealing with MAMP templates.

Apparently (I read this on the Internet so it must be true) MAMP sets a much smaller default stack size (1 MB) as compared to a typical Linux installation (8 MB). So the above ThreadStackSize pushes the size to the same as you’d find on linux.

Excluding a directory when creating a tar file

So this is a dumb one but it took me longer than expected to find an answer.

So say I want to make a local copy of a wordpress site. I want to tar up all the files to download because downloading the files one by one will take hours and hours.

Assuming a directory structure like


I would normally go to /www and do

tar -zcvf mysite.tar.gz public_html

That gets everything…much faster to ftp.

But on some of our sites, we wind up with a BIG cache and I really don’t need all that stuff. In our case the cache is at:


So how to exclude the cache files? There’s an –exclude flag for tar but it seems finicky. Turns out it has to be the first parameter, at least for me and some others.

So this worked for me:

tar --exclude='public_html/wp-content/cache' -zcvf mysite.tar.gz public_html

This did NOT work for me
tar -zcvf mysite.tar.gz public_html --exclude='public_html/wp-content/cache'
Nor did this:
tar -zcvf --exclude='public_html/wp-content/cache' mysite.tar.gz public_html

–exclude seemed to only work when it came first after the tar command.

Cleaning up a WordPress wp_options table bloated with transients

I’ve got a pair of wordpress sites under my care that have an issue with the wp_options table getting bloated. I mean, really big. Millions of rows for a modest sized site. I suspect it has something to do with the Yoast plugin but I’m not 100% sure. At some point I need to figure out WHY this happens but in the meantime I’ve just been treating the symptoms, so to speak. Every month or so I go in and clean up the tables.

Basically what is happening is that a bunch of transient records aren’t expiring or getting deleted or whatever they are supposed to be doing.

Live example from one of these sites (which, I should add, is running on MySQL)

select COUNT(*) from wp_options

I’ve actually seen it bigger.

So how many of these records are transients?
select COUNT(*) FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%')

So all but 9,771 rows are transients. Even 10K rows seems pretty big for wp_options but for now I’ll pick my battles.

So time to clean up. Step 1 is always to backup the database first, or AT LEAST backup the wp_options table. I prefer to wait to do this late at night or very early in the morning, just in case it impacts performance.

Once you have a backup, do the cleanup
DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%')

Then recheck the count:
select COUNT(*) from wp_options

Much better. And now we’re set for another month or two.

As I said, I’ve been having to do this for a couple sites for a while now, and so far haven’t seen any negative impact from nuking the transients, but as always, I don’t assume responsibility for any damage you cause to your site!