PHP SQLite insert single or multiple record and get the inserted row ID lastInsertId()

We will create the database and connection object.

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

or use one of this line to create database at different locations.

// 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
$my_conn = new PDO('sqlite:'.dirname(__FILE__).'/test.db'); // same location

We will use $my_conn to execute our query.

Create table

$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 ";
  }

By using INSERT query we will add new record.

Unique ID by lastInsertId()

Here we are added the name, class, mark and gender columns of the new record. After successful addition of record SQLite will assign the unique number to the ID column ( ROWID ). This unique number or ID is the next highest available ID of the primary key column. If the existing highest id is 35 then 36 is inserted as ID by SQLite for the new record.

$my_conn = new PDO('sqlite:'.dirname(__FILE__).'/test.db'); // same location
    // Set errormode to exceptions
$my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$sql=$my_conn->prepare("INSERT INTO student (name,class,mark,gender) 
	values('ABC','Four',80,'Female')");
if($sql->execute()){
	echo " <br><br>Record added ID: ". $my_conn->lastInsertId();
}else{
	print_r($sql->errorInfo()); 
}
$my_conn = null;

Using PDO with Parameter query

// Create (connect to) SQLite database in file
$my_conn = new PDO('sqlite:my_student.sqlite3');
// Set errormode to exceptions
  $my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
 
////////////Collect  data/////////////
$name='plus2net'; 
$mark=50;               
$class='Four';    
$gender='Female';  

///////// End of data collection /// 

$sql=$my_conn->prepare("INSERT INTO student (name,class,mark,gender) 
	values(:name,:class,:mark,:gender)");
$sql->bindParam(':name',$name,PDO::PARAM_STR, 25);
$sql->bindParam(':class',$class,PDO::PARAM_STR, 25);
$sql->bindParam(':mark',$mark,PDO::PARAM_INT, 15);
$sql->bindParam(':gender',$gender,PDO::PARAM_STR, 10);

if($sql->execute()){
echo "Successfully added record ";
echo "<br><br>ID of the new record is  : ".$my_conn->lastInsertId();
}
else{
print_r($sql->errorInfo()); // if any error is there it will be posted
$msg=" Database problem, please contact site admin ";
}

Output is here

Successfully added record

ID of the new record is : 36

Adding multiple records

We can get number of records added by using rowCount()

$query="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'),

(35, 'Rows Noump', 'Six', 88, 'female');";


$sql=$my_conn->prepare($query);
$sql->execute();

echo "<br><br> Number of records added  : ".$sql->rowCount();

Output is here

Number or records added : 35

Difference with AUTOINCREMENT & INTEGER PRIMARY KEY

If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table.

With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database.

Example

The highest ROWID is 5. This record is deleted and one more record is added to the same table. The new ROWID wihtout AUTOINCRMENT is 5 but if we use INTEGER PRIMARY KEY AUTOINCREMENT for the id column then ROWID will be 6.
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 insert record and get lastInsertId()

Unique ID by lastInsertId()

Using PDO with Parameter query

Adding multiple records

Difference with AUTOINCREMENT & INTEGER PRIMARY KEY

Example

Subscribe