Page 1 of 1

Database (Re)cycling using foreach() to get Database Results Rate Topic: ***** 1 Votes

#1 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3402
  • View blog
  • Posts: 9,611
  • Joined: 08-June 10

Posted 30 September 2010 - 06:47 AM

*
POPULAR

introduction:
This tutorial will center around the usage of PDO to allow you simple access to your DB results. Speaking of simple, the comparison to SimpleXML is not that far-fetched, as both classes provide the prerequisites to do so.

what you need:
a working PHP 5 installation
basic understanding of OOP

recommended reading:
PDO
SPL Introduction (PHP iterators)
SPL API documentation (contains useful references)

preface:
As security is a strong concern of mine, I will only use Prepared Statements to access the database, as they are immune to SQL Injections. The only database extensions, which are able to handle Prepared Statements are MySQLi, PDO and the ODBC functions. MySQLi, however, does support only a very limited amount of fetch options (1 option), which IMO doesn’t outweight the advantages MySQLi has over PDO. If you’re an experienced coder, you might be able to extend MySQLi to overcome this(1), but therefore I’ll focus on PDO in my code examples.

You can use the following code to follow the animals table examples:
try {

// start table setup
// create an SQLite DB in memory
$pdo = new PDO("sqlite::memory:");
// set error mode to Exceptions
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// create 'animals' table
// there are some features in SQLite that differ from
// other databases, like dynamic typing and the use
// of AUTOINCREMENT
$pdo->exec("
    CREATE TABLE `animals`
    (
        `id`   INTEGER PRIMARY KEY,
        `name` TEXT NOT NULL,
        `type` TEXT NOT NULL
    )"
);
// insert data query
$setup = $pdo->prepare("
    INSERT INTO
        `animals` (`name`, `type`)
    VALUES
        (:name, :type)"
);
$setup->bindParam("name", $name);
$setup->bindParam("type", $species);

// table data
// you may extend this array as you see fit
$animals = array(
    "pussy"  => "cat",
    "tweety" => "bird",
    "waldi"  => "dog",
    "tom"    => "cat",
    "jerry"  => "mouse",
    "idefix" => "dog"
);

// write to DB
foreach ($animals as $name => $species)
{
    $setup->execute();
}
// end table setup

// ### the example code would go here ###

} catch (Exception $e) {
    echo $e->getLine(), ": ", $e->getMessage();
}


Chapter I – Mandatum Simplex (the simple case)

Database result fetching is among the tasks, PHP is most often used for. Unfortunately, little is known on the user base(2), that you can do that way more efficiently. In contrast, PHP frameworks do exactly that, they use advanced PHP functionality (like PDO) to let you do things with as less/efficient code as possible.

Now for the first good news, PDO does not need you to do anything for fixed result sets. Those are results, that contain one or more row, but do not require to call the execute() method repeatedly.

Example I
$ps = $pdo->prepare("SELECT `name` FROM `animals` WHERE `type` = ?");
$ps->execute(array("cat"));

foreach ($ps as $cat)
{
    echo $cat['name'], "<br>";
}

Example notes:
I used the simplified syntax, which assumes the passed data to be of string type (the default type). If you want to pass your data in other data types, you need to specify these by using PDOStatement->bindValue() or PDOStatement->bindParam().

The object of the Prepared Statement $ps (an instance of the PDOStatement class) implements PHP’s Traversable interface, which is required if you want to (sensibly) use foreach() on an object. Bear in mind that you have to call execute() before using foreach(), otherwise the PDOStatement object will not contain data to loop over (compare it to an empty array).

However, the default fetch mode (PDO::FETCH_BOTH, i.e. a named and numerically indexed array) may not always be what you need to process. By using the setFetchMode() method, you can set from the available modes the one you see fit.

Example II
$ps = $pdo->prepare("SELECT `id`, `name` FROM `animals` WHERE `type` = :type");
$ps->execute(array("type" => "cat"));

// change the fetch mode to return the value of the second column
$ps->setFetchMode(PDO::FETCH_COLUMN, 1);

foreach ($ps as $cat)
{
	echo $cat, "<br>";
}

Example notes:
You see, we no longer access the only result value (per row) via array (i.e. $row['field_name']). MySQLi provides a similar syntax via bindResult(), but that’s all, it is capable of.

Not an example that you would ever need on a website? Check this one from the [First-Class Database Results] Tutorial.

Example IIa (slightly modified)
// the query
$ps = $pdo->prepare("SELECT `name`, `text`, `date`, `home`, `twitter` FROM `blog_comments` WHERE `topic_id` = ?");

// explicitly bind a value with its specific data type
$ps->bindValue(1, $_GET["topic"], PDO::PARAM_INT);

// fetch the result as object of a given class
$ps->setFetchMode(PDO::FETCH_CLASS, "Blog_Comment_Social");

// print out the comments
echo "<table class='comment'>";
foreach ($ps as $comment)
{
    echo $comment;
}
echo "</table>";


So far, so good. But what, if your Prepared Statement does not fall under the previous constraints, that is you fetch the rows one by one? This may be the case if you fetch a larger amount of data, which may exceed the DB or PHP memory limit when fetched all at once.

Chapter II – Mandatum Complex (the not so simple case)

In this case, you cannot directly loop over the result (well, you can, but you would get exactly one row), because you executed the Statement only once(3). Here is where PHP’s predefined interfaces directly come into play. They allow you to create an object, which can be used in foreach(). What you need to implement in the interface methods is a way to call execute() and fetch() to return the result you desire.

To use an object in foreach(), it must either implement the Traversable interface natively (like PDOStatement) or implement an iterator interface(4).

