PHP A_Db_Sqlite3 Examples

Example #1

File: example_tabledatagateway_sqlite.php Project: TheProjecter/skeleton

<?php

include 'config.php';
#include 'A/Db/Sqlite.php';
#include 'A/Db/Tabledatagateway.php';
class Projects extends A_Db_Tabledatagateway
{
}
$db = new A_Db_Sqlite3($ConfigArray['DBDSN_SQLITE']);
$db->connect();
if ($db->isError()) {
    die('ERROR: ' . $db->getMessage());
}
$project = new Projects($db);
$rows = $project->find(2);
dump($project->sql);
dump($rows);
$rows = $project->find('clients_id=', 1);
dump($project->sql);
dump($rows);

Example #2

File: example_db_sqlite3.php Project: TheProjecter/skeleton

<?php

include 'config.php';
$filename = 'sqlite3.db';
$db = new A_Db_Sqlite3(array('filename' => $filename));
$db->connect();
if (!$db->isError()) {
    $test_rows = array(0 => array(':id' => 10, ':name' => 'Foo'), 1 => array(':id' => 20, ':name' => 'Bar'));
    $sql = "CREATE TABLE test1 (id INT, name VARCHAR(100))";
    $db->query($sql);
    foreach ($test_rows as $row) {
        $result = $db->query("INSERT INTO test1 (id, name) VALUES (:id, ':name')", $row);
    }
    $sql = "SELECT * FROM test1";
    $result = $db->query($sql);
    if (!$db->isError()) {
        $row = $result->fetchAll();
        dump($row, 'ROW: ');
    } else {
        echo 'connect ERROR: ' . $db->getErrorMsg();
    }
} else {
    echo 'connect ERROR: ' . $db->getErrorMsg();
}
$db->close();
unlink($filename);
dump();

Example #3

File: create_tables.php Project: TheProjecter/skeleton

<?php

include 'config.php';
include 'A/Db/Sqlite3.php';
include 'A/Db/Tabledatagateway.php';
class Projects extends A_Db_Tabledatagateway
{
}
$db = new A_Db_Sqlite3($ConfigArray['DBDSN']);
$db->connect();
$sql = "CREATE TABLE clients (\r\n  id INTEGER PRIMARY KEY,\r\n  name TEXT\r\n);";
$db->query($sql);
$db->query("INSERT INTO clients VALUES (1, 'Project One')");
$db->query("INSERT INTO clients VALUES (2, 'Project Two')");
$db->query("INSERT INTO clients VALUES (3, 'Project Three')");
$sql = "CREATE TABLE projects (\r\n  id INTEGER PRIMARY KEY,\r\n  clients_id INTEGER,\r\n  name TEXT\r\n);";
$db->query($sql);
$db->query("INSERT INTO projects VALUES (1, 1, 'Project One')");
$db->query("INSERT INTO projects VALUES (2, 1, 'Project Two')");
$db->query("INSERT INTO projects VALUES (3, 2, 'Project Three')");
$db->query("INSERT INTO projects VALUES (4, 2, 'Project Four')");
$db->query("INSERT INTO projects VALUES (5, 2, 'Project Five')");
$db->query("INSERT INTO projects VALUES (6, 3, 'Project Six')");
$result = $db->query('SELECT * FROM clients');
while ($row = $result->fetchRow()) {
    var_dump($row);
}
$result = $db->query('SELECT * FROM projects');
while ($row = $result->fetchRow()) {
    var_dump($row);
}

Example #4

File: Sqlite3Test.php Project: TheProjecter/skeleton

 function testDb_SqliteInsertUpdateSelect()
 {
     $Db_Sqlite3 = new A_Db_Sqlite3(array('filename' => $this->filename));
     $Db_Sqlite3->connect();
     $test_rows = array(0 => array(':id' => 10, ':name' => 'Foo'), 1 => array(':id' => 20, ':name' => 'Bar'));
     $sql = "CREATE TABLE test1 (id INT, name VARCHAR(100))";
     $Db_Sqlite3->query($sql);
     $this->assertFalse($Db_Sqlite3->isError());
     $this->assertTrue($Db_Sqlite3->getErrorMsg() == '');
     foreach ($test_rows as $row) {
         $result = $Db_Sqlite3->query("INSERT INTO test1 (id, name) VALUES (:id, ':name')", $row);
         $this->assertFalse($Db_Sqlite3->isError());
         $this->assertTrue($Db_Sqlite3->getErrorMsg() == '');
     }
     $result = $Db_Sqlite3->query("SELECT * FROM test1");
     #dump($result, 'RESULT: ', 1);
     $this->assertFalse($Db_Sqlite3->isError());
     $this->assertTrue($Db_Sqlite3->getErrorMsg() == '');
     $rows = $result->fetchAll();
     #dump($rows, 'ROWS: ', 1);
     foreach ($rows as $n => $row) {
         $this->assertTrue($test_rows[$n]['id'] == $row['id']);
         $this->assertTrue($test_rows[$n]['name'] == $row['name']);
     }
     #dump($Db_Sqlite3->getSql(), 'SQL HISTORY: ', 1);
     $Db_Sqlite3->close();
 }