My PDO Solution

Continuing my transition over to PDO, I have rewritten my code so that it is contained within a single function. This is now fully running on my latest project AtlasCreators.NET.

$db = new PDO('mysql:host=thehost;dbname=thedbname;charset=utf8mb4',theusername,thepassword);

The above just sets up the db connection, and sets two attributes.

  • ATTR_ERRMODE  to ERRMODE_EXCEPTION which has the following description

In addition to setting the error code, PDO will throw a PDOException and set its properties to reflect the error code and error information. This setting is also useful during debugging, as it will effectively “blow up” the script at the point of the error, very quickly pointing a finger at potential problem areas in your code (remember: transactions are automatically rolled back if the exception causes the script to terminate).

Exception mode is also useful because you can structure your error handling more clearly than with traditional PHP-style warnings, and with less code/nesting than by running in silent mode and explicitly checking the return value of each database call.


This means when there are errors in my SQL statements they are pretty formatted like:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY `reaction`' at line 1' in /srv/data/web/vhosts/
Stack trace:
#0 /srv/data/web/vhosts/ PDO->prepare('SELECT COUNT(*)...')
#1 /srv/data/web/vhosts/ query('SELECT COUNT(*)...', Array)
#2 /srv/data/web/vhosts/ getReactionCount('art', 32, 'Like')
#3 /srv/data/web/vhosts/ showReactions('art', 32)
#4 {main}
 thrown in /srv/data/web/vhosts/ on line 9

This is useful because it lets you see the full stack trace of your exception.

The other attribute that I have set is:


Enables or disables emulation of prepared statements. Some drivers do not support native prepared statements or have limited support for them. Use this setting to force PDO to either always emulate prepared statements (if TRUE and emulated prepares are supported by the driver), or to try to use native prepared statements (if FALSE). It will always fall back to emulating the prepared statement if the driver cannot successfully prepare the current query. Requires bool.


This attribute was needed to prevent some errors when trying to prepare the statements. I didn’t save the exact reason and source for this, arg!

function query($sql, $data = array())
    global $db;
    $stmt = $db->prepare($sql);
    if (strpos($sql, 'INSERT INTO') !== false)
        $result = $db->lastInsertId();
    else if (strpos($sql, 'SELECT COUNT') !== false)
        $result = $stmt->fetchColumn();
    else if (strpos($sql, 'SELECT') !== false)
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        if (strpos($sql, 'LIMIT 1') !== false)
            $result = $result[0];
    return $result;

Now for the meaty bit, the main function above. This single function handles all my needed queries.

Usage examples:

  • Getting the count result directly
$sql = "SELECT COUNT(*) FROM `somewhere`";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
$count = $row[0];

$sql = "SELECT COUNT(*) FROM `somewhere`";
$count = query($sql);
echo "<p>The count is: ".$count."</p>";

  • Getting a set of results
$sql = "SELECT * FROM `somewhere`";
$results = mysql_query($sql);
while ($row = mysql_fetch_assoc($results))
   echo "<hr />";

$sql = "SELECT * FROM `somewhere`";
$results = query($sql);
foreach ($results as $row)
   echo "<hr />";
  • Getting a single result directly
$sql = "SELECT * FROM `somewhere` LIMIT 1;";
$result = mysql_query($sql);
$row = mysql_fetch_assoc($result);

$sql = "SELECT * FROM `somewhere` LIMIT 1;";
$row = query($sql);
  • Using variables in the SQL statement
$sql = "SELECT * FROM `somewhere` WHERE `thing` = ".mysql_real_escape_string($thing)." AND `otherthing` = ".mysql_escape_string($otherthing);
$results = mysql_query($sql);
while ($row = mysql_fetch_assoc($results))
   echo "<hr />";

$sql = "SELECT * FROM `somewhere` WHERE `thing` = :thing AND `otherthing` = :otherthing";
$results = query($sql,[
   'thing' => $thething, 
   'otherthing' => $otherthing
foreach ($results as $row)
 echo "<hr />";

Known issues:

It looks like if I use LIMIT 10, it considers it LIMIT 1. So that is something I need to look at for the next iteration.

Unable to activate Windows 10 after hardware change

The Problem

After the pain of setting up the hardware of a new machine from scratch (may do another post on that). Windows decided to kick me while I was down and tell me that it couldn’t activate windows.

After seeing the above message, I went to the activation center on Windows 10. To see the following message.

The Attempt

I selected the Troubleshoot option

I selected the “I recently changed hardware on this device” option.

It failed horribly, even after trying it again hours later, or trying to link my Microsoft account again.

The Solution

After some searching I found this reddit post, which solved the issue for me:

Downloading Belarc Advisor, generating a report and finding the key for Internet Explorer showed me the previous Windows Key.

Entering it into the slui command prompt activation window allowed me to activate successfully.


AdSense Publisher Policy Violation

Yesterday I received an email from Google Publisher Policy notifying me that one of my sites had an Adsense Publisher Policy Violation.

The email directed me to the AdSense Policy Center and found that the login page for Defiance Data had the flag Valuable Inventory: No content.

Okay sure, it’s a login page, there isn’t a lot of “content”, but there is a reason adverts are showing on the page. It uses the same template as the rest of the site, the same header (which shows a banner advert), sidebar (which shows a banner advert) and footer.

It felt a little petty to require me to write code to support hiding adverts on one page out of a template. So I pressed Request Review but there wasn’t any fields to enter any explanation, a few vague choices in a drop down which I don’t remember now. So I submitted it for review.

The next day I received an automated mail back to let me know that my review  was complete but that the original decision was upheld. I was still “Policy non-compliant” and the status was “Restricted ad serving” on that page.

Restricted ad serving

Due to violations on this page some ad networks have opted out of showing their ads. This means that the competition for ad units on this page is lower.

Not showing adverts on the login page isn’t something that will impact me on a low traffic site such as Defiance Data.  It just frustrates me that these things come out of the blue and “encourages” me to change the structure of my site with no real way to appeal.

PC Upgrade Ordered

I’ve been wanting to upgrade my  machine for the past few months, and today I finally pulled the trigger. I had been wanting to upgrade from my older CPU (AMD FX-8350) was slowing my whole system down.

The following should be arriving tomorrow:

  • Corsair CC-9011100-WW Carbide Series 400Q V2 Mid-Tower ATX Silent Computer Case with Fan Controller – Black
  • AMD Ryzen 5 1600 Desktop CPU – AM4/Hex Core/GHz/16MB/65W
  • ASUS PRIME X370-PRO AMD X370 S AM4 DDR4 SATA3 ATX Motherboard – Black
  • Corsair CMK16GX4M2B3200C16 16 GB (2 x 8 GB) Vengeance LPX DDR4 Memory Kit – Black
  • Corsair CP-9020091-UK RM650x 650 W 80 Plus Gold Certified Modular 135 mm Thermally Controlled Fan Power Supply Unit – Black

And these components join my graphics card I bought a while ago

  • Gigabyte Radeon RX 480 G1 Gaming 8GB – graphics cards (Active, ATX, AMD, Radeon RX 480, GDDR5, PCI Express x16 3.0)

I’m aiming for a quieter case and fans as my room gets rather loud, but we’ll see how that comes out.

Putting together PC parts is never really that fun, I’m terrible with wires and airflow.

The new motherboard has the option for SLI, so another graphics card might be something I add later, although likely that will need a bumped up PSU.

Next big item on my list would be a VR headset, it’ll likely be a HTC Vive. Perhaps in a month or two!

2nd Stat Re-rolls on Trove

This week Trove is running an event where Chaos Forges will re-roll the second stat on equipment in addition to the usual third and fourth stats.

Chronozilla persuaded me to head into game, and aim for Crit Damage / Attack Speed on my 2nd slots for my Gunslinger.

I did have enough tentacles to roll those Crit Damage on each, but not enough to try for the Attack Speed on the Gun.

Once the event ends I will have to use the Chaos Forge again to re-roll the 3rd and 4th stats to the desired ones.

Bad Designs are Painful

As I come around to do maintenance on some of my older projects some terrible designs come back to haunt me.

Take for example:

Event Registration

  • registration id
  • first person id
  • second person id
  • third person id
  • fourth person id
  • some details

Event Person

  • person id
  • some details

The person entry doesn’t know what registration it is applied to, and the code to call it has to do several joins to get all the people.

And while yes, now I know it’s a better design to do the following:

Event Registration

  • registration id
  • some details

Event Person

  • person id
  • registration id
  • some details

Me of 10 years ago did not, and it causes me much groaning as I attempt to re-design the database and then find all the code that needs to be updated to recognise it.

Site Changes

It’s time for me to shake up the site, I want to blog more as well as use the pre-made systems to make it easier to showcase my work.

So the URL changed from directly to, and I switched the blog over to the 2017 theme, featuring a large header image of my character being terrorized in the Golden Sands by Evilagician.

What will I be posting here? My current projects and games I’m playing. Prepare to be bored!

Defiance – Hot Crazy Summer Event

The Hot Crazy Summer event has arrived on Defiance, part of the constant chain of events. This event focuses on Incendiary enemies and has you face off against Tankers and Hellbugs.

The Pack

The Falconer Pack is available for 2,600 Bits, and contains:

  • HOT Blast Hatcher weapon
  • Corporate Valentine outfit
  • Hoverbike – TMW Falcon Red/Black
  • Falconer title
  • 18 Loyalty

Obviously the main reason you’d want to buy the pack is the new unique vehicle. The hoverbike is pretty cool. It does feel like a bike even though it’s obviously a standard runner behind the scenes.

The Blast Hatcher is a new weapon as well, it is a shotgun that fires explosive rounds, and attracts parasites. It would still require you to collect the mods for it.

However at £24, it feels like it’s priced far too expensively.

I feel like I’m playing solo

As with every event, expect to only see yourself, or your group (if you are social).

This is one of my biggest issues with Defiance, no matter how creative they get with content or how regularly they create events. If I can’t see other players, I feel like I’m playing on my own.

The main end-game of Defiance are these Major Arkfalls, so why aren’t they handled better.

Oh players! who would have thought?

It’s not that Defiance doesn’t have players…

Event Fatigue?

I’m kinda tired of events constantly. Each event has the same objectives. Pursuits, Contracts, chase jackpots, chase mods.

I do understand why it’s done, the framework exists, and it’s the easiest way to generate “content” right now. However it does get old quickly.

Yay using my old stats system

For the past year or so I’ve been using Google Analytics because it’s free, before that I used a premium service called Clicky.

Why have I picked it up again? Well I do have to pay out money for it, but I just find it so much easier to use than Analytics.

Direct links to pages people view in real time

Screenshot 2016-05-08 14.11.19

When I’m not working through a set task list, I like to improve my sites based on what pages I see people viewing at that moment.

This means that I can find old parts of the site that needs updates but have been overlooked because it wasn’t included in the monthly stats report (easy when lots of subpages).

Full links to referral pages

Screenshot 2016-05-08 14.13.09

Another issue I had with Analytics was that it was super hard to get the full URL of a page that was linking to yours. The reports I could see just showed the domain, while Clicky made it very clear where incoming traffic was coming from.


Analytics might offer a load of features and be great for a free service but it seems that it is designed for the depth of large organisation and marketing department.

I do admit that I may be using Analytics completely wrong, but I’ll take the simplicity.

Migrating Web Servers

Screenshot 2016-05-07 21.08.33For the past few years I’ve been adding more and more websites to my server instance. This has the benefit of a single pool of resources and a single cost.

However the number of sites has grown a fair bit, and if something is running in the background it slows them all down.

So the more recent sites I’ve been placing on new servers and for Defiance (the oldest of my sites) I decided to move that to a new one too.

I’ve always been afraid of site migration, which is why I have rarely done it, but it went surprisingly smoothly.

Because there wasn’t much database and file activity, I didn’t stress too much about doing pre and post updates, just a single dump.

The process went as follows.

  1. Download all site files from the server
  2. Create the new server instance
  3. Upload all site files to the new server
    • Add a change to the files to make it identifiable as the new server
  4. Dump the database to a file
  5. Import the database to the new server
    • Spend a while correcting the errors on import (cry)
  6. Update the DNS information to point to the new server
  7. Wait until the DNS update propagates throughout the internet
  8. Visit the new server

I was able to access the new server about 5 hours after I updated the DNS, but I saw visitors from other places in the world a few hours earlier.