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.

One Reply to “Trying to transition my PHP sites to PDO”

Leave a Reply