Say you have hundreds or even thousands of posts within your database, and you need to make site-wide global changes. Making each edit via the WordPress Dashboard can be time-consuming and does open up the possibility of mistakes occurring. If you do need to make site-wide edits, then it’s time to roll up your sleeves and delve directly into the WordPress MySQL database.

Always Backup WordPress First!

Your WordPress database stores every single one of your carefully written posts, every comment from your loyal readers, and every setting you have used to personalize your site. No matter how confident you are in your ability to use SQL queries, always remember to backup your WordPress database first!

Here are some resources to help you backup WordPress:

• WordPress Backups – Here you will find detailed instructions to backup your WordPress Site and your WordPress database as well as resources for automatic WordPress backups (plugins).
• Free WordPress Backup Solutions – These free WordPress backup plugins cater to every need and website.

You might also like these useful .htaccess snippets or these snippets that make WordPress user-friendly for your clients.

Add a Custom Field to All WordPress Posts & Pages

This snippet will add a custom field to every post and page found in your WP database. All you have to do is replace the UniversalCutomField to whatever Custom Field name you like to create, and then change MyCustomFieldValue to the value of your choice.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyCustomFieldValue AS meta_value FROM wp_posts
WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField');

For posts only, use this snippet…

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyCustomFieldValue AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
'' AND post_type = 'post';

…and for pages only, use this code…

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyCustomFieldValue AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
AND 'post_type' = 'page';

Delete WordPress Post Meta

When you install or remove plugins, they use the post meta to store data. After you have removed a plugin, the data will remain in the post_meta table, which of course, is no longer needed. Remember and change YourMetaKey to your own value before running this query.

DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';

Identify Unused WordPress Tags

In a WordPress database, if you run a query to delete old posts, like the one above, the old tags will remain. This query allows you to identify all of the unused tags.

SELECT * From wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

Batch Delete WordPress Spam Comments

This little snippet is a life-saver. All you have to do to delete them all is run this SQL command:

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

Batch Delete All Unapproved WordPress Comments

This SQL query will remove all unapproved comments and not touch the approved comments.

DELETE FROM wp_comments WHERE comment_approved = 0

Disable WordPress Comments on Older Posts

For this query, specify the comment_status as either open, closed, or registered_only. Also, specify the date by editing the 2016-01-01 to suit your needs.

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2016-01-01' AND post_status = 'publish';

Disabling & Enabling WordPress Trackbacks & Pingbacks

For this query, specify the comment_status as either open, closed, or registered_only.

Globally enable pingbacks/trackbacks for all users:

UPDATE wp_posts SET ping_status = 'open';

Globally disable pingbacks/trackbacks for all users:

UPDATE wp_posts SET ping_status = 'closed';

For this query, specify the ping_status as either open or closed. Also, specify the date by editing the 2016-01-01 to suit your needs.

UPDATE wp_posts SET ping_status = 'closed' WHERE post_date < '2016-01-01' AND post_status = 'publish';

Delete WordPress Comments With a Specific URL

If you have spam comments that all contain the same URL, then this query allows you to remove them in one go. The following query will delete all comments with a particular URL. The '%' means that any URL containing the string within the '%' signs will be deleted.

DELETE from wp_comments WHERE comment_author_url LIKE "%nastyspamurl%" ;

Identify & Delete WordPress Posts that are over 'X' Days Old

If you ever need to identify and delete posts over a certain number of days old, this snippet will help.

To identify any posts that are over 'X' amount of days, run this query, remembering to replace the 'X' with the number of days you are looking for:

SELECT * FROM 'wp_posts'
WHERE 'post_type' = 'post'
AND DATEDIFF(NOW(), 'post_date') > X

To delete any posts that are over 'X' amount of days, run this query:

DELETE FROM 'wp_posts'
WHERE 'post_type' = 'post'
AND DATEDIFF(NOW(), 'post_date') > X

Removing Unwanted WordPress Shortcodes

WordPress shortcodes are great, but if you decide to stop using them, their code will stay within your post content. Here is a simple SQL query to run on your database to get rid of any unwanted shortcodes. Replace unusedshortcodes with your own shortcode name.

UPDATE wp_post SET post_content = replace(post_content, '[unusedshortcodes]', '' ) ;

