SQlite3 database with PHP – Invoices management

invoices.css

body, tbody {
  font-family: Georgia,Palatino,Palatino Linotype,FreeSerif,serif;
  font-size: 16px;
}
html, body, div, p, h1, h2, h3, h4, h5, img {
  border: medium none;
  margin:0;
  padding:0;
}
/*
body, table {
  color: #222222;
  font: 400 100.1%/1 Arial,Helvetica,FreeSans,sans-serif;
}
*/
table {
  border-collapse: collapse;
  line-height: 150%;
  margin: 10px;
}
td {
  padding: 0px 5px;
}


.error {
  border: 3px solid #F00;
  background-color: #000;
  color: #FFF;
  font-face: bold;
  padding: 5px;
  margin: 25px;
}

invoices.php

<? /*

This file lists invoices from an SQlite3 database.

Requirement: PDO with SQlite
    su
    aptitude install php5 php5-sqlite
    #pecl install pdo_sqlite ### ← This is not needed anymore!!! ( http://www.egroupware.org/forum#nabble-td2365452|a2395982 )
    echo -e "\nextension=pdo.so\n" >> ../conf/php.ini

Requirement: Pear:
    ### geht nicht, weil HTML_Page2 noch beta:
    #pear install HTML_Page2 ## stattdessen:
    pear install channel://pear.php.net/HTML_Page2-0.6.0beta
*/

$invoice_db_file = "invoices.sqlite3";

// Use the Pear class HTML_Page2:
require_once('/usr/share/php/'.'HTML/Page2.php');
$page = new HTML_Page2();
$page->setTitle(tr("invoices list – by Philipp Klaus"));
$page->addStyleSheet('invoices.css');

try {
  //open the database
  $db = new PDO('sqlite:'.$invoice_db_file);
  $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  // first let the engine check table, and create it eventually
  $db->exec('CREATE TABLE IF NOT EXISTS issued_invoices(id INTEGER PRIMARY KEY, number TEXT, date TEXT)');

  //insert some data...
  $db->prepare('INSERT INTO issued_invoices (id, number, date) VALUES (?, ?, ?);')->execute( array( Null, "0101", date("c") ) );

  // print the current count of issue entries:
  $count = $db->query('SELECT COUNT(*) FROM issued_invoices;')->fetch();
  $count = $count['COUNT(*)'];
  $page->addBodyContent( '<p>'.sprintf( tr("There are %d invoices stored in the database."), $count).'</p>' );

  //now output the data to a simple html table...
  $t  = "<table border='1'>";
  $t .= "<tr><td>".tr("invoice id")."</td><td>".tr("invoice number")."</td><td>".tr("invoice date")."</td></tr>";
  $result = $db->query('SELECT * FROM issued_invoices;');
  if (!$result) throw new Exception( 'Unknown error while trying to query the database.' );
  foreach($result as $row)
  {
    $t .= "<tr><td>".$row['id']."</td>";
    $t .= "<td>".$row['number']."</td>";
    $t .= "<td>".$row['date']."</td></tr>";
  }
  $t .= "</table>";

  $page->addBodyContent( $t );

  // close the database connection
  $db = NULL;
} catch(PDOException $e) {
  $e = new pdoDbException($e);
  error(tr("Database Exception").sprintf(" : %s (error code %s)", $e->getMessage(), $e->getCode()), $page);
}
catch(Exception $e) {
  error(tr("There was an error:")." : ".$e->getMessage(), $page);
}


function error($message, $page) {
    $page->addBodyContent( '<div class="error">'.$message.'</div>' );
}

function tr($text) {
    if (!defined("LANGUAGE"))
        define("LANGUAGE", "de");
    $mesages = array();
    $messages['de'] = array(
        "Database Exception" => "Datenbank Fehler",
        "There are %d invoices stored in the database." => "In der Datenbank sind %d Rechnungen gespeichert.",
        "invoice id" => "Rechnungs ID",
        "invoice number" => "Rechnungsnummer",
        "invoice date" => "Rechnungsdatum",
        "invoices list – by Philipp Klaus" => "Rechnungsliste – von Philipp Klaus",
    );
    if(in_array($text,array_keys($messages[LANGUAGE])))
        return $messages[LANGUAGE][$text];
    else
        return $text;
}
$page->display();

// http://de2.php.net/manual/en/class.pdoexception.php#97908
class pdoDbException extends PDOException {
    public function __construct(PDOException $e) {
        if(strstr($e->getMessage(), 'SQLSTATE[')) {
            preg_match("/SQLSTATE\\[(.*)\\]\\: (.*)/", $e->getMessage(), $matches);
            $this->code = $matches[1];
            $this->message = $matches[2];
        }
    }
}

?>