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.

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.

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!

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.

Commenting System Upgrades

I’ve tried to use some existing commenting systems before, but these tended to not integrate nicely with my accounts system. I do like writing my own stuff though, so I upgraded mine.

Some images to start with!

DAoCDB was the first comments system I wrote many years ago. Ugly terrible and bad.

Screenshot 2016-05-05 13.05.24

Defiance Data was my first site that took off, and so I spent more time writing the system for that. It’s received a few minor cosmetic upgrades over the years.

Screenshot 2016-05-05 13.06.32

After allowing just standalone text in the comments I wanted to allow people to quote and reply like they would on forums. I didn’t style it in the best way though and it was quite confusing.

Screenshot 2016-05-05 13.07.24

When I launched Trovesaurus and it used the same commenting system. It wasn’t until I launched AtlasReactor.info that I wanted to take it in a new direction.

Screenshot 2016-05-05 13.10.22

The AR system added the concept of threaded comments and votes, members could up or downvote individual comments, and people could reply to comments and make comment threads. This stopped the need to keep quoting things.

Trovesaurus then received the basis of that. The first draft was still fairly ugly.

Jzk3rHN

Through user feedback we enhanced the style, and I think it’s in a much better position.  Also the user experience was improved as I added something I don’t usually do, allow posting through javascript in the background).

This made it so that people didn’t have to reload pages or lose their place. Less painful than I thought it would be, I’m not sure why I’ve always avoided it.

mMWvdLx

It’s still a work in progress, and without feedback it wouldn’t be getting the constant updates.

 

Trying to transition my PHP sites to PDO

Screenshot 2016-05-02 22.26.15

PDO – PHP Data Objects is a database access layer that the web developer community has been shifting to over the past few years mostly because of the enhanced security that prepared statements offer.

Over the past few weeks I’ve been trying to make the transition. It’s a pain!

I didn’t realise just how much I was relying on PHPs MySQL functions, from mysql_fetch_assoc() to mysql_insert_id(), I had to rewrite a large portion of my sites database interactions.

I still haven’t got a reliable way of getting the last id inserted, it’s meant to be the following, but I have had some odd returns so I’m not that confident about it.

$db->lastInsertId();

For the transition I am running both systems side by side, I have another query function mirroring my “query” function called pdoQuery.

try 
{
   $result = $db->query($sql);
}
catch (PDOException $ex) 
{
   echo $ex->getMessage();
}

This allows me to essentially run the same SQL, and output any errors same as with mysql_error until I am ready to move over to parameterized query.

Instead of using mysql_fetch_assoc in my loops, I wrote another function to get a single result in an associative array and an array of results.

function getResult($result)
{
   return $result->fetch(PDO::FETCH_ASSOC);
}

function getResults($result)
{
   return $result->fetchAll(PDO::FETCH_ASSOC);
}

So the code

$result = query($sql);
while ($row = mysql_fetch_assoc($result))
{

becomes

$result = pdoQuery($sql);
$rows = getResults($result);
foreach ($rows as $row)
{

This is a bit clunky and I guess I could remove the $rows assignment and iterate through $result->fetchAll(PDO::FETCH_ASSOC) directly.

I’ve still got a lot to learn about PDO, but I’m working on it.

The elusive spacing and  character in header

For a long time I had odd issues when I tried to generate images.

I was using php to include my general site scripts which provided the db connection and common functions but the images always generated an error.

I had to do away with trying to include the common functions as it was the only way to get the image to generate. This makes sense now as I fixed another issue.

Screenshot 2016-05-01 15.21.29

I noticed after creating a new file (with copy and paste from an older one) that a space started appearing at the top of my site. As well as the &#65279; character in the header.

I turned up this post on Stack Overflow: http://stackoverflow.com/questions/6784799/what-is-this-char-65279

The answer mentioned that character was a zero-width no-break space that was commonly used as a byte-order mark (BOM).

This didn’t really mean anything until it was mentioned to look it up in Notepad++, and there it was.

Screenshot 2016-05-01 15.21.59

The encoding on the bottom-right was UTF-8-BOM while my other files were UTF-8.

Screenshot 2016-05-01 15.22.04

So from the top menu, Encoding button, I selected Encode in UTF-8, saved the file and uploaded and the problem was sorted.

So this is my note for my future reference and for any others who have encountered this problem 🙂

 

 

Sites Report (September 2015)

Drops on both Defiance Data and Trovesaurus were expected, just sad at the number. Work on site enhancements continue along with ramping up designs for new sites.

Site Users Visits Views
Trovesaurus 89.278 (-47.49%) 224,305 (-49.45%) 783,433 (-46.63%)
Defiance Data 29,752 (-44.66%) 73,641 (-47.45%) 262,889 (-36.87%)
Rift Core 6,157 (-4.60%) 10,581 (-1.13%) 17,081 (-2.95%)