简述
在本章中，您将学习如何在 PHP 程序中使用 SQLite。安装
自 PHP 5.3.0 起默认启用 SQLite3 扩展。可以通过使用禁用它--without-sqlite3 在编译时。 Windows 用户必须启用 php_sqlite3.dll 才能使用此扩展。自 PHP 5.3.0 起，此 DLL 包含在 PHP 的 Windows 发行版中。 有关详细的安装说明，请查看我们的 PHP 教程及其官方网站。PHP 接口 API
以下是重要的 PHP 例程，它们可以满足您从 PHP 程序使用 SQLite 数据库的要求。如果您正在寻找更复杂的应用程序，那么您可以查看 PHP 官方文档。连接到数据库
以下 PHP 代码显示了如何连接到现有数据库。如果数据库不存在，则将创建它并最终返回一个数据库对象。<?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在当前目录中。您可以根据需要更改路径。如果数据库创建成功，那么它将显示以下消息 - Open database successfully 创建表
下面的 PHP 程序将用于在之前创建的数据库中创建一个表。 <?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();?> 当上面的程序被执行时，它会在你的目录中创建 COMPANY 表 test.db 它将显示以下消息 - Opened database successfullyTable created successfully 插入操作
下面的 PHP 程序显示了如何在上面示例中创建的 COMPANY 表中创建记录。 <?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 表中创建给定的记录，并显示以下两行。 Opened database successfullyRecords created successfully 选择操作
以下 PHP 程序显示了如何从上面示例中创建的 COMPANY 表中获取和显示记录 - <?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();?> 执行上述程序时，将产生以下结果。 Opened database successfullyID = 1NAME = PaulADDRESS = CaliforniaSALARY = 20000ID = 2NAME = AllenADDRESS = TexasSALARY = 15000ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000Operation done successfully 更新操作
以下 PHP 代码显示了如何使用 UPDATE 语句更新任何记录，然后从 COMPANY 表中获取并显示更新的记录。 <?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();?> 执行上述程序时，将产生以下结果。 Opened database successfully1 Record updated successfullyID = 1NAME = PaulADDRESS = CaliforniaSALARY = 25000ID = 2NAME = AllenADDRESS = TexasSALARY = 15000ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000Operation done successfully 删除操作
以下 PHP 代码展示了如何使用 DELETE 语句删除任何记录，然后从 COMPANY 表中获取并显示剩余的记录。 <?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();?> 执行上述程序时，将产生以下结果。 Opened database successfully1 Record deleted successfullyID = 1NAME = PaulADDRESS = CaliforniaSALARY = 25000ID = 3NAME = TeddyADDRESS = NorwaySALARY = 20000ID = 4NAME = MarkADDRESS = Rich-MondSALARY = 65000Operation done successfully
