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.

Leave a Reply