At this point, you may realize that creating a wrapper around PDO is more suitable, than using PDO directly. This is more for programming design reasons, because it allows you to apply Design Patterns (e.g. Singleton (always use a single instance, even in various places)) and ease the initialization of the DB connection (you hardly use more than one database on a website).

However, I will not go into detail about that, as it would go beyond the scope of this tutorial. Unfortunately it is not sensible to extend the PDOStatement class itself, because you cannot create an object via the PDO class (prepare() method) other than a PDOStatement object.

Therefore the only choice left is to use an object, that can access the required PDOStatement methods we need. Luckily, we only need PDOStatement’s public methods.

Back to the Iterator interface. Below you find a listing of the methods we have to implement. You may note, that three of its methods are named after array functions: current(), key() & next(). This is not coincidence, since you can also handle arrays in foreach(). The rewind() method corresponds to the reset() function. The valid() method is required, because you can use iterable objects in any other loop construct (while()). rewind() is used to start at the beginning and valid() determines, whether another loop cycle is possible).

Listing 1
// taken from the PHP manual
Iterator extends Traversable {
    /* Methods */
    abstract public mixed current ( void )
    abstract public scalar key ( void )
    abstract public void next ( void )
    abstract public void rewind ( void )
    abstract public boolean valid ( void )
}

Let us fill this interface with some life. That is, create a class and define the actions/methods to execute in foreach():

Example III
class DBIterator implements Iterator
{
    protected 
        $PS    = NULL,    // what to fetch
        $par   = array(), // what to execute
        $valid = false
    ;

    public function __construct(
        PDOStatement $ps,
        Array        $params
    )
    {
        $this->PS  = $ps;
        $this->par = $params;
    }
    
    public function current()
    {
        $this->PS->execute( current($this->par) );
        return $this->PS->fetch();
    }
    
    public function key()
    {
        return current( current($this->par) );
    }
    
    public function next()
    {
        $this->valid = (false !== next($this->par));
    }
    
    public function rewind()
    {
        $this->valid = (false !== reset($this->par));
    }
    
    public function valid()
    {
        return $this->valid;
    }
}

Example notes:
The implementation of the Iterator methods is taken from Introduction to SPL. Of course, you are free to use or program any implementation you like.

Listing 2
public function current()
{
    $this->PS->execute( current($this->par) )
    return $this->PS->fetch();
}

This function is of particular interest. It will provide the value of $item in foreach ($obj as $item). It is responsible for the execution of the Prepared Satement with the current value of the provided parameter array as well as getting the result value. For this to work, we need to make sure that
  • $this->par is always an array (otherwise current() won’t work(5))
  • $this->PS has an execute() and a fetch() method


Both prerequisites are checked in the class’ constructor via Type Hinting:

Listing 3
public function __construct(
    PDOStatement $ps,
    Array        $params
)


The PDOStatement class’ execute() method expects an array, that is, each of the $this->par array members must be an array itself. Additionally, this implementation does not support data types other than strings as parameter values. If you want more flexibility in what you can pass to execute(), you need to write your own implementation of fetching data through PDO … Did I mention that a wrapper around PDO would be a good idea?

Example IV
$ps = $pdo->prepare("SELECT `name` FROM `animals` WHERE `id` = ?");

// change the fetch mode to return the value directly
$ps->setFetchMode(PDO::FETCH_COLUMN, 0);

// the IDs of the animals of interest
$par = array(array(1), array(3), array(5));

// creating the iterator object
$set = new DBIterator($ps, $par);

// print result
foreach ($set as $id => $name)
{
    echo $id, ": ", $name, "<br>";
}

Do you remember Example II? There is basically one line changed. Instead of executing the PDOStatement, we create an iterable object, which re-executes the PDOStatement with each of the parameter array’s members.

That's it for today and I hope I could show, that it doesn’t take much to get DB results with barely any code.


  • follow the MySQLi discussion about that in [this thread]
  • I am sorry, if that statement is offending, but it is reality. Part of that problem is that old and outdated tutorials are not replaced by more actual ones (the PHP manual itself states about mysql that "[…] it is strongly recommended that you use the mysqli extension instead." (ref.))
  • PDOStatement only calls its fetch() method in a foreach() loop, not its execute() method.
  • The Traversable interface is for internal use only, for manual implementation use either the Iterator, IteratorAggregate or ArrayAccess interface. (ref.)
  • to be exact, we only need it to work with current(), thus an iterable object would be fine as well. The same applies to the other functions we use. Since we use reset(), an Array is the only construct that fulfills the requirements.

This post has been edited by Dormilich: 15 March 2011 - 11:39 PM


Is This A Good Question/Topic? 6
  • +

Replies To: Database (Re)cycling

#2 Jstall  Icon User is offline

  • Lurker
  • member icon

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

Posted 16 October 2010 - 10:06 AM

Good tutorial and examples of using PDO. I agree completly with what you said in the second item of your footer. I know that when I started learning PHP about six months ago all the tutorials I read were using older deprecated php functions for working with databases. In fact the project(my first job) I am currently working on is based on old PHP4 code so we are still using them today(I have been subtly suggesting an overhaul). I have began using PDO for a small personal project and tutorials like this are invaluable, thanks much!
Was This Post Helpful? 0
  • +
  • -

#3 codeprada  Icon User is offline

  • Changed Man With Different Priorities
  • member icon

Reputation: 943
  • View blog
  • Posts: 2,353
  • Joined: 15-February 11

Posted 12 June 2011 - 08:19 PM

You should get more rep for this post. Your tutorials on PDO opened my eyes to what I've been missing while using MySQLi.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1