PHP SQLite connection and creating 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.

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"; 
     
try {
    // Create (connect to) SQLite database in file
    //$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);
    
    // Create table student
	
   $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 ";
  }	
 
$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');");
// 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.

Check PHP pdo support for SQLite using phpinfo()

PHP SQLite PDO Functions

Subscribe to our YouTube Channel here

PHP SQLite PDO connection and create table

Database file path

Connection to SQLite or MySQL database

Subscribe