<?php echo'PHP Version - '.phpversion().'
';
#****************************************************
#
# Basic guide to using PDO with SQLite3 on PHP 5
# (in this case using an Abyss X1 Webserver running PHP 5.4.2)
#
#****************************************************

// create database - if database exists then will just open it
$db = new PDO("sqlite:doggies.db"); // .sqlite, .sdb equally OK // this database will be created in folder that hosts this file
echo 'New DB is - '.$db;

// remove the dogs table if it exists
$db->query('DROP TABLE dogs');

// create table
$db->query('CREATE TABLE dogs(dogname, number)');

// populate table
$db->query('INSERT INTO dogs VALUES ("Bassethound", 4)');
$db->query('INSERT INTO dogs VALUES ("Beagle", 6)');
$db->query('INSERT INTO dogs VALUES ("Bulldog", 8)');

// get records from table
$listo = $db->query("SELECT * FROM projects");

echo 'Doggie list - \n';

foreach ($listo as $row) {
echo '$row[dogname] $row[number]
';
}

// eg: typical usage that works for me

// I try to avoid or/die statements
// Transactions appear to be quite a good idea (particularly if complete data committal is critical) wherein multiple queries/inserts are only committed transactionally using commit()
// - apparently beginTransaction() should disable auto-commit until commit() has been done.
//


//set Source path
$dbPath="datafolder/";
$dbName="junglejim".".db"; // typical extns - .db, .sqlite, .sbd
$dbSource=$dbPath.$dbName; // Source path
$dsn="sqlite:".$dbSource; //the Data Source connector
// do database create
if (!file_exists($dsn)) {
try {

$db = new PDO($dsn); // set up connector // this database will be created in sub-folder of the folder that hosts this file ie: datafolder/junglejim.db
$db->query("CREATE TABLE maillist(id INTEGER PRIMARY KEY, name VARCHAR(20), mail VARCHAR(30))");
$db->query("CREATE TABLE books(id INTEGER PRIMARY KEY, title varchar(30), author VARCHAR(35))");
echo 'Database created successfully';
$db = null; # close DB connection

} catch (PDOException $e) {
echo 'Database Connection failed: ' . $e->getMessage();
}
}

$db= new PDO($dsn);

// test attributes
if ($db->getAttribute(PDO::ATTR_DRIVER_NAME) == 'sqlite') {
echo "Running on SQLite";
}

// results may vary depending on server/php type
//$findo = $db->getAttribute(PDO::ATTR_AUTOCOMMIT); // not supported
//$findo = $db->getAttribute(PDO::ATTR_PREFETCH); // not supported
//$findo = $db->getAttribute(PDO::ATTR_TIMEOUT); // not supported //$findo = $db->getAttribute(PDO::ATTR_CONNECTION_STATUS); // not supported
//$findo = $db->getAttribute(PDO::ATTR_DRIVER_NAME); // OK eg: sqlite
//$findo = $db->getAttribute(PDO::ATTR_CASE); // OK eg: 0
//$findo = $db->getAttribute(PDO::ATTR_CURSOR); // not supported
//$findo = $db->getAttribute(PDO::ATTR_CURSOR_NAME); // not supported
//$findo = $db->getAttribute(PDO::ATTR_CLIENT_VERSION); // OK eg: 3.7.7.1
//$findo = $db->getAttribute(PDO::ATTR_SERVER_VERSION); // OK eg: 3.7.7.1
//$findo = $db->getAttribute(PDO::ATTR_SERVER_INFO); // not supported
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$findo = $db->getAttribute(PDO::ATTR_ERRMODE); // OK eg: 2 for ERRMODE_EXCEPTION, 0 for ERRMODE_SILENT (default), 1 for ERRMODE_WARNING
//$findo = $db->getAttribute(PDO::ERRMODE_EXCEPTION); // not supported
echo "Findo - ".$findo;

echo "
Attribute Listing -
";
$attributes = array(
"DRIVER_NAME", "CASE", "CLIENT_VERSION", "SERVER_VERSION", "ORACLE_NULLS", "ERRMODE"
);
foreach ($attributes as $val) {
echo "PDO::ATTR_$val: ";
echo $db->getAttribute(constant("PDO::ATTR_$val"))."
";
}

echo "Available PDO Drivers -
";
print_r(PDO::getAvailableDrivers());

// add some data to Tables
$db->beginTransaction();// optional
$db->query('INSERT INTO maillist VALUES (1, "Biggles", "pilot@fly.com")');
$db->query('INSERT INTO maillist VALUES (2, "Einstein", clerk@smart.com)');
$db->query('INSERT INTO maillist VALUES (3, "Santa", redman@northpole.org)');
$db->query('INSERT INTO books VALUES (25, "War and Peace", "Tolstoy")');
$db->query('INSERT INTO books VALUES (32, "Treasure Island", "Stevenson")');
$db->commit();

// find number of rows

$tempo = $db->query("SELECT COUNT(*) FROM maillist");
//$rownum = $tempo->fetch(PDO::FETCH_NUM); // works but returns whole row
$rownum = $tempo->fetchColumn(); // works - returns first column of next row
echo 'Rows - '.$rownum[0]."
";


// get records from table
$listo = $db->query("SELECT * FROM maillist");
echo 'Mail list -
';

foreach ($listo as $row) {
echo '$row[id] $row[name] $row[mail]
';
}







?>