Singleton Database Connection Class in PHP

One of the most common things apps need to do is access a database.

It’s not rocket science. There are lots of options available for getting the job done. I prefer PDO (Php Database Objects).

I also find that having the connection established in one place is much easier than putting one together every time I need a database connection.

There are plenty of ways to go about that, too.

But here’s a cool one. It’s a singleton class. Which means that there can only ever be one connection to the database, and all the app’s transactions go through that one connection. Theoretically, it might save a little overhead. Mostly it’s just cool.

There’s a debate raging among those who claim to know more than anyone else about whether singletons are really a good idea. My take is that sometimes they are and sometimes they aren’t. Just because you can doesn’t always mean you should.

Nevertheless, database connections are one of the commonly sited “legitimate” reasons to use one. If you’re inclined to try it, here’s what I (sometimes) use:

<?php
/**
 * dbconn.php
 *
 * DBConn Class establishes a singleton Database Connection via PDO.
 * Requires that connection constants be defined in a config file somewhere
 * else before calling.
 *
 * Use this class to connect to the Database from anywhere in the app.
 * Assigning $db = DBConn::getConnection() loads the PDO
 * for running queries.
 *
 * For example:
 * $sql = 'Some SQL Statement';
 * $result = $db->query($sql);
 * returns a PDO result set.
 *
 * Or,
 * $sql = 'INSERT INTO sometable (name) VALUES (:name)';
 * $stmt = $db->prepare($sql);
 * $stmt->bindParam(':name', $name);
 * $stmt->execute();
 * executes a prepared statement to insert $name into 'sometable'.
 *
 * @return PDO Connection 
 */

class DBConn
{
 static private $_db = null; // The same PDO will persist from one call to the next

 private function __construct() {} // disallow calling the class via new DBConn

 private function __clone() {} // disallow cloning the class

 /**
  * Establishes a PDO connection if one doesn't exist,
  * or simply returns the already existing connection.
  * @return PDO A working PDO connection
  */
 static public function getConnection()
  {
  if (self::$_db == null) { // No PDO exists yet, so make one and send it back.
    try {
      self::$_db = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USER, DB_PASS);
    } catch (PDOException $e) {
      // Use next line for debugging only, remove or comment out before going live.
      // echo 'PDO says: ' . $e->getMessage() . '<br />';

      // This is all the end user should see if the connection fails.
      die('<h1>Sorry. The Database connection is temporarily unavailable.</h1>');
    } // end PDO connection try/catch
    return self::$_db;
  } else { // There is already a PDO, so just send it back.
  return self::$_db;
  } // end PDO exists if/else 
 } // end function getConnection
} // end class DBConn

Barn Raising

I went to a barn raising yesterday. We got the whole thing up in two hours, and they even had lunch. It was great.

Only it wasn’t really a barn so much as a glorified tent called a hoop house.

One of our neighbors down the road was having this hoop house delivered from the Cornell Cooperative Extension. She’ll use it for 9 weeks to raise chickens. Then the Cornell guy will come and take it to another farmer somewhere to raise chickens.

She had a guy lined up to help put up the hoop house. At the last minute he bailed.

So with the Cornell guy on the way and due at the farm at 9, she called all the neighbors who would answer the phone at 8:30. I was one of them.

Construction was just getting started when I got there. As it turned out there were plenty of hands. I didn’t have to do much. Hold a rope here. pass out a screw or bracket there.

Since I had a digital camera, I became the project photographer/videographer.

“Like most farmers, I’m not very good at marketing,” she said. “Maybe I can use the pictures to start a Facebook page or a website. People say I should do that.”

The “barn” is up. The chicks are due to arrive on Thursday or Friday.

Maybe in 9 weeks when the chicks are grown, the website will be ready, too.

Although, I’ve seen small websites go up in about the time it takes to raise a hoop house.

Writing Prompts vs Real Questions

I started blogging on Sunday Epidemic last December simply as a way to challenge myself to write something every day.

On a number of occasions I’ve been at loose ends deciding what to write about. I have a list of places to look for writing prompts. WordPress.com’s Daily Post. Several other sites that list things about each day of the year.

Tomorrow is Penguin Day. No joke.

Still, sometimes I look over all the lists and nothing seems to pop out and say, “Write about this.”

I like penguins just fine. I just don’t feel like writing about them at the moment.

One writing prompt booklet’s suggestion for today is to write about whether I’m an early bird or a night owl.

(Usually night owl. But most nights I’m wishing I could climb into bed at around 8:30. It rarely happens.)

