SQLite PHP Tutorial

Here we will learn how to use SQLite in PHP programming language to create database, tables and to perform INSERT, UPDATE, DELETE and SELECT operations on SQLite database using PDO_SQLITE extension in PHP with examples.

SQLite in PHP Interface

In PHP the PDO_SQLite extension provides PDO drivers to communicate with SQLite databases. By default PHP latest version 5.1 contains SQLite3 extension to interact with SQLite databases.  By using PDO drivers in PHP language we can perform different operations like connect to database, create tables, insert data in tables, etc. based on our requirements.

 

In case if you are using WINDOWS OS then you need to download and enable php_sqlite3.dll to use PDO_SQLite extension in PHP to connect the SQLite database. You can download this dll from our PHP Tutorial or from official website.

Connect to SQLite Database in PHP

Write the code like as shown following to connect to an SQLite database if exists. In case if the database not exists then it will create and connect to a database.

 

<?php

class DBphp extends SQLite3

{

function __construct()

{

$this->open('DBUsingPHP.db');

}

}

$db = new DBphp();

if(!$db){

echo "Oops!!! Something went wrong!!!";

} else {

echo "Database Opened!!!";

}

?>

When we execute above program the new database “DBUsingPHP.db” will create in the current application directory and we will get the result as shown below. 

 

Database Opened!!!

Here in above example, we have used the following PHP methods to do operations on the SQLite database:

Create Table in SQLite Database using PHP

Now we will create a table called “Product” in SQLite “DBUsingPHP.db” database using PHP code as shown below.

 

<?

class DBphp extends SQLite3

{

function __construct()

{

$this->open('DBUsingPHP.db');

}

}

$db = new DBphp();

$res=$db->exec("CREATE TABLE Product (p_id INTEGER PRIMARY KEY

AUTOINCREMENT,p_name TEXT NOT NULL,price REAL,quantity

INTEGER);");

if($res)

echo "Table created!!!\n";

else

echo "Oops!!! Something went wrong!!!";

}

$db->close();

?>

When we execute above program it will create table called “Product” in the database “DBUsingPHP.db” and give a result like as shown below.

 

Table created!!!

Insert Data in SQLite Table using PHP

Now we will insert data in newly created table “Product” using following code in PHP.

 

<?

class DBphp extends SQLite3

{

function __construct()

{

$this->open('DBUsingPHP.db');

}

}

$db = new DBphp();

$query1="INSERT INTO Product".

"(p_name,price,quantity)".

"VALUES ('pencil',10,50);";

$query2="INSERT INTO Product".

"(p_name,price,quantity)".

"VALUES ('Eraser',5,60);";

$db->exec($query1);

$db->exec($query2);

$db->close();

?>

When we execute the above PHP program it will insert data in the “Product” table. 

Display SQLite Table Data using PHP

Now write code like as shown following to display data from SQLite table using PHP programming language.

 

<?

class DBphp extends SQLite3

{

function __construct()

{

$this->open('DBUsingPHP.db');

}

}

$db = new DBphp();

$query1="SELECT * FROM Product;";

$result=$db->query($query1);

echo "p_id\tp_name\tprice\tquantity\n";

while($row= $result->fetchArray()){

echo $row['p_id'] . "\t".

$row['p_name'] . "\t".

$row['price']. "\t".

$row['quantity']."\n";

}

$db->close();

?>

When we execute the above PHP program we will get a result like as shown below

 

p_id  p_name      price quantity

----  ------      ----  --------

1     pencil      10    50

2     Eraser      5     60

Update SQLite Table Data in PHP

Now we will try to update quantity of all products by 20% using following statements in PHP.

 

<?

class DBphp extends SQLite3

{

function __construct()

{

$this->open('DBUsingPHP.db');

}

}

 

$db = new DBphp();

$query="UPDATE Product SET quantity=quantity + (quantity * 0.2)";

$db->exec($query);

$query1="SELECT * FROM Product";

$result=$db->query($query1);

 

echo "p_id\tp_name\tprice\tquantity\n";

while($row= $result->fetchArray()){

echo $row['p_id'] . "\t".

$row['p_name'] . "\t".

$row['price']. "\t".

$row['quantity']."\n";

}

$db->close();

?>

If you observe above program we are updating and displaying SQLite table records using the PHP program. If we execute above code we will get result like as shown below.

 

p_id  p_name      price quantity

----  -------     ----  ----

1     pencil      10    60

2     Eraser      5     72

Delete SQLite Table Data using PHP

Now we will delete data from a table using PHP programming language for that write the code as shown follow.

 

<?

class DBphp extends SQLite3

{

function __construct()

{

$this->open('DBUsingPHP.db');

}

}

 

$db = new DBphp();

$query="DELETE FROM Product WHERE p_id>1;";

$db->exec($query);

$query1="SELECT * FROM Product";

$result=$db->query($query1);

echo "p_id\tp_name\tprice\tquantity\n";

while($row= $result->fetchArray()){

echo $row['p_id'] . "\t".

$row['p_name'] . "\t".

$row['price']. "\t".

$row['quantity']."\n";

}

$db->close();

?>

If you observe above PHP program we are deleting data from the “Product” table where p_id > 1. Once we execute the above PHP program we will get a result like as shown below.

 

p_id  p_name      price quantity

----  ------      ----  --------

1     pencil      10    60

This is how we can use SQLite databases in PHP programming language to connect, create, database, tables and insert, update, delete, and display data based on our requirements.