Creating and using a table with SQLite

We have seen how to install and check the software, by creating a database. The next step to use this database requires we create an SQL table. We have to define the content of the records and build the structure with an SQL command.

The code is updated to SQLite 3.

Creating a table

The table that we create will be based on the "posts" table of Wordpress, a simplified version but with same columns. 

$dbname='base';
$mytable ="tablename";

if(!class_exists('SQLite3'))
   die("SQLite 3 NOT supported.");

$base=new SQLite3($dbname, 0666); 

$query = "CREATE TABLE $mytable(
            ID bigint(20) NOT NULL PRIMARY KEY,
            post_author bigint(20) NOT NULL,            
            post_date datetime,
            post_content longtext,
            post_title text,
            guid VARCHAR(255)            
            )";
            
$results = $base->exec($query);

The SQL command CREATE TABLE defines the columns. It is sent to the SQLite manager by the PHP method queryExec() which returns true or false, depending on whether the operation is successful or not.

See the code of the script sqlite-create-table.php.

Deleting a table

The deletion of a table is made by the DROP TABLE command of SQL. 

$query = "DROP TABLE $mytable";
$results = $base->exec($query);

See the code sqlite-delete-table.php.

Adding posts

The table of posts that we just created will be filled, like in Wordpress, with the posts we write, each post corresponding to a row of the table.

The SQL command: INSERT INTO allows to store the data. 

$number = 1;
$title="My first post";
$content="The content of my post...";
$date = strftime( "%b %d %Y %H:%M", time());
$author=1;
$url = "https://www.scriptol.com/sql/sqlite-tutorial.php";

$query = "INSERT INTO $mytable(ID, post_title, post_content, post_author, post_date, guid) 
                VALUES ('$number', '$title', '$content', '$author', '$date', '$url')";
$results = $base->exec($query);

For the purposes of the tutorial, we place the contents of the post directly into variables. In practice these variables will be assigned from an t online or local ext editor, as shown in the CMS tutorial.

The author is represented by the number 1 because Wordpress does not put the names in the posts table but in a separate table instead.

The guid column contains the URL of the post that also serves as an ID unique.

The INSERT command jas for first parameter the table name and in parentheses the list of columns to fill, then the parameter VALUE provides a list of values corresponding to the columns in the same order.
Thus, post_title, which contains the titles, will has for value $title, the variable that was assigned the title of the post.

The same queryExec method is used to send the request.

Source code of the script sqlite-write.php.

Reading a post

You can access the contents of the database with the SELECT command. 

$query = "SELECT post_title, post_content, post_author, post_date, guid FROM $mytable";
$results = $base->query($query);
$row = $results->fetchArray();

if(count($row)>0)
{
   $title = $row['post_title'];
   $content = $row['post_content']; 
   $user = $row['post_author'];
   $date = $row['post_date'];
   $url = $row['guid'];
}

To the SELECT command is given the list of columns that you want get the content and lines will be assigned to $results array. Indeed, the arrayQuery PHP method returns an array of arrays, each representing a line of the table.
In practice we will use instead other commands that we will see later to limit the resources usage.
Data are retrieved in the associative array $arr, where the column names are keys and their content the values.

Source code of the script sqlite-read.php.

Download