It’s much easier for me to write about things that I know about or things that are currently going on around me. And, so far as prompts go, it’s much easier for me to find writing material in real questions and puzzles rather than hypothetical situations.

Writing prompts tend to feel a little too much like the teacher assigning the “what I did last summer” essay each year on the first day of school.

If you did anything the world cares about on your summer vacation you probably wouldn’t need the assignment to write about it.

After four months of writing something every day I can’t claim to have the corner on writing enduring classics.

I can only say that my best posts haven’t required writing prompts to get started.

Lake George Engagements this Summer

I’m filling in at Caldwell Presbyterian Church in Lake George again this morning.

This is the first of several engagements I have scheduled there over the next couple months.

For all my Glens Falls area friends, you may want to stop by and say hello on any of the following dates:

  • May 18,
  • May 25,
  • June 1, and
  • June 29.

Service time at Cladwell is 10 am. I’m usually available to do lunch afterwards, if anyone’s interested.

I’m also scheduled to speak at the Cleverdale Chapel on Lake George on June 29. It’s a beautiful little spot on the lake.

Drop me a note in the comments if you want to plan a meet-up.

More on the Art of Generosity

Yesterday I posted about having received a gift.

I said, “Folks like that raise gift-giving to an art form.”

Coincidentally, this morning Seth Godin posted some remarks on generosity that fleshed out a little more what goes into “giving as an art form.”

I’m sure it’s not all that could be said about it, but it’s worth a read to start your thinking.

If you’re interested in practicing your own art.

An Unexpected Easter Present

A couple from Brooke’s church sent us a gift box of Omaha Steaks as an Easter present.

It showed up on our side porch on Monday.

Wow! Thanks Betty and Joe!

I never thought I’d say it about steaks, but the gift really did make us feel loved.

Partly because it was totally unexpected. But also because it was a thoughtful gift.

Folks like that raise gift-giving to an art form.

We haven’t eaten any of them yet.

I’m going to have to get a new grill to do them justice.

WordPress and php’s Multibyte String

Last August I was going crazy trying to figure out why WordPress was dropping most of my posts.

The problem happened whenever I used an apostrophe or a quote (‘ or “). Whenever I saved a draft of the post, everything after the first apostrophe or quotation mark would disappear into oblivion. This, in spite of my database collation and wp-config file being matched to the utf-8 character set.

So, for example,

Here’s a little post. Doomed to be chopped off at the first sign of an apostrophe. Alas!

would become:

Here

At the time, I had narrowed the problem down to php’s multibyte string extension. I run my own server, so I could rebuild php without the mbstring extension and everything worked fine. When I rebuilt it again with mbstring back in the mix, the problem reappeared.

And yet…

Disabling mbstring has a few disadvantages.

  • For one thing, it’s one of the php extensions required for WordPress operation. Some very popular plugins (like Jetpack) use it. In fact, without mbstring enabled, Jetpack gives you a warning that certain things, like Twitter widgets, might not work correctly.
  • For another thing, disabling the extension limits WordPress’s language capacity. I might not care that I can’t blog in German or Japanese. But then again, someday I might. And disabling mbstring is obviously not going to work for anyone else who regularly blogs in languages other than English.
  • For a third thing, it’s really hard to disable mbstring if you don’t run your own server. Those settings are loaded when php starts. And, while you can find some advice about disabling them after the fact using your .htaccess file, the solution is less than ideal.

Besides, for a geek like me, the question remained. How can mbstring be a required extension, and millions of WordPresses run just fine on servers all over the place where mbstring is enabled by default? There had to be something else.

And indeed there is a better way!

I’ve been digging a little deeper into the mbstring extension.

There are several settings that come into play, any of which can cause the death of post-apostrophe posts in WordPress. The good news is you can change these settings in your php.ini file.

For those of us who are blogging in languages represented by utf-8 characters, here are the settings you’ll need to check (or if you’re using shared hosting where you don’t have access to the main php.ini file, create your own with a text editor and put it in your root directory with these lines):

mbstring.language = Neutral

Tells the extension to use generic (utf-8) encoding.

mbstring.internal_encoding = "UTF-8"

Tells the extension to use utf-8 for internal processes.

mbstring.http_input = auto

Tells the extension to assume that anything going in (i.e. your post submission when you hit “save”) will be in the language you told it to use in line 1.

mbstring.http_output = "UTF-8"

Tells the extension that after processing spit it all back out in utf-8 encoding.

