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);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

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.

Source: http://php.net/manual/en/pdo.error-handling.php

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/lancerdoodles.com/htdocs/framework/db.php:9
Stack trace:
#0 /srv/data/web/vhosts/lancerdoodles.com/htdocs/framework/db.php(9): PDO->prepare('SELECT COUNT(*)...')
#1 /srv/data/web/vhosts/lancerdoodles.com/htdocs/framework/reactions.php(45): query('SELECT COUNT(*)...', Array)
#2 /srv/data/web/vhosts/lancerdoodles.com/htdocs/framework/reactions.php(102): getReactionCount('art', 32, 'Like')
#3 /srv/data/web/vhosts/lancerdoodles.com/htdocs/art.php(255): showReactions('art', 32)
#4 {main}
 thrown in /srv/data/web/vhosts/lancerdoodles.com/htdocs/framework/db.php 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:

  • PDO::ATTR_EMULATE_PREPARES to FALSE

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.

Source: http://php.net/manual/en/pdo.setattribute.php

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);
    $stmt->execute($data);    
    
    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
ORIGINAL
$sql = "SELECT COUNT(*) FROM `somewhere`";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
$count = $row[0];

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

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

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

BECOMES
$sql = "SELECT * FROM `somewhere` LIMIT 1;";
$row = query($sql);
print_r($row);
  • Using variables in the SQL statement
ORIGINAL
$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))
{
   print_r($row);
   echo "<hr />";
}

BECOMES
$sql = "SELECT * FROM `somewhere` WHERE `thing` = :thing AND `otherthing` = :otherthing";
$results = query($sql,[
   'thing' => $thething, 
   'otherthing' => $otherthing
);
foreach ($results as $row)
{
 print_r($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.

https://trovesaurus.com/page=2386/modify-a-second-stat-in-the-chaos-forge

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 etaew.net/blog directly to etaew.net, 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!