Sqlite 简明教程

SQLite - PHP

在本章中，你将学习如何在 PHP 程序中使用 SQLite。

In this chapter, you will learn how to use SQLite in PHP programs.

Installation

截至 PHP 5.3.0，SQLite3 扩展默认处于启用状态。有可能在编译时使用 --without-sqlite3 来禁用它。

SQLite3 extension is enabled by default as of PHP 5.3.0. It’s possible to disable it by using --without-sqlite3 at compile time.

Windows 用户必须启用 php_sqlite3.dll 才能使用此扩展。此 DLL 随 Windows 的 PHP 发行版附带，从 PHP 5.3.0 起。

Windows users must enable php_sqlite3.dll in order to use this extension. This DLL is included with Windows distributions of PHP as of PHP 5.3.0.

有关详细的安装说明，请查看 PHP 教程及其官方网站。

For detailed installation instructions, kindly check our PHP tutorial and its official website.

PHP Interface APIs

以下是一些重要的 PHP 例程，它们足以满足您通过 PHP 程序使用 SQLite 数据库的需要。如果您正在寻找一个更为精巧的应用程序，则可以查阅 PHP 官方文档。

Following are important PHP routines which can suffice your requirement to work with SQLite database from your PHP program. If you are looking for a more sophisticated application, then you can look into PHP official documentation.

Sr.No.	API & Description
1	public void SQLite3::open ( filename, flags, encryption_key ) Opens SQLite 3 Database. If the build includes encryption, then it will attempt to use the key. If the filename is given as ':memory:', SQLite3::open() will create an in-memory database in RAM that lasts only for the duration of the session. If the filename is actual device file name, SQLite3::open() attempts to open the database file by using its value. If no file by that name exists, then a new database file by that name gets created. Optional flags used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE
SQLITE3_OPEN_CREATE.	2
public bool SQLite3::exec ( string $query ) This routine provides a quick, easy way to execute SQL commands provided by sql argument, which can consist of more than one SQL command. This routine is used to execute a result-less query against a given database.	3
public SQLite3Result SQLite3::query ( string $query ) This routine executes an SQL query, returning an SQLite3Result object if the query returns results.	4
public int SQLite3::lastErrorCode ( void ) This routine returns the numeric result code of the most recent failed SQLite request.	5
public string SQLite3::lastErrorMsg ( void ) This routine returns English text describing the most recent failed SQLite request.	6
public int SQLite3::changes ( void ) This routine returns the number of database rows that were updated, inserted, or deleted by the most recent SQL statement.	7
public bool SQLite3::close ( void ) This routine closes a database connection previously opened by a call to SQLite3::open().	8

Connect to Database

以下 PHP 代码展示了如何连接到现有数据库。如果数据库不存在，则会创建它，最后将返回一个数据库对象。

Following PHP code shows how to connect to an existing database. If database does not exist, then 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";
   }
?>

现在，让我们运行上面的程序，在当前目录中创建我们的数据库 test.db 。您可以根据您的需要更改您的路径。如果数据库创建成功，则将显示以下消息 -

Now, let’s run the above program to create our database test.db in the current directory. You can change your path as per your requirement. If the database is successfully created, then it will display the following message −

Open database successfully

Create a Table

以下 PHP 程序将用于在之前创建的数据库中创建一张表。

Following PHP program 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();
?>

执行上述程序后，它将在您的 test.db 中创建 COMPANY 表，并且它将显示以下消息：

When the above program is executed, it will create the COMPANY table in your test.db and it will display the following messages −

Opened database successfully
Table created successfully

INSERT Operation

以下 PHP 程序展示了如何在上面示例中创建的 COMPANY 表中创建记录。

Following PHP program shows how to create records in the COMPANY table created in the above example.

<?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();
?>

当执行以上程序时，它将创建 COMPANY 表中给定的记录，并显示以下两行。

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

Opened database successfully
Records created successfully

SELECT Operation

以下 PHP 程序展示了如何从上面示例中创建的 COMPANY 表中获取并显示记录 -

Following PHP program shows how to fetch and display records from the COMPANY table created in the above example −

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

以下 PHP 代码展示了如何使用 UPDATE 语句更新记录，然后从 COMPANY 表中获取并显示更新后的记录。

Following PHP code shows how to use 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 =<<<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 result.

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

以下 PHP 代码展示了如何使用 DELETE 语句删除记录，然后从 COMPANY 表中获取并显示剩余的记录。

Following PHP code shows how to use DELETE statement to delete any record and then fetch and display the remaining 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 =<<<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 result.

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