Page 1 of 1

First-Class Database Results using OOP for printing out DB results Rate Topic: ***** 1 Votes

#1 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3530
  • View blog
  • Posts: 10,172
  • Joined: 08-June 10

Posted 25 June 2010 - 01:32 AM

*
POPULAR

First-Class Database Results

Today I’m going to talk about a useful feature of MySQLi and PDO. The ability to fetch a result as an object. You might ask “Why would I need that?”—you can hopefully answer that question later.

But first I like to set up an useful example. Imagine you have a blog or article page with some comments underneath. Those comments are usually stored in a database and requested on page load (AJAX would also be possible, but even that’ s building upon database requests.

A very simple HTML code for the comments would look like
<table id="comments">
	<tr>
		<td>John said:</td>
		<td>12/24/2010</td>
	</tr>
	<tr>
		<td colspan="2">merry christmas</td>
	</tr>

	<tr>
		<td>Jack said:</td>
		<td>12/31/2010</td>
	</tr>
	<tr>
		<td colspan="2">happy new year</td>
	</tr>
</table>


with a database table setup of
id       INT(10) AUTO_INCREMENT 
name     VARCHAR(20)
text     TEXT
date     DATE
homepage VARCHAR(50)
icq      INT(10)
twitter  VARCHAR(50)
email    VARCHAR(50)
topic_id INT(10)



The standard approach of writing that would be something like (excluding the connecting—I concentrate on fetching data here):

echo '<table id="comments">';
while ($row = mysql_fetch_assoc($result))
{
	echo "
	<tr>
		<td>{$row['name']} said:</td>
		<td>{$row['date']}</td>
	</tr>
	<tr>
		<td colspan='2'>{$row['text']}</td>
	</tr>";
}
echo '</table>';



A good improvement would include templating this and call that template each time you want to output data. A big advantage of that is the ability to easily change the output (say, we want to add a link to the posters hompage or twitter account).

$tr = '
	<tr>
		<td>%s said:</td>
		<td>%s</td>
	</tr>
	<tr>
		<td colspan="2">%s</td>
	</tr>';

// …

echo '<table id="comments">';
while ($row = mysql_fetch_assoc($result))
{
	printf($tr, $row["name"], $row["date"], $row["text"]);
}
echo '</table>';



You see our code is looking way cleaner, and if we like to change the HTML source we don’t have to wade through all of our code (we only need to know where the template definition for printf() is). We are now even able to change data before output (e.g. adjusting the date format via the date() function).

Still, we can get even more effective.

What’s cluttering up PHP scripts most of the time is, that so much programming logic is put into the output script, making for endless lines of code (of course you can wrap it up in functions …).

MySQLi and PDO on the other hand side, provide us with a powerful OOP interface.

I’ll use PDO in the examples, for the sole reason, that PDO throws an Exception, if there’s an error (MySQLi doesn’t throw any Exceptions anywhere, so error handling is a bit more difficult).

Before we can fetch something into an object, we need to define that object first. I choose a two class approach, where I have a base class, that holds all the properties possible (representing all the fields in the database) and a child class holding the actual template (the HTML code for output).* changing the output code becomes simply a matter of choosing another class.

class Comment
{
	protected 
		$id       = "",
		$name     = "",
		$date     = "",
		$text     = "",
		$homepage = "",
		$icq      = "",
		$twitter  = "",
		$email    = ""
	;
	
	protected function replace(
		$template
	)
	{
		$str = $template;
		$str = str_replace("_%NAME%_", $this->name, $str);
		$str = str_replace("_%TEXT%_", $this->text, $str);
		$str = str_replace("_%DATE%_", $this->date, $str);
		$str = str_replace("_%WEB%_",  $this->homepage, $str);
		$str = str_replace("_%ICQ%_",  $this->icq, $str);
		$str = str_replace("_%TWIT%_", $this->twitter, $str);
		$str = str_replace("_%MAIL%_", $this->email, $str);
		return $str;
	}
}

class Blog_Comment extends Comment
{
//	public static $TEMPLATE = '
	const TEMPLATE = '
	<tr>
		<td>_%NAME%_ said:</td>
		<td>_%DATE%_</td>
	</tr>
	<tr>
		<td colspan="2">_%TEXT%_</td>
	</tr>';

	// format the date to american date format
	public function __construct()
	{
		$this->date = date("n/j/Y", $this->date);
	}

	// this is the interesting part: call this method, if the object is printed (via echo, print or (string))
	public function __toString()
	{
		return $this->replace(self::TEMPLATE);
	}
}



This kind of template has the advantage over sprintf(), that it is easier to construct (I prefer _%NAME%_ over %1$s).

In a real project, there would probably exist a wrapper around the PDO/MySQLi code to ease querying and error handling. To be honest, databases handling is predestined for OOP wrappers. But now for something completely different … the database code.

// assuming you have already defined the constants DB_SERVER, DB_NAME, DB_USER & DB_PASS
try
{
	$class_name = "Blog_Comment";
	
	$dsn = 'mysql:host=' . DB_SERVER . ';dbname=' . DB_NAME;
	$pdo = new PDO($dsn, DB_USER, DB_PASS);
	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
	$comment = $pdo->prepare("SELECT `name`, `date`, `text` FROM `blog_comments` WHERE `topic_id` = ?");
	$comment->bindValue(1, $_GET['topic'], PDO::PARAM_INT);
	$comment->execute();
	$comment->setFetchMode(PDO::FETCH_CLASS, $class_name);
	
	echo '<table id="comments">';
	foreach ($comment as $entry)
	{
		echo $entry;
	}
	echo '</table>';
}
catch (PDOException $p)
{
	// log error
	// for simplicity, I just echo the Exception’s message
	echo '<p>', $p->getMessage(), '</p>';
}



As you can see, we’ve completely transferred the HTML code out of the program’s logic. If wee need another setup, we create the appropriate class and use its name.

class Blog_Comment_Social extends Comment
{
	const TEMPLATE = '
	<tr>
		<td colspan="2">on _%DATE%_, _%NAME%_ said:</td>
	</tr>
	<tr>
		<td colspan="2">_%TEXT%_</td>
	</tr>
	<tr>
		<td>
			<a href="_%WEB%_">
				<img src="icons/homepage.png" width="20" height="20" alt="my homepage">
			</a>
		</td>
		<td>
			<a href="http://twitter.com/_%TWIT%_">
				<img src="icons/twitter.png" width="20" height="20" alt="follow me on Twitter">
			</a>
		</td>
	</tr>';

	// format the date to RFC 2822 format
	public function __construct()
	{
		$this->date = date("r", $this->date);
	}

	public function __toString()
	{
		return $this->replace(self::TEMPLATE);
	}
}



This is all fine, unless your query has an awful lot of result sets. Then you might run into memory problems (another reason why you should always name the fields you fetch explicitly instead of using "*"). Again, Prepared Statements come to rescue. Because Prepared Statements only transmit data (except for the initial query, where the SQL code is transmitted), it doesn’t matter if we fetch all sets at once each set separately. Memory friendly.

// assuming you have already defined the constants DB_SERVER, DB_NAME, DB_USER & DB_PASS
try
{
	$class_name = "Blog_Comment";
	
	$dsn = 'mysql:host=' . DB_SERVER . ';dbname=' . DB_NAME;
	$pdo = new PDO($dsn, DB_USER, DB_PASS);
	$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
	// code for fetching a single set
	$comment = $pdo->prepare("SELECT `name`, `date`, `text` FROM `blog_comments` WHERE `id` = :id");
	$comment->bindParam(":id", $id, PDO::PARAM_INT);
	$comment->setFetchMode(PDO::FETCH_CLASS, $class_name);
	
	// code to fetch only the relevant entry ids. 
	$IDs = $pdo->prepare("SELECT `id` FROM `blog_comments` WHERE `topic_id` = ?");
	$IDs->bindValue(1, $_GET['topic'], PDO::PARAM_INT);
	$IDs->setFetchMode(PDO::FETCH_COLUMN);
	$IDs->execute();
	
	echo '<table id="comments">';
	foreach ($IDs as $id)
	{
		$comment->execute();
		echo $comment->fetch();
	}
	echo '</table>';
}
catch (PDOException $p)
{
	// log error
	// give a user friendly notice
	echo "<p>No comments available</p>";
}



At first it may seem tedious or over-complicated to use more lines than you’re used to, but we gain more than we loose. We have Content/Programme Code Separation, we are absolutely safe of SQL Injection and we have a relatively solid programme design (it could be improved, though).


* - separation of View (HTML) and Model (data)

This post has been edited by Dormilich: 24 June 2011 - 02:50 AM


Is This A Good Question/Topic? 8
  • +

Replies To: First-Class Database Results

#2 Guest_Ben*


Reputation:

Posted 29 June 2010 - 04:27 PM

Good sample of PDO, but you can do this with the regular mysql_fetch_object function too.
Was This Post Helpful? 0

#3 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3530
  • View blog
  • Posts: 10,172
  • Joined: 08-June 10

Posted 29 June 2010 - 09:21 PM

View PostBen, on 29 June 2010 - 11:27 PM, said:

but you can do this with the regular mysql_fetch_object function too.


there are always possibilities to do it another way (e.g. by MySQLi/MySQLiStatement), but even the PHP manual considers the mysql_* functions as outdated … and you’re sacrificing security.

This post has been edited by Dormilich: 29 June 2010 - 09:21 PM

Was This Post Helpful? 0
  • +
  • -

#4 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Posted 22 March 2011 - 08:11 AM

Interesting means of templating. I've not seen it done like that before, the replacements happening within the class itself. You could still utilize this by keeping the actual template files as seperate HTML and pass them in as parameters after using file_get_contents. Nice tutorial.
Was This Post Helpful? 1
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3530
  • View blog
  • Posts: 10,172
  • Joined: 08-June 10

Posted 22 March 2011 - 08:50 AM

View PostJstall, on 22 March 2011 - 04:11 PM, said:

You could still utilize this by keeping the actual template files as seperate HTML and pass them in as parameters after using file_get_contents. Nice tutorial.

I have been too lazy to do that along with some other improvements that could have been done.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1