sqlite3 PHP connection and enable extension

Enabling PHP support for SQLite3

First check your php installation by using phpinfo()

<?Php
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

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.

https://www.plus2net.com/python/download/my_db.db

Or you can run this code to create one SQLite database with sample student table.

<?Php 
$my_conn = new SQLite3('my_db.db'); // create database in same location
$sql="CREATE TABLE IF NOT EXISTS
		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
if($my_conn->backup($my_conn_backup)){
	echo " Backup database is created ";
}else{
	echo " Failed to create backup database ";
}

sqlite3_changes()

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.

Questions

How to connect to a SQLite database using PHP SQLite3?
How to create a table in a SQLite database using PHP SQLite3?
How to insert data into a SQLite database using PHP SQLite3?
How to select data from a SQLite database using PHP SQLite3?
How to update data in a SQLite database using PHP SQLite3?
How to delete data from a SQLite database using PHP SQLite3?
How to use prepared statements in PHP SQLite3?
How to handle errors in PHP SQLite3?
How to use transactions in PHP SQLite3?
How to import and export data from a SQLite database using PHP SQLite3?
How to use SQLite3 with PDO in PHP?
How to create a SQLite database in memory using PHP SQLite3?
How to use SQLite3 with PHP to create a web application?
How to use SQLite3 with PHP to build a REST API?
How to optimize PHP SQLite3 queries?
How to use SQLite3 with PHP to create a search engine?
How to use SQLite3 with PHP to create a blogging platform?
How to use SQLite3 with PHP to create an e-commerce website?
How to use SQLite3 with PHP to create a social networking site?

SQLite

Subscribe to our YouTube Channel here

SQLite3 & PHP connection