There are a few other settings you might want to play with. And if you’re blogging in Japanese you might want to check out the settings for that. You can find advice about all this on PHP’s Mbstring Runtime Configuration page.

Now, with any luck, you can have your mbstring and your apostrophes too!

Semalt, Semalt, Go Away

Last month I started noticing a lot of referrals coming to this site from semalt.semalt.com/crawler.php.

I went to semalt.com to see what that was about.

semalt home page

I wasn’t about to give them my email address without knowing anything about them. A little more digging around, and nobody seems to know exactly who they are or what they do. None of the 48895 webmasters who trust them seem to write about it. Instead, the broad consensus is that they’re Ukrainian spammers.

They don’t appear to be attempting to hack the site directly. It buggers the site statistics though, since you get a bunch of meaningless referrals, all of which result in bounces (hit the page then leave the site).

I also found out that you can’t block them by their IP because they seem to somehow co-opt IPs from all over the place.

There are a number of suggestions for using your .htaccess file to block them. I tried a few. Here’s what finally worked for me:

RewriteEngine On

#Block Spammers (We hope!)
RewriteCond %{HTTP_REFERER} semalt.com [NC]
RewriteRule .* - [F,L]

Note, I had to insert this before the WordPress rewrite block. If you put it after the WordPress section, the WordPress redirect processes first and it never gets to the spam block rewrite.

So long, Semalt.

12/7/14 – PostScript: If you’re running behind a dynamic cache like Varnish, blocking referrers via .htaccess won’t work. More on this, and a possible work-around.

Disposable Laptop

Last week two people came to me asking about their HP laptops.

In each case the description of the symptoms were the same, “It takes for ever to start up.”

In each case, I fixed their problem.

Case 1. A recent Windows Automatic Update was causing a conflict with an HP “crapplet.”

A crapplet is an app that comes preloaded on your PC by the manufacturer. Crapplets don’t do anything useful. They are added to the list of bulleted items in the “Bonus Software” list on the package to make it seem like you’re getting a good deal.

The crapplet in this case was HP Connection Manager. It’s purportedly there to help you hook up with your WiFi. Windows already knows how to automatically hook up to WiFi. The crapplet is totally useless. Except when it’s worse than useless because it gets in the way of Windows hooking up to WiFi.

The solution: Uninstall the crapplet. Now it runs fine.

This took about 45 minutes (because the machine was hung up on the crapplet and running at the speed of tortoise) to diagnose and fix.

The friend of a friend offered to pay. I took $20. He was happy to part with the money.

Had he driven to the nearest Staples in Plattsburgh (round trip 80 miles, gas $16) and paid Staples EasyTech to do it, they would have charged him, at minimum (prices listed this morning on staples.com):

  • Hardware Diagnostic Fee: $69.99
  • PC Tune-up Fee: $9.99
  • System Restore Fee: $129.99

And depending on whether or not they could figure out what the problem was, possibly a

  • Hard Drive Recovery Fee: $259.99

which is essentially just installing a new hard drive and a new copy of Windows.

For $470 you can buy a brand new HP laptop.

Case 2. During a Windows Automatic Update, the computer got unplugged.

To be fair, this is something Windows warns you about. There’s always that screen that says, “Thou shalt not unplug thy computer while it’s configuring updates.” But accidents happen.

Now Windows Explorer would crash before it could completely load, and the computer was stuck in this infinite try-to-load-but-fail loop. And nothing else can happen, because Windows can’t really function without Explorer loaded.

The culprit was a corrupted “Thumbnail Handler Extraction Host.” You can tell because if you force Windows to start the Task Manager (Ctrl-Alt-Del), you see a new copy of the Thumbnail Handler appear every time Explorer tries to restart. (Eventually, there will be so many of them, they’ll fill up the available memory and crash the machine entirely.)

You can’t just download a clean copy of Thumbnail Handler. It’s proprietary Microsoft stuff. The only way to get it back is by doing a System Restore. It’s not hard to do. It takes 15 minutes. But Staples EasyTech will charge you $129.99 for the privilege.

In addition, I found a few viruses on board (because that’s the first thing you usually check for when someone brings in a PC doing strange things). And, the botched update had also botched the Norton Anti-Virus, which needed to be re-installed.

Here’s the EasyTech bill:

  • PC Diagnostic Fee: $69.99
  • Virus Removal Fee (5 viruses @ 149 each): $750.00
  • System Restore Fee: 129.99
  • PC Tune-up Fee: 9.99
  • Software Installation Fee (Norton): 29.99

