PDO SQLite connection object and creating sample student table

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

By default, PDO does not throw exceptions for errors, which means you might not notice issues immediately.
Here are the error modes.

PDO::ERRMODE_SILENT:
- This is the default error mode.
- 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

Subscribe to our YouTube Channel here

PHP SQLite PDO connection object and create table

Database file path

Setting the Error Mode in PDO

Connection to SQLite or MySQL database

Subscribe