❮ Sqlite Syntax Sqlite Distinct Keyword ❯

SQLite - `PHP`

Installation

The SQLite3 extension is enabled by default since PHP 5.3.0. You can disable the SQLite3 extension at compile time using --without-sqlite3.

Windows users must enable php_sqlite3.dll to use this extension. Since PHP 5.3.0, this DLL is included in the Windows distribution of PHP.

For detailed installation instructions, it is recommended to check our PHP tutorial and its official website.

PHP Interface API

The following are important PHP functions that will fulfill your requirements when using SQLite databases in your PHP programs. For more details, please refer to the PHP official documentation.

No.	API & Description
1	public void SQLite3::open ( filename, flags, encryption_key ) <br> Opens an SQLite 3 database. If the build includes encryption, it will attempt to use the key. If the filename is set to ':memory:', SQLite3::open() will create an in-memory database that only lasts for the duration of the session. If the filename is an actual device file name, SQLite3::open() will attempt to open the database file using this parameter. If a file with that name does not exist, a new database file with that name will be created. Optional flags are used to determine how to open the SQLite database. By default, it opens with SQLITE3_OPEN_READWRITE	SQLITE3_OPEN_CREATE.
2	public bool SQLite3::exec ( string $query ) <br> This routine provides a quick way to execute SQL commands provided by the sql parameter, which can consist of multiple SQL commands. This function is used to execute a result-less query on the given database.
3	public SQLite3Result SQLite3::query ( string $query ) <br> This routine executes an SQL query and returns an SQLite3Result object if the query returns results.
4	public int SQLite3::lastErrorCode ( void ) <br> This routine returns the numeric result code of the most recent failed SQLite request.
5	public string SQLite3::lastErrorMsg ( void ) <br> This routine returns the English text description of the most recent failed SQLite request.
6	public int SQLite3::changes ( void ) <br> This routine returns the number of database rows that were updated or inserted or deleted by the most recent SQL statement.
7	public bool SQLite3::close ( void ) <br> This routine closes the database connection previously opened by calling SQLite3::open().
8	public string SQLite3::escapeString ( string $value ) <br> This routine returns a string that has been properly escaped for safe use in an SQL statement.

Connecting to the Database

The following PHP code shows how to connect to an existing database. If the database does not exist, it will be created, and finally, a database object will be returned.

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('test.db');
      }
   }
   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfully\n";
   }
?>

Now, let's run the above program to create our database test.db in the current directory. You can change the path as needed. If the database is created successfully, the following message will be displayed:

Opened database successfully

Creating a Table

The following PHP code snippet is used to create a table in the previously created database:

<?php
   class MyDB extends SQLite3
   {
      function __construct()
      {
         $this->open('test.db');
      }
   }

$db = new MyDB();
if (!$db) {
   echo $db->lastErrorMsg();
} else {
   echo "Opened database successfully\n";
}

$sql = <<&lt;EOF
   CREATE TABLE COMPANY
   (ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL);
EOF;

$ret = $db->exec($sql);
if (!$ret) {
   echo $db->lastErrorMsg();
} else {
   echo "Table created successfully\n";
}
$db->close();
?>

When the above program is executed, it will create the COMPANY table in the test.db and display the messages as shown below:

Opened database successfully
Table created successfully

INSERT Operation

The following PHP program shows how to create records in the COMPANY table created above:

<?php
class MyDB extends SQLite3
{
   function __construct()
   {
      $this->open('test.db');
   }
}
$db = new MyDB();
if (!$db) {
   echo $db->lastErrorMsg();
} else {
   echo "Opened database successfully\n";
}

$sql = <<&lt;EOF
   INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (1, 'Paul', 32, 'California', 20000.00 );

   INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

   INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

   INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
   VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
EOF;

$ret = $db->exec($sql);
if (!$ret) {
   echo $db->lastErrorMsg();
} else {
   echo "Records created successfully\n";
}
$db->close();
?>

When the above program is executed, it will create the given records in the COMPANY table and display the following two lines:

Opened database successfully
Records created successfully

SELECT Operation

The following PHP program shows how to get and display records from the COMPANY table created above:

<?php
class MyDB extends SQLite3
{
   function __construct()
   {
      $this->open('test.db');
   }
}
$db = new MyDB();
if (!$db) {
   echo $db->lastErrorMsg();
} else {
   echo "Opened database successfully\n";
}

$sql = <<&lt;EOF
   SELECT * from COMPANY;
EOF;

$ret = $db->query($sql);
while ($row = $ret->fetchArray(SQLITE3_ASSOC)) {

echo "ID = ". $row['ID'] . "\n";
echo "NAME = ". $row['NAME'] ."\n";
echo "ADDRESS = ". $row['ADDRESS'] ."\n";
echo "SALARY =  ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>

When the above program is executed, it will produce the following result:

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

UPDATE Operation

The following PHP code shows how to use the UPDATE statement to update any record, and then fetch and display the updated records from the COMPANY table:

<?php
class MyDB extends SQLite3
{
   function __construct()
   {
      $this->open('test.db');
   }
}
$db = new MyDB();
if(!$db){
   echo $db->lastErrorMsg();
} else {
   echo "Opened database successfully\n";
}
$sql =<<&lt;EOF
   UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
$ret = $db->exec($sql);
if(!$ret){
   echo $db->lastErrorMsg();
} else {
   echo $db->changes(), " Record updated successfully\n";
}

$sql =<<&lt;EOF
   SELECT * from COMPANY;
EOF;
$ret = $db->query($sql);
while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
   echo "ID = ". $row['ID'] . "\n";
   echo "NAME = ". $row['NAME'] ."\n";
   echo "ADDRESS = ". $row['ADDRESS'] ."\n";
   echo "SALARY =  ".$row['SALARY'] ."\n\n";
}
echo "Operation done successfully\n";
$db->close();
?>