Traversing Hierarchy Tree Using PHP: Adjacency List Model

Relational databases represent data as rows and columns inside a table; however, this representation is not suitable for hierarchical data. Certain models are available that allow you to store hierarchical data in these databases. One such method is the Adjacency List model. This article shows you how to manipulate adjacency list implementation of hierarchical (tree) data using PHP.

Hierarchical data consists of elements that have parent-child relationships. It can be visualized as a tree-like structure where each node can have one parent and many children. A node that does not have a parent is called root node while a node that does not have any children is called leaf node. Any connecting node is called branch node.

Adjacency list model is a naive solution used (unknowingly) by majority of programmers. In this implementation, each row stores a reference to its parent. This simplifies most database operations except for SELECTing a subtree (remember that the depth of the subtree is unknown).

Since a tree is a recursively defined data structure, it can be traversed naturally using recursion. The code demonstrated in this article uses (i) one query (ii) array to store all results (iii) index for faster iteration (iv) recursion. It does not use references or multiple queries.

SQL Query to Find Overlapping (Conflicting) Date Ranges

Date ranges are often stored in a database table as a pair of datetime columns; for example, start_date and end_date. A common operation on date ranges is to find those that lie outside or overlap a given date range. A hotel reservation system for example might need to check if a room is available from Jun/1/2012 5:00 PM to Jun/3/2012 11:00 AM before confirming. Writing a query for this is much easier than you expect.

Export MySQL Data to CSV Using PHP

15 minutes into writing this article, I realized that PHP 5.1 now includes the fputcsv function that was missing from earlier versions of PHP. It formats a line (passed as a fields array) as CSV and write it (terminated by a newline) to the specified file handle. However, it is not properly documented what exactly is the "newline" character; is it CR, LF or CRLF.

I have a habit of re-inventing the wheel, so I've put together a substitute for this function that echoes the CSV data instead of writing to a file and (tries to) closely follow the recommendations described in RFC 4180.