PDO SQLite connection object and creating sample student table

Create a PDO SQLite connection

Use __DIR__ for a stable file path. PDO throws exceptions by default in PHP 8+, but setting the error mode explicitly keeps the intent clear and supports older runtimes.

<?php
$db = new PDO('sqlite:' . __DIR__ . '/my_student.db');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

echo 'SQLite connection ready';
?>

Current example first; detailed examples below are retained for additional learning and comparison.

We will create the database and connection object.

$my_conn = new PDO('sqlite:my_student.sqlite3');

Database file path

The database path we can set by using this code. We can change the database file extension also. Use one of the line.

//$my_conn = new PDO('sqlite:my_student.sqlite3');// same path as file execution  
//$my_conn = new PDO('sqlite:D:\\sqlite-data\\my_student.db');// different path
$my_conn = new PDO('sqlite:'.dirname(__FILE__).'/test.db'); // same location

We will use $my_conn to create our table with some sample data.

SQLite database connection from PHP PDO and adding error mode handling by using configuration A2

Setting the Error Mode in PDO

As of PHP 8.0, PDO::ERRMODE_EXCEPTION is the default. Setting it explicitly is still useful when the example should make its error-handling policy clear and when older PHP versions are being considered.
Here are the error modes.

PDO::ERRMODE_SILENT:
- This was the default error mode before PHP 8.0.
- PDO will set an error code for you to check manually, but it won't interrupt script execution or throw any exceptions.
PDO::ERRMODE_WARNING:
- In this mode, PDO will emit a PHP warning whenever an error occurs.
- The script will continue executing, but you'll be notified of the issue through warnings.
PDO::ERRMODE_EXCEPTION:
- This mode causes PDO to throw a PDOException whenever an error occurs.
- This is the most robust option, as it allows you to handle errors using try-catch blocks.
- It is often recommended for better error handling and debugging.

As a developer we will use ERRMODE_EXCEPTION inside our try catch code block.
This we will keep inside our config.php file to connect from other files.

config.php

try {
    $my_conn = new PDO('sqlite:my_database.db');
    $my_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully!";
} 
catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

We used create table query to create our studet table.

By applying insert command student records are added to the table.

The full code is here, we used this student table as sample data in our SQLite tutorials.

<?php
//require "menu.php"; 
require 'config.php';// Connection object $my_conn is taken from here.    
try {
    	
   $count=$my_conn->prepare("CREATE TABLE IF NOT EXISTS
				student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text 
                      )");
					  
  if($count->execute()){
  echo " TABLE student created ";
  }else{
  echo " Not able to create student ";
  }	
 
$count=$my_conn->exec("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');");
echo "<br> Number of records added : ".$count;
// Close file db connection
$my_conn = null;
}
catch(PDOException $e) 
  {
    // Print PDOException message
    echo $e->getMessage();
  } 
?>

Display some sample records.

$sql="SELECT * FROM student LIMIT 0,5";

echo "<table>";
foreach ($my_conn->query($sql) as $row) {
 echo "<tr ><td>$row[id]</td><td>$row[name]</td></tr>";
}
echo "</table>";

Delete this table ?

<?php
try {
    //$my_conn = new PDO('sqlite:my_student.sqlite3');// same path as file execution  
	$my_conn = new PDO('sqlite:D:\\sqlite-data\\my_student.db');// different path
    // Set errormode to exceptions
	$my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

	$sql=$my_conn->prepare("DROP TABLE  student");

	if($sql->execute()){
		echo " <br><br>Table deleted. ";
	}else{
		print_r($sql->errorInfo()); 
	}
	$my_conn = null;
}
catch(PDOException $e) 
	{
		// Print PDOException message
		echo $e->getMessage();
	} 
?>

Connection to SQLite or MySQL database

Configuring MySQL or SQLite database connection

By changing the connection object inside config.php file we can easily switch our database. Check this in our scripts.
Download sample script for SQLite with instructions on how to use.

SQLite support Display records from SQLite database table

PHP SQLite PDO Functions

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Connect PHP to SQLite with PDO

Create a PDO SQLite connection

Database file path

Setting the Error Mode in PDO

Connection to SQLite or MySQL database

Subhendu Mohapatra