Total: $1080.

(Again, that’s provided they can figure it out and don’t just throw in the towel and charge you the $259.99 Hard Drive Recovery Fee.)

For which you can also buy a new MacBook Air.

She’s one of Brooke’s church ladies, though, and very sweet. She certainly can’t afford and doesn’t deserve to be abused to the tune of $1050. So she gets the fix for free.

I’m not saying I’ll fix your disposable laptop for free, or even for $20.

I’m also not saying that EasyTech is bad, or their prices are unfair. They have to make a living. If you need them, you need them.

Some days I wish I could bring myself to charge what they do. But I can’t. So I’m poor.

But I am saying that HP laptops are shitty machines running shitty software.

I’m saying you’d be better off saving the $399 you’re spending on the disposable, the $99 you’re going to spend on Anti-Virus software that doesn’t really work, and the $1080 you’re likely to spend on the EasyTech bill eventually, and get yourself a really nice Mac instead.

For most people, it’s actually cheaper.

Then again, you could just toss your HP disposable laptop in the recycle bin and get a shiny new one every time it screws up. That might be cheaper, too.

Happy Birthday, Mom

Today is my mother’s birthday. She’s turning 39. Happy birthday, Mom.

She’s one of the handful of Sunday Epidemic’s regular readers.

Sunday Epidemic is like most new blogs in that respect. Not many readers, among whom are the author’s mother and the author’s cat.

It’s ok. I haven’t done much to promote Sunday Epidemic. Until recently I haven’t linked any posts to Facebook or Twitter. I haven’t mentioned it to many people in person. It’s been mostly Google search that has brought anyone here, and not many people at that.

However, even in the four months I’ve been writing here with the few visitors to the site, it’s already developed a “long tail” pattern.

The long tail is a statistical pattern where the most popular items tend to get a vast majority of interest, while a lot of items garner just a little interest, and yet the “tail” constitutes the greater part of the whole. Statisticians have known about it for years. It’s been the basis of a popular retail business strategy since Chris Anderson wrote about it in 2006 in his book, The Long Tail.

Here’s a graph of the number of views of every Sunday Epidemic article that’s been called up over the last three months.

Sunday Epidemic Pageviews

The first five bars are the five “Most Read” posts that appear in the right sidebar. The rest is everything else. 86 posts in all that anyone has looked at. The first one has twice as many views as the second one. The second one has almost twice as many as numbers 3 and 4. At the other end, there are almost 20 posts that have been looked at just once in the last three months. That’s the long tail.

If you chart the sales figures for ice cream flavors, you’ll find the same pattern. Here’s a list of ice cream sales compiled by the Food Channel:

  1. Vanilla, 29%
  2. Chocolate, 8.9%
  3. Butter pecan, 5.3%
  4. Strawberry, 5.3%
  5. Neapolitan, 4.2%
  6. Chocolate chip, 3.9%
  7. French vanilla, 3.8%
  8. Cookies and cream, 3.6%
  9. Vanilla fudge ripple, 2.6%
  10. Praline pecan, 1.7%
  11. Cherry, 1.6%
  12. Chocolate almond, 1.6%
  13. Coffee, 1.6%
  14. Rocky road, 1.5%
  15. Chocolate marshmallow, 1.3%
  16. All others, 23.7%

Put that into a bar chart, and you get:

Ice Cream Flavor Popularity

Vanilla far outsells even the number 2 flavor, chocolate, almost 3 to 1. And yet, if you take the whole chart, the sales of all the other flavors combined outsell vanilla more than 2 to 1.

The same pattern appears in just about any “best-seller” list you can name.

What that means for Sunday Epidemic (or just about any other activity you might engage in) is most of the posts I’ll write will disappear into obscurity on the end of the long tail.

I post every day. Out of every three months of posts, six weeks worth will be seen maybe three times, probably less.

On the other hand a very few of those posts, one every three months or so, will become (relatively) popular.

So why write a post every day? Because the huge number of posts that get just 1 or 2 hits together will eventually outnumber the hits of the most popular one or two.

Most of the work any of us does is doomed to obscurity. But it’s showing up every day and creating all those obscure bits over time that ends up carrying more weight when you consider the whole long tail than the memorable “big hits.”

So here’s to all the moms (and dads) showing up every day all those years without much fanfare. By the time you (or your kids) get to age 39, all those bits will be what counts.

(And there’ll probably be one or two “greatest hits,” too.)