Skip to content

Instantly share code, notes, and snippets.

@adamcrampton
Last active November 21, 2018 23:53
Show Gist options
  • Save adamcrampton/8ccc92ecd79f26e0275672827a7d9529 to your computer and use it in GitHub Desktop.
Save adamcrampton/8ccc92ecd79f26e0275672827a7d9529 to your computer and use it in GitHub Desktop.
Useful WordPress queries for comments table
-- All live comments
SELECT COUNT(*) FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
-- Comment count grouped by author
SELECT COUNT(*) AS comment_count, comment_author, user_id FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
GROUP BY user_id
ORDER BY comment_count DESC
-- Comment count grouped by IP address
SELECT COUNT(*) AS comment_count, comment_author, comment_author_IP FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
GROUP BY comment_author_IP
ORDER BY comment_count DESC
-- Comment count logged in users
SELECT COUNT(*) AS comment_count, comment_author FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
AND user_id != 0
GROUP BY comment_author_IP
ORDER BY comment_count DESC
-- Comment count guests
SELECT COUNT(*) AS count, comment_author FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
AND user_id = 0
GROUP BY comment_author_IP
ORDER BY count DESC
-- Comment count grouped by date
SELECT COUNT(*) AS comment_count, DATE(comment_date) FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
GROUP BY DATE(comment_date)
ORDER BY DATE(comment_date) ASC
-- Comment count grouped by IP address and date
SELECT COUNT(*) AS comment_count, DATE(comment_date), comment_author, comment_author_IP FROM wp_comments
WHERE comment_approved = 1
AND comment_date > '2018-08-22'
GROUP BY DATE(comment_date), comment_author_IP
ORDER BY DATE(comment_date) ASC, comment_count DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment