PHP Connect to SQLite Database

Posted by marinedalek on Thu, 27 Jun 2019 21:06:40 +0200

The SQLite3 extension is enabled by default in PHP 5.3.0+ or above.You can disable it at compile time using --without-sqlite3.

Windows users can use this extension by enabling php_sqlite3.dll.Php_sqlite3.dll is included by default in PHP releases after PHP 5.3.0.

For detailed installation instructions, see PHP Tutorial And its official website.

Connect to the SQLite database

The following PHP code shows how to connect to SQLite Database.If the database does not exist, it will create a new database and return a database object.

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

Now run the above program to create the specified database in the current directory: phpdb.db.You can change the path as needed.If the database is successfully created, the following message is provided:

After executing the above statement, a database file named phpdb.db should also be automatically created in the same directory.As shown below-

Create Table

The following PHP program will be used to create a table in the database (phpdb.db) created above:

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

When you execute the above program, it creates a table named company in the database (phpdb.db) with the following message:

Insert Data Operation

The following PHP program shows how to insert data records into the company table created in the above example:

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

// Delete then create table
$sql = "DROP table company";
$ret = $db->exec($sql);

// Create Table Statement

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

When you execute the above program, the given record is inserted into the company table and displayed as follows:

SELECT Operation

The following PHP program shows how to get and display data records from the company table created in the above example:

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

// Delete then create table
$sql = "DROP table company";
$ret = $db->exec($sql);

// Create Table

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

// Query data in tables

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

When you execute the above procedure, the following results are produced:

update operation

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

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

// Delete then create table
$sql = "DROP table company";
$ret = $db->exec($sql);

// Create Table

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

// Salary for update ID=1 is: 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";
}

// Query data in tables

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

When you execute the above procedure, the following results are produced:

Delete operation

The following PHP code shows how to delete any records using the DELETE statement, then get and display the remaining records from the company table:

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

// Delete then create table
$sql = "DROP table company";
$ret = $db->exec($sql);

// Create Table

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

// Update data records with ID less than or equal to 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";
}

// Query data in tables

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

When you execute the above procedure, the following results are produced:

Since other operations are similar, just write the corresponding SQL statement and execute it using PHP, the other examples are no longer illustrated here.If in doubt, you can leave a message to contact.

Topics: SQL PHP Database SQLite

Programmer Think

PHP Connect to SQLite Database

Connect to the SQLite database

Create Table

Insert Data Operation

SELECT Operation

update operation

Delete operation

Hot Topics