TM::MyCSV
A text file based database complement for PHP 4 and 5

August 2009
Winner

This class handles standard CSV or TXT text files as they where database tables. It supports most benefits of both SQL tables and PHP arrays. It doesn't need a real database management system nor does it require any knowlege of the SQL language. It hides all file system functions so you don't have to deal with file pointers, field delimiters, escape sequences and so on. Because it uses the widespreaded standard CSV file format, you are able to create, read and update the tables using any spreadsheet software (e.g. Excel). It supports user defined table sort similar to ORDER BY, auto incremented ID numbers, limitation and joins similar to LIMIT and LEFT OUTER JOIN, it's binary safe (uses work arounds for all known fgetcsv() related bugs) and lots more.

TM::MyCSV is optimized to be used with the minimum amount of source code. For example, create a file called “table.csv” with the following content:

id,value
3,Example
4,Another value
7,Blue

Now, call this script to iterate the file contents and display all records:

<?php

require_once("MyCSV.class.php");

$table = new MyCSV("table");
while ($row = $table->each())
{
    echo $row['id'] . " is " . $row['value'] . "<br>";
}

Note: There is an other project on the web that used the name “MyCSV” for a short time in 2003. Note that I started my project earlier. It's up to you to decide which project is better maintained and easier to use.

Licensing

The class is open source (of course) and free for non-commercial use. If you want to use it in a commercial project, please ask for a personal license.

Download

Download the TM::MyCSV PHP class (version from 2009-09-02)

More examples

Sort (order by) example

The method order() orders the table rows by one or more columns.

Sorting order and type flags:

ASC or SORT_ASC – Sort in ascending order (default).
DESC or SORT_DESC – Sort in descending order.
SORT_REGULAR – Compare items normally (default).
SORT_NUMERIC – Compare items numerically.
SORT_STRING – Compare items as strings.
SORT_NAT – Compare items using a “natural order” algorithm.
SORT_NULL – Move empty elements to the end.

You can use a syntax similar to SQL, if you want. Some usage examples:

$table->sort("a, b DESC");
$table->sort("a b DESC");          // Same as above
$table->sort("a", "b", SORT_DESC); // Same as above
$table->sort("a SORT_STRING SORT_NULL b SORT_NULL");
$table->sort("a SORT_NAT, b SORT_NAT, c");

Seek example

The method seek() sets the internal pointer to the data row specified by an ID or an absolute or relative offset (a row number).

$table = new MyCSV("table");
$table->insert(array('id' => 3)); // Row number 0
$table->insert(array('id' => 7)); // Row number 1

$table->seek(1, SEEK_SET);        // Jump row number 1
$row = $table->fetch_assoc();
echo $row['id'];                  // Displays "7"
$table->seek(7);                  // Jump to ID 7

Left outer join example

The method join() performs a left outer join with another table.

The tables are merged using a foreign key from the left table and the primary key from the right table. This adds temporary columns to the left table (temporary means, they aren't stored using write()). A slightly complex example:

$rightTable = new MyCSV();
$rightTable->insert(array('id' => 7, 'color' => "red"));
$rightTable->insert(array('id' => 8, 'color' => "yellow"));

echo "<pre>";
$rightTable->dump();
echo "\n";

$leftTable = new MyCSV();
$leftTable->insert(array('thing' => "Table", 'color_id' => 7));
$leftTable->insert(array('thing' => "Chair", 'color_id' => 8));
$leftTable->insert(array('thing' => "Lamp",  'color_id' => 7));

$leftTable->dump();
echo "\n";

$leftTable->join($rightTable, "color_id");

while ($row = $leftTable->each())
{
    echo $row['thing'] . " is " . $row['color'] . "\n";
}

Limit (top) example

The method limit() limits the number of rows to be fetched.

Use limit(2) to fetch the first two rows only when calling each() (or fetch_assoc()). Use limit(2, $id) to fetch the next two rows, where $id is calculated using first() for the first page and using next($id, 2), next($id, 4) and so on for all other pages. Example:

$table = new MyCSV("table");
for ($i = 10; $i < 21; $i++)
{
    $table->insert(array('text' => "Text $i"));
}

// Order the table first because limit() depends on this.
$table->sort("text DESC");

// Limit to 5 rows starting from a specific id.
$rows = 5;
$id = isset($_REQUEST['id']) ? $_REQUEST['id'] : $table->first();
$table->limit($rows, $id);

while ($row = $table->each())
{
    echo "ID $row[id]: $row[text]<br>";
}

// Calculate and display the link targets for paging/pagination.
$first = $table->first();
$prev  = $table->prev($id, $rows);
$next  = $table->next($id, $rows);
$last  = $table->prev($table->last(), ($table->count() - 1) % $rows);
if (strcmp($first, $id)) echo "<a href=\"$PHP_SELF?id=$first\">First</a> ";
if ($prev)               echo "<a href=\"$PHP_SELF?id=$prev\">Prev</a> ";
if ($next)               echo "<a href=\"$PHP_SELF?id=$next\">Next</a> ";
if (strcmp($last, $id))  echo "<a href=\"$PHP_SELF?id=$last\">Last</a>";

Call limit() (or limit(0) or something like that) to reset the limitation.

Dump example

The method dump() dumps the table to screen. Example:

$table = new MyCSV("people");
$table->insert(array('name' => "Adam", 'age'  => 23));
$table->insert(array('name' => "Bill", 'age'  => 19));

echo "<pre>";
$table->dump();

This displays the following CSV file contents, even if the file “people.csv” does not exist:

id,name,age
1,Adam,23
2,Bill,19

TM::MyCSV A text file based database complement for PHP 4 and 5