sqlite3的名擴展在PHP 5.3.0+以上都會默認啟用。在可以compile-時使用--without-sqlite3來禁用它。
視窗柯林斯用戶通過啟用php_sqlite3.dll才能使用此擴展。php_sqlite3.dll默認所有遊戲在PHP 5.3.0之後的PHP發行版中。
有關詳細的安裝說明,請查看PHP教程及其官方網站。
連接到的SQLite數據庫以下PHP代碼顯示如何連接到SQLite數據庫。如果數據庫不存在,那麼它將創建一個新的數據庫,最後將返回一個數據庫對象。
- <?php
- class SQLiteDB extends SQLite3
- {
- function __construct()
- {
- $this->open('phpdb.db');
- }
- }
- $db = new SQLiteDB();
- if(!$db){
- echo $db->lastErrorMsg();
- } else {
- echo "Yes, Opened database successfully\n";
- }
- ?>
複製代碼
現在,運行上面的程序在當前目錄中創建指定數據庫:phpdb.db。可以根據需要更改路徑。如果數據庫成功創建,則會提供以下消息:
在執行上面語句後,應該也會在相同的目錄下自動創建一個名稱為:phpdb.db的數據庫文件。如下所示 -
創建表以下PHP程序將用於在上面創建的數據庫(phpdb.db)中創建一個表:
- <?php
- class SQLiteDB extends SQLite3
- {
- function __construct()
- {
- $this->open('phpdb.db');
- }
- }
- $db = new SQLiteDB();
- if(!$db){
- echo $db->lastErrorMsg();
- } else {
- echo "Yes, Opened database successfully<br/>\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 "Yes, Table created successfully<br/>\n";
- }
- $db->close();
-
複製代碼
當執行上述程序時,它將在數據庫(phpdb.db)中創建一個名稱為:company的表,並顯示以下消息:
插入數據操作以下PHP顯示程序如何了上述在示例中創建³³的company表中插入數據記錄:
- <?php
- class SQLiteDB extends SQLite3
- {
- function __construct()
- {
- $this->open('phpdb.db');
- }
- }
- $db = new SQLiteDB();
- if(!$db){
- echo $db->lastErrorMsg();
- } else {
- echo "Yes, Opened database successfully<br/>\n";
- }
-
- // 先删除后创建表
- $sql = "DROP table company";
- $ret = $db->exec($sql);
-
- // 创建表语句
-
- $sql =<<<EOF
- CREATE TABLE if not exists 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 "Yes, Table created successfully<br/>\n";
- }
-
- // $db->close();
-
- $sql =<<<EOF
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (1, 'Maxsu', 26, 'Haikou', 20000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (2, 'Allen', 25, 'Guangzhou', 15000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (3, 'Tenny', 23, 'Shanghai', 20000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (4, 'Weiwang', 25, 'Beijing ', 65000.00 );
- EOF;
-
- $ret = $db->exec($sql);
- if(!$ret){
- echo $db->lastErrorMsg();
- } else {
- echo "Yes, Some Records has Inserted successfully<br/>\n";
- }
- $db->close();
-
-
複製代碼
當執行上述程序時,將向company表中插入給定的記錄,並顯示如下:
選擇操作以下PHP顯示程序如何了上述從示例中創建³³的company表中並電子雜誌數據顯示記錄:
- <?php
- class SQLiteDB extends SQLite3
- {
- function __construct()
- {
- $this->open('phpdb.db');
- }
- }
- $db = new SQLiteDB();
- if(!$db){
- echo $db->lastErrorMsg();
- } else {
- echo "Yes, Opened database successfully<br/>\n";
- }
-
- // 先删除后创建表
- $sql = "DROP table company";
- $ret = $db->exec($sql);
-
- // 创建表
-
- $sql =<<<EOF
- CREATE TABLE if not exists 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 "Yes, Table created successfully<br/>\n";
- }
-
- // $db->close();
-
- $sql =<<<EOF
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (1, 'Maxsu', 26, 'Haikou', 20000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (2, 'Allen', 25, 'Guangzhou', 15000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (3, 'Tenny', 23, 'Shanghai', 20000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (4, 'Weiwang', 25, 'Beijing ', 65000.00 );
- EOF;
-
- $ret = $db->exec($sql);
- if(!$ret){
- echo $db->lastErrorMsg();
- } else {
- echo "Yes, Some Records has Inserted successfully<br/>\n";
- }
-
- // 查询表中的数据
-
- echo "<b> Select Data from company table :</b><hr/>";
-
- $sql =<<<EOF
- SELECT * from COMPANY;
- EOF;
-
- $ret = $db->query($sql);
- while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
- echo "ID = ". $row['ID'] . "<br/>\n";
- echo "NAME = ". $row['NAME'] ."<br/>\n";
- echo "ADDRESS = ". $row['ADDRESS'] ."<br/>\n";
- echo "SALARY = ".$row['SALARY'] ."<br/>\n\n";
- echo '----------------------------------<br/>';
- }
-
- echo "Operation done successfully\n";
-
- $db->close();
-
-
複製代碼
執行上述程序時,會產生以下結果:
更新操作以下PHP顯示代碼如何了使用UPDATE語句來更新記錄,從然後company表中並電子雜誌更新顯示的記錄:
- <?php
- class SQLiteDB extends SQLite3
- {
- function __construct()
- {
- $this->open('phpdb.db');
- }
- }
- $db = new SQLiteDB();
- if(!$db){
- echo $db->lastErrorMsg();
- } else {
- echo "Yes, Opened database successfully<br/>\n";
- }
-
- // 先删除后创建表
- $sql = "DROP table company";
- $ret = $db->exec($sql);
-
- // 创建表
-
- $sql =<<<EOF
- CREATE TABLE if not exists 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 "Yes, Table created successfully<br/>\n";
- }
-
- // $db->close();
-
- $sql =<<<EOF
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (1, 'Maxsu', 26, 'Haikou', 20000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (2, 'Allen', 25, 'Guangzhou', 15000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (3, 'Tenny', 23, 'Shanghai', 20000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (4, 'Weiwang', 25, 'Beijing ', 65000.00 );
- EOF;
-
- $ret = $db->exec($sql);
- if(!$ret){
- echo $db->lastErrorMsg();
- } else {
- echo "Yes, Some Records has Inserted successfully<br/>\n";
- }
-
- // 更新ID=1的薪水为:29999
-
- $sql = 'UPDATE COMPANY set SALARY = 29999.00 where ID=1';
- $ret = $db->exec($sql);
- if(!$ret){
- echo $db->lastErrorMsg();
- } else {
- echo $db->changes(), " Record(ID=1) updated successfully<br/>\n";
- }
-
-
- // 查询表中的数据
-
- echo "<b> Select Data from company table :</b><hr/>";
-
- $sql =<<<EOF
- SELECT * from COMPANY;
- EOF;
-
- $ret = $db->query($sql);
- while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
- echo "ID = ". $row['ID'] . "<br/>\n";
- echo "NAME = ". $row['NAME'] ."<br/>\n";
- echo "ADDRESS = ". $row['ADDRESS'] ."<br/>\n";
- echo "SALARY = ".$row['SALARY'] ."<br/>\n\n";
- echo '----------------------------------<br/>';
- }
-
- echo "Operation done successfully\n";
-
- $db->close();
複製代碼
執行上述程序時,會產生以下結果:
刪除操作以下PHP顯示代碼如何了使用DELETE語句刪除任何記錄,從然後company表中並電子雜誌剩餘顯示的記錄:
- <?php
- class SQLiteDB extends SQLite3
- {
- function __construct()
- {
- $this->open('phpdb.db');
- }
- }
- $db = new SQLiteDB();
- if(!$db){
- echo $db->lastErrorMsg();
- } else {
- echo "Yes, Opened database successfully<br/>\n";
- }
-
- // 先删除后创建表
- $sql = "DROP table company";
- $ret = $db->exec($sql);
-
- // 创建表
-
- $sql =<<<EOF
- CREATE TABLE if not exists 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 "Yes, Table created successfully<br/>\n";
- }
-
- // $db->close();
-
- $sql =<<<EOF
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (1, 'Maxsu', 26, 'Haikou', 20000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (2, 'Allen', 25, 'Guangzhou', 15000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (3, 'Tenny', 23, 'Shanghai', 20000.00 );
-
- INSERT INTO company (ID,NAME,AGE,ADDRESS,SALARY)
- VALUES (4, 'Weiwang', 25, 'Beijing ', 65000.00 );
- EOF;
-
- $ret = $db->exec($sql);
- if(!$ret){
- echo $db->lastErrorMsg();
- } else {
- echo "Yes, Some Records has Inserted successfully<br/>\n";
- }
-
- // 更新ID小于等于2的数据记录
-
- $sql =<<<EOF
- DELETE from COMPANY where ID<=2;
- EOF;
- $ret = $db->exec($sql);
- if(!$ret){
- echo $db->lastErrorMsg();
- } else {
- echo $db->changes(), " Record(ID<=2) deleted successfully<br/>\n";
- }
-
- // 查询表中的数据
-
- echo "<b> Select Data from company table :</b><hr/>";
-
- $sql =<<<EOF
- SELECT * from COMPANY;
- EOF;
-
- $ret = $db->query($sql);
- while($row = $ret->fetchArray(SQLITE3_ASSOC) ){
- echo "ID = ". $row['ID'] . "<br/>\n";
- echo "NAME = ". $row['NAME'] ."<br/>\n";
- echo "ADDRESS = ". $row['ADDRESS'] ."<br/>\n";
- echo "SALARY = ".$row['SALARY'] ."<br/>\n\n";
- echo '----------------------------------<br/>';
- }
-
- echo "Operation done successfully\n";
-
- $db->close();
複製代碼
執行上述程序時,會產生以下結果:
最後如要列出資料庫內有多少Table name
- <?php
- $db = new SQLite3('db/chinhook.db');
- $tablesquery = $db->query("SELECT name FROM sqlite_master WHERE type='table';");
-
- while ($table = $tablesquery->fetchArray(SQLITE3_ASSOC)) {
- echo $table['name'] . '<br />';
- }
- ?>
複製代碼
參考文章
https://www.yiibai.com/sqlite/php-with-sqlite.html
https://stackoverflow.com/questions/17997722/sqlite3-query-to-list-all-tables-in-database-only-shows-one-table
http://www.sqlitetutorial.net/sqlite-tutorial/sqlite-show-tables/
留言列表