[How to] Display Number of Posts in WordPress

Using WordPress core function wp_count_posts, you can easily display total number of posts. Here is a simple example on how to use this function:

<?php
	$count_posts = wp_count_posts();
	$published_posts = $count_posts->publish;
	echo 'We published ' . $published_posts . ' posts until now, and it's still growing..';
?>

This displays a text output like this;

We published 1908 posts until now, and it's still growing..

With wp_count_posts you also get other counts like drafts, requested posts etc. Here is a sample output showing what wp_count_posts returns as an Object:

stdClass Object
(
    [publish] => 1908
    [future] => 0
    [draft] => 9
    [pending] => 0
    [private] => 1
    [trash] => 0
    [auto-draft] => 19
    [inherit] => 0
    [request-pending] => 0
    [request-confirmed] => 0
    [request-failed] => 0
    [request-completed] => 0
)

In your theme, you can use any of those properties to display the number of posts.

12+ Useful SQL Queries for WordPress Database Cleanup

If you are using WordPress for a long time, it is possible that you have many spam comments, revisions, transients cache etc. on your wp database. In this post, I listed most useful SQL queries you can use to clean up your WordPress database and reduce its size around 85%!


[toc]


Tools Required

My favorite tool for database cleanup is phpMyAdmin. If your hosting provider has CPanel, you can access your database using phpMyAdmin. If you don’t have CPanel, you can use a WordPress plugin to run SQL queries on your WordPress database.


Backup Your Database First

If you are going to run a manual query on WordPress database, I highly recommend you to backup your database first. Even when you think a query is harmless, a small mistake on the query can cause an unrecoverable damage on your tables.


1. Replace Old Links on Posts

UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content`, "http://shailan.com", "https://metinsaylan.com" ) WHERE `post_type`="post";

This snippet replaces all occurrences of a link on your post contents. I have added filterpost_type, so it will replace links only on posts.


2. Replace or Remove Old Shortcodes

UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content`, "[html]", '<pre class="html">' ) WHERE `post_type`="post";
UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content`, "[/html]", "</pre>" ) WHERE `post_type`="post";

This snippet replaces removed shortcodes with HTML tags. If you just want to remove the shortcode you can use the following sample:

UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content`, "[adsense]", '' ) WHERE `post_type`="post";

3. Delete All Post Revisions

DELETE FROM `wp_posts` WHERE `post_type`="revision"

This query snippet removes all post revisions from the database. Please note that this code doesn’t remove any post meta or term relationships. See tip 11 for removing orphaned post meta.


4. Close Comments on All Posts

UPDATE `wp_posts` SET `comment_status` = 'closed' WHERE `post_type`="post";
UPDATE `wp_posts` SET `ping_status` = 'closed' WHERE `post_type`="post";

This query closes comments and pings for all posts on your WordPress blog. If you want to enable comments, you just need to change “closed” to “open”.


5. Delete All Trashed Posts

DELETE FROM `wp_posts` WHERE `post_status`="trash"

This SQL query snippet removes all trashed posts from your database. It can save you a good amount of memory if you have many posts.


6. Delete All oEmbed Cache

DELETE FROM `wp_posts` WHERE `post_type`="oembed_cache"

I recently disabled oEmbed on my WordPress blog. So, if you are not using oEmbeds, this is also unnecessary data in your database. This query will remove all oEmbed cache from your database.


7. Delete Old Contact Forms Shortcodes

UPDATE `wp_posts` SET `post_content` = REPLACE( `post_content`, '[contact-form-7 id="4339"]', '' ) WHERE `post_type`="post";

This query deletes all contact forms with the given id from your posts.


8. Delete All Pingbacks

DELETE FROM `wp_comments` WHERE `comment_type` = 'pingback';

This snippet deletes all pingbacks from the comments table.


9. Delete All Spam Comments

DELETE FROM `wp_comments` WHERE `comment_approved` = 'spam';

This query removes all spam comments from comments table.


10. Delete All Transients On Options Table

DELETE FROM `wp_options` WHERE `option_name` LIKE '%_transient%';

This query deletes all transient cache from your options table. This query can save you a huge amount of memory depending on your WordPress database age.


11. Delete All Orphaned Post Meta

DELETE m FROM `wp_postmeta` AS m
LEFT JOIN `wp_posts` AS p ON m.`post_id` = p.`ID`
WHERE p.`ID` IS NULL

