Example usage of flatfile class
The Flatfile package is a small but very poweful set of classes for
database like access to text "flat files". It provides equivalents to
many of the common SQL commands.
Below is an extended example of how to use Flatfile and the related
classes. The example is a table that holds 'posts' (such as a message on
a message board), each with an ID, the title, the date, the author and the
text of the post.
First we will set up some constants for the columns (not required,
Now we need to create the database object and set it up. $datadir is set to the directory where all the tables
$db->datadir = 'data/';
To get all rows from a table, use selectAll():
$allrows = $db->selectAll('posts.txt');
The result will be an array of arrays: $allrows[r][c] will contain
the data for row r, column c, where r and c are integers (zero based
To get a single row that is identified by a unique field, use
$aSingleRow = $db->selectUnique('posts.txt', POST_ID, '1234');
The result is a single array of the values in the specified row.
To do a simple WHERE clause, e.g. get all posts from user 'joe',
use selectWhere() and SimpleWhereClause:
$rows = $db->selectWhere('posts.txt',
The output is an array of arrays, as for selectAll().
To build a complex criteria that will select rows from user 'joe'
made after the first of February 2005 (assuming the date column stores
You can also build the composite where clause in the constructor:
To use the clause, and only return the first 5 posts, sorted in date order descending, do this:
$rows = $db->selectWhere('posts.txt', $compClause, 5,
(This introduces the $limit and $orderBy parameters of selectWhere())
You can of course use any WhereClause object as part of a
CompositeWhereClause and so can have a combination of AND and OR
The 'WhereClause' method can be used with deleteWhere()
and updateSetWhere(). Other WhereClause classes are available and if they are not
sufficient you can create your own very easily.
You can order on multiple fields by supplying an array of OrderBy objects instead of a single one.
To insert a row, use insert() or insertWithAutoId(). The latter will do an
auto-increment on a specified field, and will return the newly generated
$newpost[POST_ID] = '0'; // dummy
$newpost[POST_TITLE] = 'A great post';
$newpost[POST_BY] = 'Me';
$newpost[POST_TEXT] = 'I have discovered a truly wonderful cure to cancer
which this line of code is too small to contain';
$newId = $db->insertWithAutoId('posts.txt', POST_ID, $newpost);
To set a number of fields in a table, you can use updateSetWhere(). Suppose user joe has a sex
$db->updateSetWhere('posts.txt', array(POST_BY => 'jane'),
The second parameter can contain any number of fields (not just one
as above), or even the complete row.
deleteWhere() works in a similar
fashion to updateSetWhere() and selectWhere(). 'joe', or should I say
'jane', later decides that she wants to disown all posts she made while
still a man:
OrderBy and SimpleWhereClause use the constants
STRING_COMPARISON, NUMERIC_COMPARISON and INTEGER_COMPARISON to qualify
ordering and comparison operations. However, these constants are in fact
just the names of functions that do the comparison of two variables. You
can therefore use your own function or a builtin PHP function wherever
one of these constants can be used. This means, for example, that you
can use strcasecmp() to do case insensitive string
This will match all posts by joe or JOE or jOe:
You can of course create your own function, providing it works equivalently