PHP 操作SQLite

標籤：

串連資料庫

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

 

上述程式執行時，它會在 test.db 中建立 COMPANY 表，並顯示下面所示的訊息：

Opened database successfullyTable created successfully

 

INSERT 操作

下面的 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

 

SELECT 操作

下面的 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

 

UPDATE 操作

下面的 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

 

DELETE 操作

下面的 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

 

 

PHP 操作SQLite