PDO SQLite Class

標題：採Class寫法之PDO連接SQLite

PHP (index.php)：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

<?php $test = new sql; echo $test->connectsql(); class sql { public $dsn = "sqlite:hello.sqlite3"; public $user = null; public $password = null; //建立資料庫 function connectsql() { try { //連結資料庫 $dbq = new PDO($this->dsn, $this->user, $this->password); echo "PDO Connection ok<br />"; //建立資料表db_math //新增INTEGER屬性PRIMARY KEY特性的id欄位 //新增INT屬性的project欄位 //新增TEXT屬性的function欄位 $dbq->exec("CREATE TABLE db_math(id INTEGER PRIMARY KEY, project INT, function TEXT)"); echo "Create Table ok<br />"; //建立索引db_math_id並指定為資料表db_math的id欄位 $dbq->exec("CREATE INDEX db_math_id ON db_math(id)"); echo "Create Index ok<br />"; //寫入資料3筆 $dbq->exec("INSERT INTO db_math(project,function) VALUES (1,'2+3')"); $dbq->exec("INSERT INTO db_math(project,function) VALUES (1,'4+5')"); $dbq->exec("INSERT INTO db_math(project,function) VALUES (2,'6+7')"); echo "Insert Data ok<br />"; //檢查若未成功則還原 $dbq->beginTransaction(); //搜尋全部資料 $stq = $dbq->prepare('SELECT *FROM db_math'); $stq->execute(); $result = $stq->fetchAll(); //印出資料內容 foreach($result as $arr) { print_r($arr); echo "<br />"; } //搜尋project欄位值為1的資料 $stq = $dbq->prepare("SELECT * FROM db_math WHERE project = '1'"); $stq->execute(); $result = $stq->fetchAll(); //印出資料內容 foreach($result as $arr) echo $arr['id'].", ".$arr['project'].", ".$arr['function']."<br />"; $this->dsn = null; } catch (PDOException $e) { //印出錯誤訊息 echo "Connection failed: ".$e->getMessage()."<br />"; $this->dsn = null; } } } ?>

輸出結果：
PDO Connection ok Create Table ok Create Index ok Insert Data ok Array ( [id] => 1 [0] => 1 [project] => 1 [1] => 1 [function] => 2+3 [2] => 2+3 ) Array ( [id] => 2 [0] => 2 [project] => 1 [1] => 1 [function] => 4+5 [2] => 4+5 ) Array ( [id] => 3 [0] => 3 [project] => 2 [1] => 2 [function] => 6+7 [2] => 6+7 ) 1, 1, 2+3 2, 1, 4+5
說明：
採類別的寫法撰寫基本PDO連結資料庫，以及建立SQLite3資料表並進行Select搜尋之範例，
使用PDO請記得將相關之PHP extension打開。