SQLite tutorial: Getting started

The use of SQLite starts by installing the software and creating a database. The installation is require on his own server or on a local desktop. To use it on a shared hosting, you can skip the installation and go directly to the verification section.

Installing SQLite is simple and to check this first step has been achieved correctly, we have just to create a new database like in the script below.

Why use SQLite

You can choose to use either SQLite or MySQL on a website.

Advantages of SQLite:

• It does not require a MySQL database on the server, you use your own storage, a SQLite file.
• It can be saved easily simply by saving the file of the base!
• It can be used locally with programs written in C or PHP or other languages.
• It may be used for running Web applications offline in HTML 5.

In return:

• Access to a SQLite database may slow down if the size becomes very important.
• Multiple users can not simultaneously modify a database.
• The SQLite extension is not activated on all shared hosts.

More infos: Appropriate uses for SQLite.

Installing SQLite for PHP

The SQLite library is not included by default, it is necessary to modify the PHP.INI file in the directory of PHP and activate two lines, by removing the semicolon in prefix:

extension=php_pdo_sqlite.dll
extension=php_sqlite3.dll

You can run SQLite in Using PHP and SQL locally with XAMPP .

To verify that SQLite works, put the script sqlite-check.php (it is in the archive) in a subfolder of www in Wamp, and run it in localhost.

Or upload it on the server and launch the page, for example: http://www.scriptol.code/sqlite-check.php

The script for SQLite 3 (since PHP 5.4)

<?php
$dbname='base';
if(!class_exists('SQLite3'))
  die("SQLite 3 NOT supported.");

$base=new SQLite3($dbname, 0666);
echo "SQLite 3 supported."; 
?>

The script for SQLite 2:

<?php
$dbname='base';
$base=new SQLiteDatabase($dbname, 0666, $err);
if ($err)
  die("SQLite NOT supported.");

echo "SQLite supported.";
?>

This code creates the database named base. If the extension is not available, the variable $base will be false.
If it works, a file named base appears in the directory of the script.

Download

• The complete source code of the scripts in a ZIP archive and the source code for the SQLite 3 version.