PHP Classes

TM::MyCSV: Manage CSV files like database tables

Recommend this page to a friend!
     
  Info   View files Example   Screenshots Screenshots   View files View files (12)   DownloadInstall with Composer Download .zip   Reputation   Support forum (1)   Blog (1)    
Ratings Unique User Downloads Download Rankings
StarStarStarStar 72%Total: 1,935 This week: 1All time: 2,035 This week: 75Up
Version License PHP version Categories
mycsv 1.0.0Free for non-comm...4.0.5Databases, Files and Folders, Content...
Description 

Author

Thiemo Kreuz

This package can be used to manage CSV text files like database tables. The class combines most benefits of SQL tables and PHP arrays including a powerfull method for sorting rows.

The class manages CSV files with support for storing, retrieving and deleting rows of data and also to add and remove columns from those files.

Inserted row identifier column values may be auto-incremented. The row retrieval is binary safe, may obey a given column sorting criteria similar to SQL ORDER BY clause, limit the number of retrieved rows similar to LIMIT clause, and support joins like with SQL LEFT OUTER JOIN.

A separate sub-class allows importing and exporting data to MySQL database tables.

Another class can generate a HTML forms based administration interface for accessing and editing data from a directory of CSV files, as well as alter their columns.

Innovation Award
PHP Programming Innovation award winner
August 2009
Winner

Prize: One copy of the Zend Studio		 PHPMyAdmin is an application that became very popular because it provides a Web based user interface to let the users manager MySQL databases used by PHP Web sites.

This package provides a similar Web based user interface for managing data in CSV files.

Manuel Lemos
Picture of Thiemo Kreuz
  Performance   Level  
Name: Thiemo Kreuz <contact>
Classes: 2 packages by
Country: Germany Germany

Level 1 Star

 Innovation award
Innovation award
Nominee: 1x

Winner: 1x

Instructions

This class is written to use with PHP 4 and no longer works with newer PHP versions. So, please do not use it with newer PHP versions.

Example

<?php

/**
 * This is a complex example script for the TM::MyCSV class. It shows how to
 * create and change a table, how to use insert(), delete() and sort().
 *
 * @author Thiemo Mättig (http://maettig.com/)
 */

// The method sort() depends on your locale settings if you use the sorting type
// flag SORT_LOCALE_STRING.
setlocale(LC_ALL, "de_DE@euro", "de_DE", "deu_deu");

// Include the PHP class first.
require_once("MyCSV.class.php");

// Read the file products.csv if exists. If you run the script for the first
// time, the write() call below will create the file if required.
$products = new MyCSV("products");

if (isset($_REQUEST['action']) && $_REQUEST['action'] == "delete")
{
    $products->delete($_REQUEST['id']);
    $products->write();
}

// Check if something was submitted in the form.
if (!empty($_POST['name']))
{
    // Add a new row to the table. If the file does not exist and the table
    // contains no fields, this call also creates the fields. Not that all
    // TM::MyCSV tables will contain an 'id' column. Every row you add to the
    // table will get a new ID (similar to AUTO_INCREMENT from MySQL).
    $products->insert(array(
        'name' => stripslashes($_POST['name']),
        'price' => stripslashes($_POST['price']),
    ));
    // Save the whole table to disk.
    $products->write();
}

// Check if the user clicked one of the table headers.
$sort = isset($_REQUEST['sort']) ? $_REQUEST['sort'] : "";
$desc = isset($_REQUEST['desc']) ? " DESC" : "";

// It's a very bad idea to use something that the user entered in your SQL
// statements. This leads to SQL insertions. We do a translation instead. All
// invalid values, hacking attempts and so on will fall back to the default
// behaviour.
switch ($sort)
{
    case "name":
        // Order by name in ascending or descending order. Two products with the
        // same name will be ordered by price.
        $products->sort("name SORT_LOCALE_STRING SORT_NULL $desc, price SORT_NUMERIC");
        break;
    case "price":
        // Order by price using a numeric comparison. SORT_NULL moves products
        // without a price to the bottom.
        $products->sort("price SORT_NUMERIC SORT_NULL $desc, name SORT_LOCALE_STRING");
        break;
    default:
        // Ordered by ID ("unordered") by default.
        if (!$desc)
        {
            $products->ksort();
        }
        else
        {
            $products->krsort();
        }
        break;
}

