Sweating the Details

If you’ve ever had to write a PHP page which displays data from a MySQL query, you’ve run into this problem before:

How do you design a system to elegantly separate database code from display code, and maintain a clean object-oriented structure?

Not so good solutions

I have been plagued with this problem for quite some time, and I often settled on half-baked solutions such as:

All code in one place:

$query = "SELECT u_username, u_firstName, u_lastName FROM userTable";
$users = mysql_query($query);
while($row = mysql_fetch_assoc($users))
{
 echo "<div class="user">";
 echo "<strong>".$row['u_username']."</strong> ".
	"<em>".$row['u_firstName']." ".$row['u_lastName']."</em>";
 echo "</div>";
}

This is a simple example, but it is clear to see that in a real-life example, things are going to quickly get hairy. There’s no separation of display and data, and worse, there’s also no OOP or code reusability. If you were displaying users throughout your site with the same code, any changes would require you to revisit code in many places.

Database code with display code inserted using require:

Suppose we were to use the following code snippet (in a separate PHP file) and include it using require:

displayuser.php

echo "<div class="user">";
echo "<strong>".$row['u_username']."</strong> ".
	"<em>".$row['u_firstName']." ".$row['u_lastName']."</em>";
echo "</div>";

userpage.php

$query = "SELECT u_username, u_firstName, u_lastName FROM userTable";
$users = mysql_query($query);
while($row = mysql_fetch_assoc($users))
{
  require "displayuser.php";
}

Offloading the display code into the other PHP file helps organize things a bit, and we can reuse the code snippet wherever we need to display a user. However, there are still a few drawbacks. First, we have no way of controlling the style of output. Suppose we want a “short view” of each user, with just their username. In other situations, perhaps we want a “full view” with their real name and some other biographical information. Using this approach, we would have to create two separate PHP files and include each as appropriate. Messy!

Display code inside global-scope functions

Instead of including two separate files for two different display styles, we can instead include one file at the beginning of our page’s PHP code, and call upon display functions.

displayuserfunctions.php

function userShortView($user)
{
  echo "<div class="user">";
  echo "<strong>".$user['u_username']."</strong>"
  echo "</div>";
}
 
function userLongView($user)
{
  echo "<div class="user">";
  echo "<strong>".$user['u_username']."</strong> ".
	 "<em>".$row['u_firstName']." ".$row['u_lastName']."</em>";
  echo "<strong>".$user['u_email']." ".$user['u_phone']."</strong>";
  echo "</div>";
}

userpage.php

$query = "SELECT u_username, u_firstName,
		u_lastName, u_email, u_phone FROM userTable";
$users = mysql_query($query);
while($row = mysql_fetch_assoc($users))
{
  userShortView($user);  // print short view for each user
}
while($row = mysql_fetch_assoc($users))
{
  userShortView($user);  // now print long view for each user (cheesy example)
}

We’re getting a lot closer to a workable solution. The code is organized and reusable. However, one drawback of our approach is that the data itself isn’t being handled too elegantly. It is passed blindly as an associative array into our display functions, with no regard for the “user-ness” of the data. If we wanted to do two different things with the data, say, display it and then modify the user’s properties somehow, we might end up passing the data to two different functions. We would like the user data itself to be able to have certain functionalities, and for that, we’re going to “upgrade” it to an object. The next refinement deals with this issue.

At last, the solution

The most elegant way to handle the display of our database user data is to create a User class, and move the data directly into our class using mysql_fetch_object(). This gives us many benefits, including:

• A clean model for containing the user data
• All functions are kept alongside the data they use
• The data can also be passed around elegantly as a User rather than an associative array. We are assigning meaning to the data by using a class. There might be a WorkGroup object which holds many User objects, and so on.

Here’s what a class-based setup might look like:

class User
{
  public $u_username;
  public $u_firstName;
  public $u_lastName;
  public $u_email;
  public $u_phone;
 
  function shortView()
  {
    echo "<div class="user">";
    echo "<strong>".$u_username."</strong>"
    echo "</div>";
  }
 
  function longView()
  {
    echo "<div class="user">";
    echo "<strong>".$u_username."</strong> ".
	 "<em>".$u_firstName." ".$u_lastName."</em>";
    echo "<strong>".$u_email." ".$u_phone."</strong>";
    echo "</div>";
  }
}

We would create this object my simply replacing our mysql_fetch_assoc line with:

mysql_fetch_object($users,"User")

You can even specify an array of parameters to pass to the class constructor as an optional third argument. We can already see that this is really powerful, but the real muscle-flexing comes with adding functions like these to the class:

function getFromID($id)
  {
    $id = mysql_real_escape_string($id); // better make sure it's safe!
    $query = "SELECT u_username, u_firstName,
		u_lastName, u_email, u_phone FROM userTable WHERE u_id = $id";
    $fetched_user = mysql_query($query);
    $result = mysql_fetch_object($fetched_user, get_class($this));
    foreach(get_object_vars($result) as $var => $value) $this->$var = $value;
  }
 
  function duplicateUser($newUserName)
  {
    $newUserName = mysql_real_escape_string($newUserName);
    $query = "INSERT INTO userTable
              (u_username,u_firstName,u_lastName,u_email,u_phone) VALUES
              ('$newUserName','$u_firstName','$u_lastName','$u_email','$u_phone')";
    $mysql_query($query);
  }
 
  function delete()
  {
    $query = "DELETE FROM userTable WHERE u_username = '$u_username'";
    $mysql_query($query);
  }
}

Now we have all kinds of sweet functionality in our User, and we’re head and shoulders above a simple associative array. I hope you found this article useful, and I’ll see you next time!