Programming – Ben Lobaugh Online

Find total number of post records on a WordPress Multisite in MySQL

On August 3, 2018

In Computing, MySQL, Programming, Web Development, WordPress

I was recently asked for the total number of posts on a WordPress website. Normally as simple as looking at the number in the dashboard, however in this instance the WordPress install was a Multisite with nearly 100 sites. That would have taken far too long to do manually, but what about having MySQL do the heavy lifting for me?

The information_schema database contains data about every database and table on the server. One of the metrics it stores is the count of rows in a table. We can use that to extrapolate roughly how many entries are in all the posts tables combined. This is not an exact science, however it will get you close to the real numbers. I argue that it will typically be “close enough” on larger installs, such as the one I was looking at.

In the following query, just replace the database name with your database and run it.

SELECT SUM( table_rows )
FROM information_schema.TABLES 
WHERE 
	table_schema = "{{YOUR_DATABASE_NAME}}" 
	AND table_name LIKE '%_posts';

Find and update WordPress posts with comments older than NNN days

By Ben Lobaugh (blobaugh)

On March 12, 2018

In Computing, MySQL, Programming, Web Development, WordPress

I have a use case where I need to disable comments on posts in WordPress where the last comment was more than one year, or 365 days, ago.

Posts can be found with this query:

SELECT p.ID, p.post_title, p.post_date, p.comment_status, p.`ping_status`
FROM wp_posts AS p
WHERE p.ID IN (
     SELECT c.comment_post_ID
     FROM wp_comments AS c
     WHERE DATEDIFF( curdate(), c.comment_date ) <= 365
  );

The outer query takes the inner query as the search parameter. The inner query is finding the latest comment for each post based on the number in the WHERE condition. 365 represents 365 days, or 1 year.

Therefore, this query is getting the latest comment for post where the comment is less than 1 year old.

Now let’s say you need to update the posts to disable comments. The goal is to disable commenting for all posts that have not had a comment within the last year. It can be accomplished with the following:

UPDATE wp_posts
SET comment_status='closed', ping_status='closed'
WHERE ID IN (
		SELECT c.comment_post_ID
		FROM wp_comments AS c
		WHERE DATEDIFF( curdate(), c.comment_date ) >= 365
);

Here again the number 365 represents the number of days.

Check the posts and you should see the comments have been closed.

MySQL: Count number of tables in database

By Ben Lobaugh (blobaugh)

On August 18, 2016

In Computing, MySQL, Programming

If you need to count the number of tables that exist in a MySQL database you can do so with the following query. Just remember to swap out the database name!

SELECT count(*) 
FROM information_schema.tables 
WHERE table_schema = 'YOUR_DB_NAME'

Bash script to automatically convert git submodules to regular files

By Ben Lobaugh (blobaugh)

On August 3, 2016

In Computing, Computing Tips, Git, Linux, OS X, Programming

Git submodules drive me batty! They are a great idea in theory however in practical application they are a pain in the butt to work with.

I have a project that has accumulated over a dozen submodules over the past couple years. Switching branches and merging anything has become excruciating. This morning was the last straw. I WANT THEM GONE! Removing dozens of submodules by hand is time consuming so I tossed together this quick Bash script. I hope it is helpful to anyone else out there struggling with git submodules.

#!/bin/bash

# Get a list of all the submodules
submodules=($(git config --file .gitmodules --get-regexp path | awk '{ print $2 }'))

# Loop over submodules and convert to regular files
for submodule in "${submodules[@]}"
do  
   echo "Removing $submodule"
   git rm --cached $submodule # Delete references to submodule HEAD
   rm -rf $submodule/.git* # Remove submodule .git references to prevent confusion from main repo
   git add $submodule # Add the left over files from the submodule to the main repo
   git commit -m "Converting submodule $submodule to regular files" # Commit the new regular files!
done

# Finally remove the submodule mapping
git rm .gitmodules

Reset all git submodules

By Ben Lobaugh (blobaugh)

On June 10, 2016

In Computing, Git, Programming

Here is a quick snippet that will reset all your git submodules to their most recent commit. Handy when something has happened such as a file permissions change that unintentionally affected the submodules.

git submodule foreach --recursive git reset --hard

Ben Lobaugh Online

I think, therefore I am. I am, therefore I sail

Category: Programming Page 1 of 20

Find total number of post records on a WordPress Multisite in MySQL

Find and update WordPress posts with comments older than NNN days

MySQL: Count number of tables in database

Bash script to automatically convert git submodules to regular files

Reset all git submodules

2018 Vision

2018 Reading Challenge