SQLite PHP Tutorial

  Total Views : 768
  Comments : 0
  Keywords : Connect to sqlite database in php using pdo sqlite extension with examples, Connect and create sqlite database, tables using php programming with examples

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 driver to communicate with SQLite databases. By default PHP latest version 5.1 contains SQLite3 extension to interact with SQLite databases.  By using PDO driver 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 exntesion in PHP to connect 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 a SQLite database if exists. In case if 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 current application directory and we will get the result like as shown below. 

 

Database Opened!!!

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

Create Table in SQLite Database using PHP

Now we will create table called “Product” in SQLite “DBUsingPHP.db” database using PHP code like 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 database “DBUsingPHP.db” and give 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 above PHP program it will insert data in “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 above PHP program we will get 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 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 table using PHP programming language for that write the code like 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 “Product” table where p_id > 1. Once we execute above PHP program we will get 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. 

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User