PHP/MySQL Database/sqlite database

Access sqlite database

 
$dbconn = sqlite_open("phpdb");
    if ($dbconn) {
            sqlite_query($dbconn, "CREATE TABLE animal(Name VARCHAR(255), MaxAge INT);");
            sqlite_query($dbconn, "INSERT INTO animal VALUES ("A", 15)");
            $result = sqlite_query($dbconn, "SELECT Name FROM animal");
            var_dump(sqlite_fetch_array($result, SQLITE_ASSOC));
    } else {
            print "Connection to database failed!\n";
    }

Copying Resultsets into an Array Manually

 
<?php
     $rows = array();
     while($row = sqlite_fetch_array($result, $res_type, $decode)) {
        $rows[] = $row;
     }
?>

Creating an SQLite database

 
<?php
$db = new PDO("sqlite:/usr/local/zodiac");
$db->beginTransaction();
$q = $db->query("SELECT name FROM sqlite_master WHERE type = "table" AND name = "zodiac"");
if ($q->fetch() === false) {
    $db->exec(<<<_SQL_
CREATE TABLE zodiac (
  id INT UNSIGNED NOT NULL,
  sign CHAR(11),
  symbol CHAR(13),
  planet CHAR(7),
  element CHAR(5),
  start_month TINYINT,
  start_day TINYINT,
  end_month TINYINT,
  end_day TINYINT,
  PRIMARY KEY(id)
)
_SQL_
);
    $sql=<<<_SQL_
INSERT INTO zodiac VALUES (1,"Aries","Ram","Mars","fire",3,21,4,19);
INSERT INTO zodiac VALUES (2,"Taurus","Bull","Venus","earth",4,20,5,20);
INSERT INTO zodiac VALUES (3,"Gemini","Twins","Mercury","air",5,21,6,21);
_SQL_;
    foreach (explode("\n",trim($sql)) as $q) {
        $db->exec(trim($q));
    }
    $db->commit();
} else {
    $db->rollback();
}
?>

SQLite Error Constants

 
SQLITE_OK              No error occurred.
 
SQLITE_ERROR           SQLite error (or database not found).
 
SQLITE_INTERNAL        An internal SQLite error.
 
SQLITE_PERM            Access permission denied.
 
SQLITE_ABORT           Callback routine aborted.
 
SQLITE_BUSY            The database file is currently locked.
 
SQLITE_LOCKED          A table within the database is locked.
 
SQLITE_NOMEM           SQLite memory allocation error.
 
SQLITE_READONLY        An attempt to write to a read-only database.
 
SQLITE_INTERRUPT       Interrupted operation.
 
SQLITE_IOERR           A file I/O error has occurred.
 
SQLITE_CORRUPT         The specified database is corrupted.
 
SQLITE_FULL            Database is full.
 
SQLITE_CANTOPEN        Could not open database file.
 
SQLITE_PROTOCOL        Database lock protocol error.
 
SQLITE_SCHEMA          The database schema changed.
 
SQLITE_TOOBIG          Too much data for a single row.
 
SQLITE_CONSTRAINT      Abort due to constraint violation.
 
SQLITE_MISMATCH        Data type mismatch.
 
SQLITE_AUTH            Authorization denied.
 
SQLITE_ROW             sqlite_step() has another row ready.
 
SQLITE_DONE            sqlite_step() has finished executing.