How to create SQLite database

These steps can only be performed in CDA Labs or by FTP
In this guide, HOME will refer to your home folder on the server, the one containing public_html

• Create a DB folder inside your HOME
• Create a new empty file HOME\DB\mydb.sqlite
  
  
• Extract phpLiteAdmin (Download) into HOME\public_html\
  You should now have a file HOME\public_html\phpliteadmin.php
• Open phpliteadmin.php with a text editor, change the following:
  ...
  //password to gain access (change this to something more secure than 'admin')
  $password = "<your new password to access phpLiteAdmin goes here>";
  
  //directory relative to this file to search for SQLite databases (if false, manually list databases below)
  $directory = "../db/";
  ...

You can now use phpLiteAdmin to setup the structure of your database.

When writing code, use relative paths to the database. For example, if your code is in home/public_html/testapp/test.php, you would connect to the database with the command
$db= new SQLite3("../../db/mydb.sqlite");

PHP Example (home/public_html/test.php):
<?php
$db= new SQLite3("../db/mydb.sqlite");//asuming you created a folder "db" in the root of your home drive and your database files is called "test.db".
//$db->exec('CREATE TABLE table1(id INTEGER PRIMARY KEY NOT NULL, value TEXT)');
//Uncomment the line above to create the  Table, do it only once.
$string_to_insert=$db->escapeString(date("r" ,time()));//Important to escape any strings before inserting them into a query since they can contain an illegal character 
//or can be used for "sting insertion" hacks.
$db->exec("INSERT INTO table1 (value) VALUES ('$string_to_insert')");
$sql_select='SELECT * FROM table1 ORDER BY ID DESC';
$result=$db->query($sql_select);
echo "<table border='1'>";
echo "<tr>";
$numColumns=$result->numColumns();
for ($i = 0; $i < $numColumns; $i++)
{
    $colname=$result->columnName($i);
    echo "<th>$colname</th>";
}
echo "</tr>";
while($row = $result->fetchArray(SQLITE3_NUM))
{
    echo "<tr>";
    for ($i = 0; $i < $numColumns; $i++)
    {
        $value=$row[$i];
        echo "<th>$value</th>";
    }
    echo "</tr>";
}
echo "</table>";
$sql_clean="DELETE FROM table1 WHERE ID<(SELECT MAX(ID) FROM table1)-20";//Keeps only the last 20 entries in the table.
$db->exec($sql_clean);
?>