PHP and MYSQL: Recursion Revisited

In this tutorial I will be taking the concepts that we learned in PHP AND MYSQL: RECURSION and converting the code to use the latest and greatest: PDO.

INTRODUCTION

As a few readers mentioned, my previous tutorial regarding the storage and retrieval of comments in a threaded m-ary tree included old functions, namely mysql_query.  While the goal of this site is to help people understand the concepts driving solutions, I have decided that I should introduce more recent ways of solving problems.  

In this tutorial I will show the old code (procedural) and then the new code (object oriented) and describe the benefits that each holds. 

THE OLD CODE

Let's take a look at the old code used by PHP AND MYSQL: RECURSION

<?php
$db = mysql_connect("$dbHost", "$dbUser", "$dbPass") or die ("Error connecting to database.");
 
mysql_select_db("$dbDatabase", $db) or die ("Couldn't select the database.");
 
function getComments($ID) {
 
   $ticket = mysql_query("SELECT * FROM ticketText WHERE `ID` ='$ID'");
 
   while ($ticketRow = mysql_fetch_array($ticket)) {
 
      echo '<p style="">'.$ticketRow['text'].'</p>';
 
   }
 
   $thread = mysql_query("SELECT * FROM ticketText WHERE `referenceID` ='$ID'");
 
   if (mysql_num_rows($thread)>0) {
 
      echo '<div style="margin-left:10px; border-left: 1px black dotted; ">';
 
      while ($nextTicket = mysql_fetch_array($thread)) {
 
         getComments($nextTicket['ID']);
 
      }
 
      echo '</div>';
 
   }
 
}
 
getComments(1);
 
?>

The code listed above works, but as I mentioned before, it uses old functions.  So how can we modify it to use newer standards? Take a look at the updated code below:

public class Comments() {
 
const threadQuery = " SELECT * FROM ticketText WHERE `responseTextID` = :commentID ";
private function getComments($commentID) {
 
   $ticket = $this->database->prepare(self::ticketQuery);
 
   $ticket->bindParam(":commentID", $commentID, PDO::PARAM_INT);

   $ticket->execute();
 
   while ($ticketRow = $ticket->fetch()) {
 
      $this->commentHtmlString .= '<p><a href="#">['.$ticketRow['firstName'].' '.$ticketRow['lastName'].']</a> '.$ticketRow['timeStamp'].'<br />'.$ticketRow['text'].'</p>'; 
 
   }
 
   $thread = $this->database->prepare($this::threadQuery);
 
   $thread->bindParam("commentID", $commentID, PDO::PARAM_INT);
 
   $thread->execute();
 
   if ($thread->rowCount()) {
 
      $this->commentHtmlString .= '<div style="margin-left:10px; border-left: 1px black dotted; ">';
 
      while ($nextTicket = $thread->fetch()) {
         
         $this->getComments($nextTicket['ID']);
     
      }
 
      $this->commentHtmlString .= '</div>';
   }

}
}

What's different?

The main difference is that fact that we are now relying on PHP's new and improved PDO class.  Using the PDO class is completely different than the typical mysql_connect function in that it is classful.  The PDO class offers us an object oriented (or iterative if you still don't want to bite the OOP bullet) approach at querying the database.

Why would we want that?

One of the most significant improvements is in the area of security.  In the example posted above, I used the bindParams method.  Because I told MYSQL what I was going to do (prepared the statement) and told MYSQL where I was going to insert a variable as well as its type, we are much less prone to SQL injection attacks.  In the old code, we simply trusted that the input was safe to send to the database.

What do I mean by safe?

Take the following query as an example:

"SELECT something FROM table WHERE ID = $someNumber";

If the variable $someNumber was simply submitted by the user then we are at risk for SQL injection. What is SQL injection? Imagine that the user sent this.

1; DROP TABLE table;

If run, the database would see this as two commands:

SELECT something FROM table WHERE ID = 'null'

DROP TABLE table

Using the PDO class and the bindParams method lets the database know that we are just searching inside the ID column and that all input is to be treated as a string.  This is not to say that you should just blindly accept input, however, it does help protect you against some of the more common attacks.

Take Away

If nothing else, be aware that SQL injection exists and that if you are creating a program intended for production that accesses the SQL database you should look further into the concept of SQL injection.  Sanitize your variables; if you are looking for a number from the user, verify that it is in fact a number before you blindly hand it off to the database.