PHP连接SQLite数据库

编辑时间: 2018-02-07 13:02:24    关键字:

 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";    } ?> 
PHP

现在,运行上面的程序在当前目录中创建指定数据库: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(); 
PHP

当执行上述程序时,它将在数据库(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(); 
PHP

当执行上述程序时,将向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

执行上述程序时,会产生以下结果:

更新操作

以下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

执行上述程序时,会产生以下结果:

删除操作

以下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(); 
PHP

执行上述程序时,会产生以下结果:

由于其它操作都类似,只需写上对应SQL语句,然后使用PHP执行即可,所以其它的示例在这里就不再一一演示了。如有疑问,可以留言联系。

推荐热图

合作推荐

2010-2018 可思数据版权所有 About SYKV | ICP备案:京ICP备14056871号