SQLite - PHP

installation

Since PHP 5.3.0 onwards SQLite3 extension is enabled by default. You can disable SQLite3 extended use--without-sqlite3 at compile time.

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

For detailed installation instructions, see our PHP tutorial recommendation and its official website.

PHP Interface API

The following are important PHP program to meet your needs using SQLite database in PHP program. If you need more details, please see the official PHP documentation.

序号	API & 描述
1	public void SQLite3::open ( filename, flags, encryption_key ) 打开一个 SQLite 3 数据库。如果构建包括加密，那么它将尝试使用的密钥。如果文件名filename赋值为':memory:'，那么 SQLite3::open() 将会在 RAM 中创建一个内存数据库，这只会在 session 的有效时间内持续。如果文件名 filename 为实际的设备文件名称，那么 SQLite3::open() 将使用这个参数值尝试打开数据库文件。如果该名称的文件不存在，那么将创建一个新的命名为该名称的数据库文件。可选的 flags 用于判断是否打开 SQLite 数据库。默认情况下，当使用 SQLITE3_OPEN_READWRITE \| SQLITE3_OPEN_CREATE 时打开。
2	public bool SQLite3::exec ( string $query ) 该例程提供了一个执行 SQL 命令的快捷方式，SQL 命令由 sql 参数提供，可以由多个 SQL 命令组成。该程序用于对给定的数据库执行一个无结果的查询。
3	public SQLite3Result SQLite3::query ( string $query ) 该例程执行一个 SQL 查询，如果查询到返回结果则返回一个SQLite3Result对象。
4	public int SQLite3::lastErrorCode ( void ) 该例程返回最近一次失败的 SQLite 请求的数值结果代码。
5	public string SQLite3::lastErrorMsg ( void ) 该例程返回最近一次失败的 SQLite 请求的英语文本描述。
6	public int SQLite3::changes ( void ) 该例程返回最近一次的 SQL 语句更新或插入或删除的数据库行数。
7	public bool SQLite3::close ( void ) 该例程关闭之前调用 SQLite3::open() 打开的数据库连接。
8	public string SQLite3::escapeString ( string $value ) 该例程返回一个字符串，在 SQL 语句中，出于安全考虑，该字符串已被正确地转义。

Connect to the database

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

<? 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, create our databasetest.db in the current directory.You can change the path as needed. If the database is successfully created, it will display the message shown below:

Open database successfully

Create a table

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

<? 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 procedure, it creates COMPANY table intest.db and displays the message shown below:

Opened database successfully
Table created successfully

INSERT operation

The following PHP program shows how to create a record 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 = <<< 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 ();
?>

The above program is executed, it will be created in the COMPANY table for a given record, and displays the following two lines:

Opened database successfully
Records created successfully

SELECT operations

The following PHP program shows how to get from the COMPANY table created earlier and displays the record:

<? 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 will produce 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

UPDATE operation

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

<? 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 will produce 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

DELETE operation

The following PHP code shows how to use the DELETE statement deletes any records, and then taken from the COMPANY table and displays the remaining recording:

<? 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 will produce 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

Previous: SQLite - Java

Next: SQLite - Perl

SQlite Tutorial

SQLite Advanced Tutorial

SQLite interface

SQLite - PHP

installation

PHP Interface API

Connect to the database

Create a table

INSERT operation

SELECT operations

UPDATE operation

DELETE operation