// This is the end of the controller component of the script and the start of
// the view component. Check http://en.wikipedia.org/wiki/Model–view–controller
// if you don't know what MVC is. You don't need the frameworks mentioned there.
// The idea of MVC is simple: Prepare everything in the controller part (above
// these lines) and display everything in the view part (below these lines).
// Using code in the view part is not forbidden. However, it's forbidden to
// change data.

?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html lang="de" xml:lang="de" xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1" />
<title>TM::MyCSV Example Script</title>
<meta name="robots" content="none" />
<style type="text/css">

/* A little bit of CSS to make the example look nice. */

body
{
    color: #333;
    font-family: Verdana, sans-serif;
}
form div{
    margin: 0.5em 0;
}
label
{
    cursor: pointer;
    float: left;
    padding-right: 0.6em;
    text-align: right;
    width: 10em;
}
table
{
    border-collapse: collapse;
    border-spacing: 0;
    empty-cells: show;
}
th, td
{
    border-bottom:1px solid #CCC;
    padding: 0.4em 1em;
    text-align: left;
    vertical-align: top;
}
th, th a
{
    background: #666;
    color: #FFF;
}

</style>
</head>
<body>

<h1>TM::MyCSV Example Script</h1>

<!-- In most cases I use my TM::Apeform class to create such forms. In this
     case, I don't want another dependency in this example script. -->

<form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
    <fieldset>
        <legend>Add a new product</legend>
        <div>
            <label for="name">Product name:</label>
            <input id="name" name="name" type="text" />
        </div>
        <div>
            <label for="price">Price:</label>
            <input id="price" name="price" type="text" />
        </div>
        <div>
            <label>&nbsp;</label>
            <input type="submit" value="Add new product" />
        </div>
    </fieldset>
</form>

<table>
    <tr>
        <th>ID</th>
        <th>
            <a href="<?php echo $_SERVER['PHP_SELF']?>?sort=name<?php
            if ($sort == "name" && !$desc) echo '&amp;desc'?>">Product name</a><?php
            if ($sort == "name") echo $desc ? '&#x25BC;' : '&#x25B2;'?>
</th>
        <th>
            <a href="<?php echo $_SERVER['PHP_SELF']?>?sort=price<?php
            if ($sort == "price" && !$desc) echo '&amp;desc'?>">Price</a><?php
            if ($sort == "price") echo $desc ? '&#x25BC;' : '&#x25B2;'?>
</th>
        <th></th>
        </tr>

<?php

// Sometimes it is easier to use echo statements instead of the mixed HTML and
// PHP syntax above. I use both depending on the amount of HTML code.

while ($product = $products->each())
{
    echo '<tr>';
    echo '<td>' . htmlspecialchars($product['id']) . '</td>';
    echo '<td>' . htmlspecialchars($product['name']) . '</td>';
    echo '<td>';
    if (empty($product['price']))
    {
        echo '&mdash;';
    }
    else
    {
        echo htmlspecialchars($product['price']) . ' &euro;';
    }
    echo '</td>';
    echo '<td><a href="' . $_SERVER['PHP_SELF'] . '?action=delete&amp;id=' .
        htmlspecialchars($product['id']) . '">Delete</a></td>';
    echo '</tr>';
}

?>

</table>

</body>
</html>

Details

TM::MyCSV Change Log

2009-09-02
    * sort() accepts SORT_TIME to order a table by a column that contains
      textual date and/or time values like "January 1 2009". Use SORT_NUMERIC if
      you use Unix timestamps and SORT_DEFAULT or SORT_STRING if you use ISO
      dates like I do in this change log.
    * Two example scripts added.

2009-09-01
    * sort() accepts SORT_LOCALE_STRING. Don't forget to use setlocale() before.
    * SORT_NULL fixed. The number 0 and the string "0" should not be handled as
      NULL values. Instead of a boolean comparison we use strlen() now.
    * SORT_STRING fixed. Values like "2a" and the number 2 have been compared as
      numbers. This is an intended PHP behaviour, but should happen with
      SORT_REGULAR only.
    * add_field() accepts field names with Umlauts and other characters from
      0x7F to 0xFF.
    * example_products.php added.

2008-06-19
    * row_exists() destroyed the data array in PHP 5. The reason is unknown (a
      bug in PHP?).

