1.49. SQLite-PHP

1.49.1. Installation ¶

Starting with PHP 5.3.0, the SQLite3 extension is enabled by default. Can be used at compile time –without-sqlite3 Disable the SQLite3 extension.

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

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

1.49.2. PHP interface API ¶

Here are some important PHP programs that can meet your needs for using SQLite databases in PHP programs. If you need more details, please check the PHP official documentation.

Serial number	API & description
1	Public void SQLite3::open (filename, flags, encryption_key)
	Open a SQLite 3 database. If the build includes encryption, then it will try to use the key.
	If the filename filename is assigned to’: memory:’, then SQLite3::open () will create an in-memory database in RAM, which will only last for the duration of the session.
	If the file name filename is the actual device file name, then SQLite3::open () will use this parameter value to try to open the database file. If a file with that name does not exist, a new database file with that name is created.
	The optional flags is used to determine whether to open the SQLite database. By default, it opens when SQLITE3_OPEN_READWRITE \| SQLITE3_OPEN_CREATE is used.
2	Public bool SQLite3::exec (string $query)
	This routine provides a shortcut to execute the SQL command, which is provided by the sql parameter and can consist of multiple SQL commands. This program is used to execute a fruitless query against a given database.
3	Public SQLite3Result SQLite3::query (string $query)
	This routine executes a SQL query and returns a SQLite3Result object if the query returns a result.
4	Public int SQLite3::lastErrorCode (void)
	This routine returns the numeric result code for the last failed SQLite request.
5	Public string SQLite3::lastErrorMsg (void)
	This routine returns an English text description of the most recent failed SQLite request.
6	Public int SQLite3::changes (void)
	This routine returns the number of database rows updated or inserted or deleted by the most recent SQL statement.
7	Public bool SQLite3::close (void)
	This routine closes the database connection that was opened by calling SQLite3::open ().
8	Public string SQLite3::escapeString (string $value)
	This routine returns a string that has been correctly escaped for security reasons in the SQL statement.

1.49.3. Connect 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 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 in the current directory test.db . You can change the path as needed. If the database is created successfully, the message shown below is displayed:

Open database successfully

1.49.4. Create a tabl ¶

The following PHP code snippet will be 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 =<<<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 be executed in the test.db Create in COMPANY Table and displays the message shown below:

Opened database successfully
Table created successfully

1.49.5. INSERT operation ¶

The following PHP program shows how to create the COMPANY Create a record in the 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 =<<<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 be executed in the COMPANY The given record is created in the table and the following two rows are displayed:

Opened database successfully
Records created successfully

1.49.6. SELECT operation ¶

The following PHP program shows how to create the COMPANY Get and display records in the 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 =<<<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 produces the following results:

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

1.49.7. UPDATE operation ¶

The following PHP code shows how to use the UPDATE Statement to update any records, and then from the COMPANY Table to get and display updated records:

<?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 =<<<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 =<<<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 produces the following results:

Opened database successfully
1 Record updated successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

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

1.49.8. DELETE operation ¶

The following PHP code shows how to use the DELETE Statement to delete any records, and then from the COMPANY Get and display the remaining records in the 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 =<<<EOF
      DELETE from COMPANY where ID=2;
EOF;
   $ret = $db->exec($sql);
   if(!$ret){
     echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Record deleted successfully\n";
   }

   $sql =<<<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 produces the following results:

Opened database successfully
1 Record deleted successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

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

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

Operation done successfully