Change Your WordPress Posts Into Pages and Vice-Versa

Changing posts to pages is very easy. All you have to do is run this short SQL query:

UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'

… and if you want to change pages to posts use this snippet:

UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page'

Change Author Attribution On All WordPress Posts

The first thing you will need to do for this snippet is retrieve the IDs of the WordPress author. You can find this out by using the following SQL command:

SELECT ID, display_name FROM wp_users;

Once you have the old and new IDs, insert the command below, remembering to replace NEW_AUTHOR_ID with the new author ID and OLD_AUTHOR_ID with the old.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Batch Deleting WordPress Post Revisions

Post revisions can be very useful, but they also considerably increase the size of your MySQL database. You could manually delete post revisions, but a much quicker method would be to use this SQL query.

DELETE FROM wp_posts WHERE post_type ="revision";

Disable or Enable All WordPress Plugins

If you have ever encountered the white screen of death and found yourself unable to login to the WordPress Admin after activating a new plugin, then this snippet will certainly help you. It will disable all plugins instantly, allowing you to log back in.

UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';

Changing the Destination URL of a WordPress Site

Once you've moved your blog (template files, uploads & database) from one server to another, the next thing you will then need to do is to tell WordPress your new address.

Remember and change https://www.old-site.com to your old URL, and the https://www.new-site.com to your new URL.

The first command to use is:

UPDATE wp_options SET option_value = replace(option_value, 'https://www.old-site.com', 'https://www.new-site.com') WHERE option_name = 'home' OR option_name = 'siteurl';

Then you will have to change the url from the table wp_posts with this snippet:

UPDATE wp_posts SET guid = replace(guid, 'https://www.old-site.com','https://www.new-site.com);

And finally, you'll need to search the content of your posts to be sure that your new URL link is not messing with the old URL:

UPDATE wp_posts SET post_content = replace(post_content, ' https://www.ancien-site.com ', ' https://www.nouveau-site.com ');

Change the Default 'Admin' WordPress Username

Every WordPress installation will create an account with the default Admin username. Being able to change this default username will give your WordPress admin panel additional security.

Change YourNewUsername to your new name:

UPDATE wp_users SET user_login = 'YourNewUsername' WHERE user_login = 'Admin';

Manually Reset your WordPress Password

If you have only a single user on your WordPress installation, and the login name is 'admin,' you can reset your password with this simple SQL query. Once executed, it will replace PASSWORD with your new password.

UPDATE 'wordpress'.'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'wp_users'.'user_login' ='admin' LIMIT 1;

Search & Replace WordPress Post Content

To search and replace post content, use the following code. Replace OriginalText with the current text and replace NewText with your new text.

UPDATE wp_posts SET 'post_content'
= REPLACE ('post_content',
'OriginalText',
'NewText');

Changing the URL of WordPress Images

If you need to change the paths of your images, you can use this SQL command:

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'src=”https://www.myoldurl.com', 'src=”https://www.mynewurl.com');

The post 20 Time-Saving WordPress SQL Query Snippets appeared first on Speckyboy Design Magazine.

Today, we have code snippets that focus on extending the functionality of WordPress even further. Just like the previous post, there are 20 very useful snippets in total, covering many areas of theme development, including tracking page-views, custom shortcodes and widgets, custom title lengths and excerpts, and much, much more.

You might also like these useful .htaccess snippets & hacks, these WordPress SQL Query Snippets, or these snippets that make WordPress user-friendly for your clients.

Automatically Add Twitter & Facebook Buttons to WordPress Posts

This snippet will add Twitter and Facebook buttons to the bottom of all your posts. All you have to do is paste the code below into your functions.php file:

function share_this($content){
    if(!is_feed() && !is_home()) {
        $content .= '<div class="share-this">
                    <a href="https://twitter.com/share"
class="twitter-share-button"
data-count="horizontal">Tweet</a>
                    <script type="text/javascript"
src="https://platform.twitter.com/widgets.js"></script>
                    <div class="facebook-share-button">
                        <iframe
src="https://www.facebook.com/plugins/like.php?href='.
urlencode(get_permalink($post->ID))
.'&amp;layout=button_count&amp;show_faces=false&amp;width=200&amp;action=like&amp;colorscheme=light&amp;height=21"
scrolling="no" frameborder="0" style="border:none;
overflow:hidden; width:200px; height:21px;"
allowTransparency="true"></iframe>
                    </div>
                </div>';
    }
    return $content;
}
add_action('the_content', 'share_this');