2005-11-21
    * add_field() fixed (failed with $afterField = "unknown column").
    * Some tiny code beatyfications and speed-ups.

2005-08-07
    * There is a new bug in fgetcsv(). All double quotes are stripped from the
      beginning and/or end of the values. I added another workaround to solve
      this behaviour (improved the "smart" backslashes).
    * Fixed a tiny bug in insert(). Auto incrementation of ID "x" returned "y"
      but should return a numeric key.
    * test.php: Added more test cases.

2005-06-30
    * Added third argument to limit(), e.g. limit(3, 9, SEEK_SET) for the old
      MySQL behaviour.

2005-05-08
    * Full support for remote files added (read-only of course). Works only with
      comma separated files by default. Use $t = new MyCSV(); $t->delimiter =
      ";"; $t->read("http://..."); to read a remote file with another delimiter.
    * Changed data_seek(), it's an alias for seek(..., SEEK_SET) now.

2005-04-11
    * Changed and improved seek() and limit() a lot. Both use absolute IDs
      instead of relative offsets now. WARNING! These changes are incompatible
      to previous versions of the class! limit() is NOT compatible to MySQL's
      LIMIT any more! Set $whence to SEEK_SET for the old behaviour.

2005-03-19
    * insert() and update() return false if $data is not an array.

2004-09-18
    * Added an other solution for the deadlock problem in read(). Uses the
      blocking flock() again. Triggers a warning if a file is opened twice.
    * write() returns false if the filename is empty.
    * "9a" == 9 returns true! Now the class uses strcmp() instead.

2004-08-24
    * Auto increment feature in insert() is a lot faster now.
    * first() and last() rewritten.
    * write() is little bit faster now (uses str_replace instead of strtr).
    * close() improved.
    * PHPDoc comments updated, @requires added.

2004-08-23
    * write() fixed, ids may contain the delimiter character now.
    * Auto increment feature in insert() slightly improved.

2004-07-31
    * first() and last() fixed, return false if the table is empty.

2004-06-08
    * prev() fixed.

2004-03-08
    * Tiny speed-ups in export and write() (about 30% faster).

2004-02-15
    * sort() uses the object method array(&$this, '_cmp') instead of the static
      class method array('MyCSV', '_cmp') now.

2004-02-08
    * Introduced read(). You may call "new MyCSV()" and "read(tablename)"
      instead of "new MyCSV(tablename)" now.
    * Locked files (Windows) made read() fail (is_writeable() returned true).

2003-12-28
    * limit() added (for each() and fetch_assoc() only!).
    * The array in insert(), update(), row_exists() is not expected by ref. any
      more. insert(array(...)) was impossible due to this.

2003-11-19
    * $insert_id is private. Use insert_id() instead.

2003-11-10
    * "Smart" backslashes added. This makes the class compatible to any PHP
      version, including >=4.3.2 and 5.x. Critical characters \x00 and \ are
      "smart" escaped now.
    * Bugfix: close() caused errors if file does not exist.

2003-11-03
    * Fixed tiny "Notice" bug in export().

2003-10-29
    * sort() allows to use SORT_NULL in combination with any other flag, e.g.
      sort("a SORT_NAT SORT_NULL b SORT_NUMERIC SORT_NULL").

2003-10-28
    * Bugfix: update() didn't handled numeric field names properly.
    * Speeded up insert() and update() a bit.

2003-10-27
    * Works in both PHP <=4.3.1 and >=4.3.2 now (fgetcsv() was changed).
    * Added parameter MyCSV(..., length), defaults to 10k instead 100k now.
    * Bugfix: drop_field("id") was allowed by mistake, returns false now.

2003-10-26
    * flock() in MyCSV() doesn't block/kill the web server any more when trying
      to lock the file twice. Now it triggers an user warning.
    * Added close() but made it private because in general it's not needed.
    * Added tablename().
    * Improved the file reader a lot. ID column may be anywhere in the file or
      may be completely missing. MyCSV() adds missing IDs immediatelly.
    * Bugfix: write() improved, IDs may contain double quotes now.
    * add_field() returns false if field name contains any special character.
    * Renamed property _tablename into filename.

2003-10-20
    * Added second parameter to ad_field(..., $afterField).
    * Added first() and last().
    * Bugfix: delete(0) didn't work, it killed the whole table.
    * Bugfix: sort("id ...") works (calls ksort() or krsort() internaly).
    * Bugfix: sort("... desc") didn't work because it was case sensitive.
    * A bunch of small fixes.