This query deletes all post meta that is not linked to any post. This query needs to be run if you manually deleted post revisions or posts using SQL.


12. Get a Full List Of Meta Keys

SELECT DISTINCT meta_key FROM `wp_postmeta`

This is not a database cleanup query. But, once you get a list of meta keys that are added, you can use those keys to delete unused meta keys on your database. See next query tip for an example.


13. Delete Removed Plugin Meta Keys

DELETE FROM `wp_postmeta` WHERE `meta_key` LIKE '%aktt%'

This query deletes all meta keys including a keyword. If you have a word common to a plugin meta, you can use that keyword to remove all meta keys used by that plugin.


14. Update Post Author ID on All Posts

UPDATE `wp_posts` SET `post_author` = '1' WHERE `post_type`='post' AND `post_status`='publish'

This query doesn’t remove any rows, but updates author for all posts to a specified author ID. If you had guest authors in the past, you can use this query to remove all old authors.


BONUS: Trigger to Empty Trashed Posts on Post Publish

CREATE TRIGGER `EMPTY_TRASH_ON_PUBLISH` AFTER INSERT ON `wp_posts` FOR EACH ROW DELETE FROM `wp_posts` WHERE `post_status`="trash"

This query creates a trigger on your database to empty your deleted posts on post publish.

 


BONUS: One Query For All

I merged all cleanup queries above in one box, so you can run this directly on SQL field:

DELETE FROM `wp_posts` WHERE `post_type`="revision";
UPDATE `wp_posts` SET `comment_status` = 'closed' WHERE `post_type`="post";
UPDATE `wp_posts` SET `ping_status` = 'closed' WHERE `post_type`="post";
DELETE FROM `wp_posts` WHERE `post_status`="trash";
DELETE FROM `wp_posts` WHERE `post_type`="oembed_cache";
DELETE FROM `wp_comments` WHERE `comment_type` = 'pingback';
DELETE FROM `wp_comments` WHERE `comment_approved` = 'spam';
DELETE FROM `wp_options` WHERE `option_name` LIKE '%_transient%';
DELETE m FROM `wp_postmeta` AS m
LEFT JOIN `wp_posts` AS p ON m.`post_id` = p.`ID`
WHERE p.`ID` IS NULL;

I hope you found those queries useful. Follow me on twitter for more tips. Enjoy!

[How to] Add a User to WordPress Programmatically

In a recent development project, I had to add a user to WordPress programmatically because I had no WP user data but only access to FTP. If this is your case, here is the code I used to add myself to the WordPress users:

<?php 
/* Add user to wordpress programmatically */
$user_name = "wpadmin";
$user_email = "[email protected]";
$user_pass = "1234";
$user_id = username_exists( $user_name );
if ( !$user_id and email_exists($user_email) == false ) {
    $random_password = $user_pass; // Alternate : wp_generate_password( $length=5, $include_standard_special_chars=false );
    $user_id = wp_create_user( $user_name, $random_password, $user_email );
} else {
    $random_password = __('User already exists.  Password inherited.');
}
print $random_password;

[How to] Edit functions.php File in WordPress

Many WordPress hacks depend on WordPress hooks and filters. If you want to add those hacks to your theme, probably the best way is using your theme’s functions.php file. In order to add codes to your theme’s function.php file, you can follow one of the ways explained below.


1. Using WordPress Theme Files Editor

editor1

If you can edit theme files (requires Administrator role), and your theme files are editable, you can edit your theme files using WordPress admin panel. To do this, simply follow those steps below:

  • Go to your WordPress dashboard (www.yoursite.com/wp-admin).
  • Navigate to Appearance > Editor.
  • Choose the theme you want to edit from the dropdown and the file from the right sidebar.

Please note that this method is dangerous, since you are editing online files, if your site is blocked because of an error, you will need FTP access to fix your error file.


[seealso ids=”6067,7982,4584″]


2. Using FTP to edit your functions file

If you have FTP connection information for your site, you can use FTP to edit your functions.php file:

  • Use your favorite FTP manager to access the location of your WordPress files.
  • Navigate to wp-content/themes/your-theme.
  • Open up the functions.php file and edit to your heart’s content!

Please note that the above methods will only work if your theme includes a functions.php file.
If functions.php file doesn’t exist in your wp-content/themes/your-theme directory, then you can create it.

I hope you liked this tutorial, Enjoy!