Tag Archives: SQL

Changed WordPress Blog to Use HTTPS

A few days ago I noticed some odd errors in my PHP error log indicating some HTTPS accesses to my site which shouldn’t have been possible. Upon further investigation, it turns out that my web host, Surpass Hosting, had enabled cPanel’s AutoSSL on their shared servers back on January 24th, 2018 or so. I decided to take advantage of this and began migrating my blog to use HTTPS.ย 

I first went into the General Settings and changed the WordPress Address (URL) and Site Address (URL) to use https. I then followed the directions at Replacing Image Links in WordPress After Installing an SSL Certificate – WPMU DEV to manually update my database using SQL commands. While there are plugins that could probably do this, I wanted to know exactly what SQL commands were being executed on my database. Note: I made a backup of the database before starting any of this. These are the commands I used to change my image and other links to use HTTPS (I replaced my actual domain name here to confuse bots):

UPDATE wp_posts
SET post_content = REPLACE(post_content,'src="http://blog.(domain.com)','src="https://blog.(domain.com)');

UPDATE wp_posts
SET post_content = REPLACE(post_content,'src="http://www.(domain.com)','src="https://www.(domain.com)');

UPDATE wp_posts
SET guid = REPLACE (guid, 'http://blog.(domain.com)', 'https://blog.(domain.com)') WHERE post_type = 'attachment';

UPDATE wp_posts
SET post_content = REPLACE(post_content,'href="http://blog.(domain.com)','href="https://blog.(domain.com)');

Because some of my featured images are external images on my main site being handled by Featured Image From URL, I also needed to update the wp_postmeta database. I used

SELECT * FROM wp_postmeta
WHERE meta_key = "fifu_image_url" AND meta_value != "";

to list the posts that had one and just manually updated these entries in my SQL editor directly.

I also had to modify the code in my widgets to avoid loading mixed content from my gallery and main website, which will also work over HTTPS now.

To make my local development environment match the web host, I then began to figure out how to get SSL working on my local Apache installation. I’ll save that endeavor for another post (which I did get working). 😃

Anyway, so far, so good.

How to not count views made by the Gallery 3 Admin

If you’re like me, you like to know how many times your photos are viewed; however, you don’t want to count the views made by yourself because who cares how many times you look at one of your own images?

Here’s how I got Gallery 3.0 to stop counting my views as the gallery administrator. (Update 1-27-2011: For those that have upgraded to Gallery 3.0.1, please see this post.) I should mention that my target audience for this post are those that are familiar with editing PHP files and have a means to access their Gallery database (to reset the view_counts back to zero).

First, in /themes/(your theme)/views/page.html.php add the following code near the top (below the first line, making sure you keep the code within PHP opening and closing tags.):

2
3
4
5
6
7
8
<?
if ($user->admin == '1') {
   $_SESSION['admin'] = true;
} else {
   $_SESSION['admin'] = false;
}
?>

Then, in /modules/gallery/controllers/photos.php modify the code around line 50: (make sure you do not leave the existing view_count++ statement outside the IF statements; otherwise, you’ll get duplicate view counts)

50
51
52
53
54
55
56
    $template->content = new View("photo.html");

    // mrh added the session admin check
    if(!$_SESSION['admin']) {
      $photo->view_count++;
    }
    $photo->save();

Then, in /modules/gallery/controllers/movies.php around line 50:

50
51
52
53
54
55
56
57
58
    $template->content = new View("movie.html");

    // mrh added the session admin check
    if (!$_SESSION['admin']) {
      $movie->view_count++;
    }
    $movie->save();

    print $template;

Finally, in /modules/gallery/controllers/albums.php around line 77:

77
78
79
80
81
82
83
    // mrh added the session admin check
    if (!$_SESSION['admin']) {
      db::query("UPDATE {items} SET `view_count` = `view_count` + 1 WHERE `id` = $album->id")
      ->execute();
    }

    print $template;

The Gallery will now not count any views made by the administrator which in my case, is me. ๐Ÿ™‚ To reset the view counts back to zero, the following SQL statement will work:

UPDATE items SET view_count = 0 WHERE view_count > 0;

Since you’re modifying the core Gallery code, be sure to make backups before applying any future upgrades as your changes will be lost.

Hopefully this helps! ๐Ÿ™‚