Track WordPress Post Views by Using Post Meta

For a simple way to keep track of the number of post views, paste this snippet into the functions.php, and then follow steps 1 and 2.

function getPostViews($postID){
    $count_key = 'post_views_count';
    $count = get_post_meta($postID, $count_key, true);
    if($count==''){
        delete_post_meta($postID, $count_key);
        add_post_meta($postID, $count_key, '0');
        return "0 View";
    }
    return $count.' Views';
}
function setPostViews($postID) {
    $count_key = 'post_views_count';
    $count = get_post_meta($postID, $count_key, true);
    if($count==''){
        $count = 0;
        delete_post_meta($postID, $count_key);
        add_post_meta($postID, $count_key, '0');
    }else{
        $count++;
        update_post_meta($postID, $count_key, $count);
    }
}

Step 1: Paste the code below within the single.php within the loop:

<?php
          setPostViews(get_the_ID());
?>

Step 2: Paste the snippet below anywhere within the template where you would like to display the number of views:

<?php
          echo getPostViews(get_the_ID());
?>

Track WordPress Post View Amount by Using Post Meta

This snippet will create a list of your most popular posts based on page views. Please note that this will only work if you have already implemented the ‘Track Post View Amount by Using Post Meta’ from above.
Place this snippet just before the loop within the index.php template and WordPress will use the post views to order your posts from highest to lowest.

<?
query_posts('meta_key=post_views_count&orderby=post_views_count&order=DESC');
?>

WordPress Breadcrumbs Without a Plugin

Breadcrumbs can be a useful navigation technique that offers link to the previous page the user navigated through to arrive at the current post/page. There are plugins you could use, but the code snippet below could be an easier solution.

Paste this code into your functions.php file.

function the_breadcrumb() {
echo '<ul id="crumbs">';
if (!is_home()) {
echo '<li><a href="';
echo get_option('home');
echo '">';
echo 'Home';
echo "</a></li>";
if (is_category() || is_single()) {
echo '<li>';
the_category(' </li><li> ');
if (is_single()) {
echo "</li><li>";
the_title();
echo '</li>';
}
} elseif (is_page()) {
echo '<li>';
echo the_title();
echo '</li>';
}
}
elseif (is_tag()) {single_tag_title();}
elseif (is_day()) {echo"<li>Archive for "; the_time('F jS, Y'); echo'</li>';}
elseif (is_month()) {echo"<li>Archive for "; the_time('F, Y'); echo'</li>';}
elseif (is_year()) {echo"<li>Archive for "; the_time('Y'); echo'</li>';}
elseif (is_author()) {echo"<li>Author Archive"; echo'</li>';}
elseif (isset($_GET['paged']) && !empty($_GET['paged'])) {echo "<li>Blog Archives"; echo'</li>';}
elseif (is_search()) {echo"<li>Search Results"; echo'</li>';}
echo '</ul>';
}

Then paste the calling code below, wherever you would like the breadcrumbs to appear (typically above the title tag).

<?php the_breadcrumb(); ?>

Display the Number of Facebook Fans

Facebook is a must site for sharing your blogs articles. Here is a method for showing your visitors the total number of Facebook ‘Likes’ your blog currently has.

To use this code all you have to do is replace the YOUR PAGE-ID with your own Facebook page id.

<?php
$page_id = "YOUR PAGE-ID";
$xml = @simplexml_load_file("https://api.facebook.com/restserver.php?method=facebook.fql.query&query=SELECT%20fan_count%20FROM%20page%20WHERE%20page_id=".$page_id."") or die ("a lot");
$fans = $xml->page->fan_count;
echo $fans;
?>

Display an External RSS Feed in WordPress

This snippet will fetch the latest entries of any specified feed url.

<?php include_once(ABSPATH.WPINC.'/rss.php');
wp_rss('https://wpforums.com/external.php?type=RSS2', 5); ?>

This code takes the rss.php file that is built into WordPress (used for widgets). It is set to display the most recent 5 posts from the RSS feed ‘https://example.com/external.php?type=RSS2’.

WordPress Shortcode to Display External Files

If you are looking for a quick way to automatically include external page content within your post, this snippet will create a shortcode allowing you to do it.

Paste this code into your themes functions.php file:

function show_file_func( $atts ) {
  extract( shortcode_atts( array(
    'file' => ''
  ), $atts ) );
 
  if ($file!='')
    return @file_get_contents($file);
}
 
add_shortcode( 'show_file', 'show_file_func' );

And then post this shortcode, editing the URL, into your post or page.

[show_file file="https://speckyboy.com/2010/12/09/20-plugin-replacing-tutorials-tips-snippets-and-solutions-for-wordpress/"]

Create Custom WordPress Widgets

WordPress provides many widgets, but if you want to create custom widgets tailored to your blog, then this snippet may help you.

Paste the code below into your functions.php file.

    class My_Widget extends WP_Widget {  
        function My_Widget() {  
            parent::WP_Widget(false, 'Our Test Widget');  
        }  
    function form($instance) {  
            // outputs the options form on admin  
        }  
    function update($new_instance, $old_instance) {  
            // processes widget options to be saved  
            return $new_instance;  
        }  
    function widget($args, $instance) {  
            // outputs the content of the widget  
        }  
    }  
    register_widget('My_Widget'); 
  

Create Custom WordPress Shortcodes

Shortcodes are a handy way of using code functions within your theme. The advantage of shortcodes is that they can be easily used with the WordPress post editor.

Add this code to your functions.php:

function helloworld() {
return 'Hello World!';
}
add_shortcode('hello', 'helloworld');

You can then use [hello] wherever you want to display the content of the shortcode.

Custom Title Length

This snippet will allow you to customise the length (by the number of characters) of your post title.

Paste this code into the functions.php:

    
function ODD_title($char)
    {
    $title = get_the_title($post->ID);
    $title = substr($title,0,$char);
    echo $title;
    }

To use this function all you have to do is paste the below code into your theme files, remembering to change the ’20’ to what ever character amount you require:

<?php ODD_title(20); ?>

Custom WordPress Excerpts

Sometimes you may need to limit how many words are in the excerpt, with this snippet you can create your own custom excerpt (my_excerpts) replacing the original.

Paste this code in functions.php.

    
<?php add_filter('the_excerpt', 'my_excerpts');
function my_excerpts($content = false) {
            global $post;
            $mycontent = $post->post_excerpt;
 
            $mycontent = $post->post_content;
            $mycontent = strip_shortcodes($mycontent);
            $mycontent = str_replace(']]>', ']]&gt;', $mycontent);
            $mycontent = strip_tags($mycontent);
            $excerpt_length = 55;
            $words = explode(' ', $mycontent, $excerpt_length + 1);
            if(count($words) > $excerpt_length) :
                array_pop($words);
                array_push($words, '...');
                $mycontent = implode(' ', $words);
            endif;
            $mycontent = '<p>' . $mycontent . '</p>';
// Make sure to return the content
    return $mycontent;
}
?>

Secondly, paste this code within the Loop:

<?php echo my_excerpts(); ?>

Redirect WordPress Post Titles To External Links

This snippet could be useful for anyone with a news submission site, by redirecting your post title to an external URL when clicked via a custom field.

Paste this snippet into your functions.php file:

//Custom Redirection
function print_post_title() {
global $post;
$thePostID = $post->ID;
$post_id = get_post($thePostID);
$title = $post_id->post_title;
$perm  = get_permalink($post_id);
$post_keys = array(); $post_val  = array();
$post_keys = get_post_custom_keys($thePostID);
if (!empty($post_keys)) {
foreach ($post_keys as $pkey) {
if ($pkey=='url') {
$post_val = get_post_custom_values($pkey);
}
}
if (empty($post_val)) {
$link = $perm;
} else {
$link = $post_val[0];
}
} else {
$link = $perm;
}
echo '<h2><a href="'.$link.'" rel="bookmark" title="'.$title.'">'.$title.'</a></h2>';
}

Then you add a custom field named ‘url’ and in the value field put the link to which the title is to be redirected to.

Redirect to Single WordPress Post if There is Only One Post in Category/Tag

If there is only one post within a category or tag, this little snippet will jump the reader directly to the post page.

Paste this code into your themes functions.php file:

function stf_redirect_to_post(){
    global $wp_query;
 
    // If there is one post on archive page
    if( is_archive() && $wp_query->post_count == 1 ){
        // Setup post data
        the_post();
        // Get permalink
        $post_url = get_permalink();
        // Redirect to post page
        wp_redirect( $post_url );
    }  
 
} add_action('template_redirect', 'stf_redirect_to_post');

List Scheduled/Future WordPress Posts

Paste the code anywhere on your template where you want your scheduled posts to be listed, changing the max number or displayed posts by changing the value of showposts in the query.

<?php
$my_query = new WP_Query('post_status=future&order=DESC&showposts=5');
if ($my_query->have_posts()) {
    while ($my_query->have_posts()) : $my_query->the_post();
        $do_not_duplicate = $post->ID; ?>
        <li><?php the_title(); ?></li>
    <?php endwhile;
}
?>

Screenshots of External WordPress Pages Without a Plugin

This is a very simple URL script that will generate a screenshot of any website. Here is the URL:

https://s.wordpress.com/mshots/v1/http%3A%2F%2Fspeckyboy.com%2F?w=500

To see what the link above does, click here.

All you have to do is insert your required URL in the place of the ‘speckyboy.com’ part of the link and resize (‘w=500’) as required.

Add Content to the End of Each RSS Post

Adding some extra content that is only viewable by your RSS subscribers couldn’t be easier with this snippet.
Paste this code into the functions.php:

function feedFilter($query) {
    if ($query->is_feed) {
        add_filter('the_content','feedContentFilter');
    }
    return $query;
}
add_filter('pre_get_posts','feedFilter');
 
function feedContentFilter($content) {
    $content .= '<p>Extra RSS content goes here... </p>';
 
    return $content;
}

Reset Your WordPress Password

What would you do if you forget your WordPress Admin Password and you no longer have access to the admin area? To fix this all you have to do is jump to your PhpMyAdmin Sql-window and run the following command.

UPDATE `wp_users` SET `user_pass` = MD5('NEW_PASSWORD') WHERE `wp_users`.`user_login` =`YOUR_USER_NAME` LIMIT 1;

Detect Which Browser Your WordPress Visitors are Using

If you want to use different stylesheets for different browsers, then this is a snippet you could use. It detects the browser your visitors are using and creates a different class for each browser. You can use that class to create custom stylesheets.

add_filter('body_class','browser_body_class');
function browser_body_class($classes) {
global $is_lynx, $is_gecko, $is_IE, $is_opera, $is_NS4, $is_safari, $is_chrome, $is_iphone;
if($is_lynx) $classes[] = 'lynx';
elseif($is_gecko) $classes[] = 'gecko';
elseif($is_opera) $classes[] = 'opera';
elseif($is_NS4) $classes[] = 'ns4';
elseif($is_safari) $classes[] = 'safari';
elseif($is_chrome) $classes[] = 'chrome';
elseif($is_IE) $classes[] = 'ie';
else $classes[] = 'unknown';
if($is_iphone) $classes[] = 'iphone';
return $classes;
}

Display WordPress Search Terms from Google Users

If a visitor reached your site through Google’s search, this script will display the terms they searched for in order to find your site. Just paste it anywhere outside of the header section.

<?php
$refer = $_SERVER["HTTP_REFERER"];
if (strpos($refer, "google")) {
        $refer_string = parse_url($refer, PHP_URL_QUERY);
        parse_str($refer_string, $vars);
        $search_terms = $vars['q'];
        echo 'Welcome Google visitor! You searched for the following terms to get here: ';
        echo $search_terms;
};
?>

Show Different Content for Mac & Windows

If you ever have the need to display different content to either Mac or Windows users (ie. software download link for different platforms), you can paste the following code into your themes functions.php file. You can easily change the content of the function to your own needs.

<?php
if (stristr($_SERVER['HTTP_USER_AGENT'],"mac")) {
echo ‘Hello, I\’m a Mac.’;
} else {
echo ‘And I\’m a PC.’;
}
?>

The post 20 Code Snippets for Extending the Functionality of WordPress appeared first on Speckyboy Design Magazine.