PHP連接SQLite數據庫
SQLite3擴展名在PHP 5.3.0+
以上都會默認啓用。可以在編譯時使用--without-sqlite3
來禁用它。
Windows用戶可通過啓用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
表中插入給定的記錄,並顯示如下:
SELECT操作
以下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();
執行上述程序時,會產生以下結果:
由於其它操作都類似,只需寫上對應SQL語句,然後使用PHP執行即可,所以其它的示例在這裏就不再一一演示了。如有疑問,可以留言聯繫。