PHP SQLite insert record and get lastInsertId()

SQLite

We will create the database and connection object.
$my_conn = new PDO('sqlite:my_student.sqlite3');
We will use $my_conn to execute our query.

By using INSERT query we will add new record.

Unique ID by lastInsertedId()

Here we are added the name, class, mark and sex columns of the new record. After successful addition of record SQLite will assign the unique number to the ID column. 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.
// 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';    
$sex='Female';  

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

$sql=$my_conn->prepare("INSERT INTO student (name,class,mark,sex) 
	values(:name,:class,:mark,:sex)");
$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(':sex',$sex,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`, `sex`) 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
Download sample script for SQLite with instructions on how to use.

Check PHP pdo support for SQLite using phpinfo()

PHP SQLite PDO Functions


plus2net.com







Post your comments , suggestion , error , requirements etc here




We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer