SQLite classic ...login
SQLite classic tutorial
author:php.cn  update time:2022-04-13 17:05:02

SQLite - PHP


Installation

The SQLite3 extension is enabled by default since PHP 5.3.0. SQLite3 extensions can be disabled at compile time using --without-sqlite3.

Windows users must enable php_sqlite3.dll to use this extension. As of PHP 5.3.0, this DLL is included in the Windows distribution of PHP.

For detailed installation instructions, it is recommended to check out our PHP tutorial and its official website.

PHP Interface API

The following are important PHP programs that can meet your needs for using SQLite databases in PHP programs. If you need more details, check out the official PHP documentation.

34##public int SQLite3::lastErrorCode (void)##5##public string SQLite3::lastErrorMsg (void)public int SQLite3::changes (void)public bool SQLite3::close (void)
Serial NumberAPI & Description
1public void SQLite3 ::open ( filename, flags, encryption_key )

Open a SQLite 3 database. If the build includes encryption, then the key it will try to use.

If the file name filename is assigned the value ':memory:', then SQLite3::open() will create an in-memory database in RAM, which will only Lasts for the duration of the session.

If the file name filename is the actual device file name, then SQLite3::open() will use this parameter value to try to open the database file. If a file with that name does not exist, a new database file with that name will be created.

Optional flags are used to determine whether to open the SQLite database. Turned on by default when using SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.

2##public bool SQLite3::exec ( string $query )
This routine provides A shortcut for executing SQL commands. The SQL command is provided by the sql parameter and can be composed of multiple SQL commands. This program is used to execute a query with no results for a given database.

public SQLite3Result SQLite3::query (string $query)
This routine executes a SQL query, if the query returns a result, a

SQLite3Result object is returned.

This routine returns the most recent failure Numeric result code for SQLite requests.

This routine returns the most recent failure An English text description of the SQLite request.

6
This routine returns the most recent The number of database rows updated or inserted or deleted by the SQL statement.

7
This routine is called before closing SQLite3 ::open() Open database connection.
8public string SQLite3::escapeString ( string $value )

This routine returns a string, In the SQL statement, the string is properly escaped for security reasons.

Connecting to the database

The following PHP code shows how to connect to an existing database. If the database does not exist, it will be created and a database object will be returned.

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

Now, let us run the above program to create our database test.db in the current directory. You can change the path as needed. If the database is created successfully, a message like the one shown below will be displayed:

Open database successfully

Create table

The following PHP code snippet will be used to create a table in the previously created database:

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

When the above program is executed, it creates the COMPANY table in test.db and displays the message shown below:

Opened database successfully
Table created successfully

INSERT operation

below The PHP program shows how to create a record in the COMPANY table created above:

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

When the above program is executed, it creates the given record in the COMPANY table and displays the following two lines:

Opened database successfully
Records created successfully

SELECT Operation

The following PHP program shows how to obtain and display records from the COMPANY table created earlier:

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

When the above program is executed, it produces the following results:

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

UPDATE operation

The following PHP code shows how to use the UPDATE statement to update any record, and then get and display the updated record from the COMPANY table:

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

The above program execution , it produces the following result:

Opened database successfully
1 Record updated successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY =  15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

DELETE Operation

The following PHP code shows how to use the DELETE statement to delete any record and then get and display the remaining records from the COMPANY table:

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

When the above program is executed, it will produce the following results:

Opened database successfully
1 Record deleted successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY =  25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY =  20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY =  65000

Operation done successfully

php.cn