Creating and Using a SQLite Table in PHP

We saw how to install SQL and create a database. The first necessary step to use this database is to create a table that defines which data fields will make up our database.

This code is updated for SQLite 3.

Create Table

The table we will create will be based on the Wordpress "posts" table, in a simplified version, but with the 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);

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

See create-table.php script code.

Delete Table

The table is erased using the SQL DROP TABLE command.

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

See delete-table.php code.

Add tickets

The table of tickets we have just created will be filled, as for Wordpress, along with the bills that will be written, each ticket corresponds to one line of the table.

The SQL: INSERT INTO command allows you to insert ticket data.

$number = 1;
$title="Mon dernier billet";
$content="Le texte de mon article...";
$date = strftime( "%b %d %Y %H:%M", time());
$author=1;
$url = "https://www.iqlevsha.ru/sql/tutoriel-sqlite.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 needs of the textbook, we put the contents of the ticket directly into the variables. In practice, these variables will be assigned from an online or local text editor, as shown in the CMS tutorial.

The author is represented by the number 1, because Wordpress places the names not in the message table, but in a separate table.

The guid column contains the URL of the record, which also serves as a unique identifier.

The INSERT command has the table name as the first parameter and a list of matching columns in parentheses, and then the VALUE parameter provides a list of matching values in the same order.
Thus, the post_title that contains the securities will have a value of $ title, the variable to which the banknote name has been assigned.

The same queryExec method is used to send the request.

sql lite-write.php script source code.

Read the post

The contents of the database are accessed using 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'];
}

The SELECT command provides a list of columns whose contents are required, and the rows will be assigned to the $ results table. The PHP arrayQuery method returns an array of arrays, each representing a row in the table.
In practice, other orders that can be seen later will be used to limit resource consumption.
The row is retrieved from the $ arr associative table with column names for keys and contents for values.

sql lite-read.php script source code.

Loading