2003-07-16
    * Added SORT_NAT (natural sort) to sort().
    * SORT_NULL handles "0" as empty too.

2003-06-02
    * Added some possible field delimiters (":", "\0" and space).

2003-05-27
    * Bugfix: Multiple calls of sort() are working now.
    * Bugfix: join() resets the internal array pointer now.

2003-05-16
    * Added: ksort()/krsort(). Similar to sort("id ...")/sort("id DESC ...").
    * Bugfix: sort() don't mix up the IDs any more (bases on uasort() now).
    * Added sorting type SORT_NULL to sort(). This always moves empty elements
      to the end of the table.
    * Bugfix: Files can be write-protected, then write() triggers a warning.
    * Bugfix: rand() can return more than one random IDs now, e.g. rand(5).

2003-04-03
    * Improved data security a lot. File locking keeps the file locked until
      the script ended. No file sharing problems possible any more.
    * Added ids(), min(), max() and rand(). They all return one or more ID
      numbers. Fetch the corresponding data rows using $table->data($id).
    * Added filesystem functions exists() and is_writeable().
    * Added some missing methods and aliases, e.g. count(), fetch_assoc(), ...
    * No need to use add_field() any more, initial insert() will handle this.
    * Ordered the methods in logical groups: SQL/Array/Filesystem functions.

2003-04-01
    * Bugfix: update() uses array_merge now. This allows particular updates.
    * Added: id_exists() checks if an ID exists (see array_key_exists()).
    * Added: ids() returns all ID numbers (see array_keys()).
    * Added: rand() returns a random ID number (see array_rand()).

2003-03-16
    * File rows will be separated by "\r\n" now. Compatibility to Windows.
    * Added export() which returns the CSV file as a string.
    * Bugfix: insert_id() works now.
    * A lot of small changes. Works with E_ALL on. Faster too.

2003-01-20
    * drop_field() works now.

2002-11-21
    * Variable delimiter added, ',' and ';' are supported now.

Screenshots  
  • MyCSVAdmin_Structure.png
  • MyCSVAdmin_Edit_with_Join.png
  Files folder image Files  
File Role Description
Files folder imageMyCSVAdmin (4 files)
Accessible without login Plain text file ChangeLog.txt Doc. Change log
Accessible without login Plain text file example_filter.php Example How to search in a CSV table
Accessible without login Plain text file example_mysql_to_csv.php Example How to dump a MySQL table into CSV
Accessible without login Plain text file example_products.php Example How to create and sort tables, insert and delete rows
Accessible without login Plain text file MyCSV.class.php Class Main class
Accessible without login HTML file MyCSV_manual.html Doc. Manual and API reference
Accessible without login Plain text file MyCSV_MySQL.class.php Class Extended Class
Accessible without login Plain text file test.php Test PHPUnit 1 Unit Test

  Files folder image Files  /  MyCSVAdmin  
File Role Description
  Accessible without login Plain text file ChangeLog.txt Doc. Administrator Change Log
  Accessible without login Plain text file index.php Appl. Administrator Main Script and Configuration
  Accessible without login Plain text file MyCSVAdmin.class.php Class Administrator Main Class
  Accessible without login HTML file MyCSVAdmin_manual.html Doc. Administrator Manual and API reference

Downloadmycsv-2009-09-02.zip 77KB
Downloadmycsv-2009-09-02.tar.gz 76KB
Install with ComposerInstall with Composer
Needed packages  
Class DownloadWhy it is needed Dependency
TM::Apeform Download .zip .tar.gz Only for the Administrator class and script, to display all the forms. Conditional
 Version Control Unique User Downloads Download Rankings  
 0%
Total:1,935
This week:1
All time:2,035
This week:75Up
User Ratings User Comments (1)
 All time
Utility:91%StarStarStarStarStar
Consistency:75%StarStarStarStar
Documentation:70%StarStarStarStar
Examples:70%StarStarStarStar
Tests:54%StarStarStar
Videos:-
Overall:72%StarStarStarStar
Rank:176
 
does not work at all!
3 days ago (gilles lucato)		50%StarStarStar
 

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.


  Related pages  
Detailed description and examples for the class on my personal website