SQLite3 & PHP connection

Enabling PHP support for SQLite3

First check your php installation by using phpinfo()
echo phpinfo();
You should see the details about the SQLite3 like this if the same is available.
phpinfo() output for sqlite3 support3

If sqlite3 support is not available then it is to be enabled by opening the PHP configuration file, php.ini and un-comment the extension.

enabling sqlite3 extension inside php.ini file
Inside the extension directory in your system you must have php_sqlite3.dll in order to use this extension.

Connecting and displaying records of SQLite Database by SQLite3 using PHP Script - 18

Connection to SQLite database

SQLite3 is a serverless, file-based database system. When connecting to a SQLite3 database, we must specify the path to the database file.

If the file exists, SQLite3 will open it and establish a connection. If the file does not exist, SQLite3 will create a new database file with the given name.

You can use any extension you want for the database file, but it is common to use the `.db` extension.
//$my_conn = new SQLite3('my_db.db');// same path as file execution  
//$my_conn = new SQLite3('D:\\my_db\\my_db.db');// Full path with file name
$my_conn = new SQLite3(dirname(__FILE__).'/my_db.db'); // same location
Once our connection is established we can collect some records from a table. Note that here we already have student table with some records.
$my_conn = new SQLite3('my_db.db'); // connection to db
$sql="SELECT * FROM student "; // Query to collect records 
echo "<table>";
$results = $my_conn->query($sql);
while ($row = $results->fetchArray()) {
	echo "<tr ><td>$row[id]</td><td>$row[name]</td></tr>";
echo "</table>";
You can download the sample SQLite database with student table from here also.


Or you can run this code to create one SQLite database with sample student table.
$my_conn = new SQLite3('my_db.db'); // create database in same location
		student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text 
$my_conn->exec($sql); // Create table 

$sql="INSERT INTO `student` 
(`id`, `name`, `class`, `mark`, `gender`) VALUES
(1, 'John Deo', 'Four', 75, 'female'),
(2, 'Max Ruin', 'Three', 85, 'male'),
(3, 'Arnold', 'Three', 55, 'male'),
(4, 'Krish Star', 'Four', 60, 'female'),
(5, 'John Mike', 'Four', 60, 'female'),
(6, 'Alex John', 'Four', 55, 'male'),
(7, 'My John Rob', 'Five', 78, 'male'),
(8, 'Asruid', 'Five', 85, 'male'),
(9, 'Tes Qry', 'Six', 78, 'male'),
(10, 'Big John', 'Four', 55, 'female'),
(11, 'Ronald', 'Six', 89, 'female'),
(12, 'Recky', 'Six', 94, 'female'),
(13, 'Kty', 'Seven', 88, 'female'),
(14, 'Bigy', 'Seven', 88, 'female'),
(15, 'Tade Row', 'Four', 88, 'male'),
(16, 'Gimmy', 'Four', 88, 'male'),
(17, 'Tumyu', 'Six', 54, 'male'),
(18, 'Honny', 'Five', 75, 'male'),
(19, 'Tinny', 'Nine', 18, 'male'),
(20, 'Jackly', 'Nine', 65, 'female'),
(21, 'Babby John', 'Four', 69, 'female'),
(22, 'Reggid', 'Seven', 55, 'female'),
(23, 'Herod', 'Eight', 79, 'male'),
(24, 'Tiddy Now', 'Seven', 78, 'male'),
(25, 'Giff Tow', 'Seven', 88, 'male'),
(26, 'Crelea', 'Seven', 79, 'male'),
(27, 'Big Nose', 'Three', 81, 'female'),
(28, 'Rojj Base', 'Seven', 86, 'female'),
(29, 'Tess Played', 'Seven', 55, 'male'),
(30, 'Reppy Red', 'Six', 79, 'female'),
(31, 'Marry Toeey', 'Four', 88, 'male'),
(32, 'Binn Rott', 'Seven', 90, 'female'),
(33, 'Kenn Rein', 'Six', 96, 'female'),
(34, 'Gain Toe', 'Seven', 69, 'male'),
(35, 'Rows Noump', 'Six', 88, 'female')";

$my_conn->exec($sql); // insert 35 rows of data
echo 'Number of rows inserted: ', $my_conn->changes();
echo '<BR>Last inserted row ID : ', $my_conn->lastInsertRowID() ;

Create a backup database

backup() returns true on success. We will create one backup database using our main database.
$my_conn = new SQLite3('my_db.db');// Connect to existing database   

$my_conn_backup = new SQLite3('D:\\my_db\\backup.db'); // location with file name
	echo " Backup database is created ";
	echo " Failed to create backup database ";


This function is a useful tool for working with SQLite databases. It can be used to verify that queries are executed successfully.
SELECT changes()
More on sqlite3 changes() function
exec() : Execute a result less SQL
query() : Getting result object by executing SQL
PHP SQLite Pagination Script for Efficient Data Management
PHP SQLite Parameterized Query

Sample script using PHP , SQLite3 connection and SQLite database : plus2net_sqlite3_v1

These scripts are developed by using PHP SQLite3 connection to learn different queries and how to execute to mange SQLite database.
Download sample scripts using SQLite3 with instructions on how to use.


Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net


    Post your comments , suggestion , error , requirements etc here

    PHP video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer