PHP SQLite insert record and get lastInsertId()

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

Unique ID by lastInsertId()

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

Using PDO with Parameterized Query

A Parameterized Query is a method used in SQL to safely insert user input into SQL statements. Instead of embedding user inputs directly into the SQL query, placeholders are used, and the actual data values are provided at runtime. This ensures that user inputs are treated as data and not executable code, thereby preventing SQL injection attacks. Parameterized queries are essential for secure database operations, especially when handling dynamic user input.

• Security: Parameterized queries protect against SQL injection by treating user inputs as data rather than executable code.
• Flexibility: They allow the same SQL statement to be reused with different values, making the code more maintainable and versatile.
• Usage: Commonly used in conjunction with prepared statements to enhance both security and performance in database operations.

// 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 /// 

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

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

Successfully added record

ID of the new record is : 36

Adding multiple records by using Parameterized Query

try {
    $sql = "INSERT INTO student (name, class, mark, gender) 
		VALUES (:name, :class, :mark, :gender)";

    // Prepare the SQL statement
    $stmt = $my_conn->prepare($sql);

    // Array of student records to be added
    $students = [
        ['name' => 'Alice Smith', 'class' => 'Four', 'mark' => 88, 'gender' => 'Female'],
        ['name' => 'Bob Johnson', 'class' => 'Five', 'mark' => 92, 'gender' => 'Male'],
        ['name' => 'Charlie Brown', 'class' => 'Four', 'mark' => 75, 'gender' => 'Male']
    ];

    // Loop through each student and execute the prepared statement
   foreach ($students as $student) {
     // Bind parameters with specific data types
     $stmt->bindParam(':name', $student['name'], PDO::PARAM_STR, 50);//Limit to 50 char
     $stmt->bindParam(':class', $student['class'], PDO::PARAM_STR, 10);//Limit to 10 char
     $stmt->bindParam(':mark', $student['mark'], PDO::PARAM_INT); //Ensure it's an integer
     $stmt->bindParam(':gender', $student['gender'], PDO::PARAM_STR, 10);//Limit to 10 char
       // Execute the statement
      $count = $stmt->execute();
     echo "<br> Number of record added ".$count.", Student id :" .$my_conn->lastInsertId();
    }

    echo "<br>Multiple student records added successfully!";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

// Close the connection
$my_conn = null;

Number of record added 1, Student id : 90
Number of record added 1, Student id : 91
Number of record added 1, Student id : 92
Multiple student records added successfully!

Difference with AUTOINCREMENT & INTEGER PRIMARY KEY

Example

Explanation of AUTOINCREMENT in SQLite

• Step 1: Dropping the Table

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

  This code deletes the `student` table if it exists, removing all records.
• Step 2: Creating the Table with AUTOINCREMENT

  $count=$my_conn->prepare("CREATE TABLE IF NOT EXISTS
      student(id integer primary key AUTOINCREMENT, 
              name text, 
              class text, 
              mark integer, 
              gender text 
              )");
  $count->execute();

  The `id` column is defined as `INTEGER PRIMARY KEY AUTOINCREMENT`, ensuring that each new row is assigned a unique ID, even if previous rows have been deleted.
• Step 3: Inserting Records

  $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');");

  Two records are added with IDs 1 and 2.
• Step 4: Deleting a Record

  $id=2;
  $query="DELETE FROM student where id=:id";
  $stmt=$my_conn->prepare($query);
  $stmt->bindParam(':id', $id, PDO::PARAM_INT);
  $stmt->execute();

  The record with ID 2 is deleted.
• Step 5: Inserting a New Record After Deletion

  $stmt=$my_conn->prepare("INSERT INTO student (name,class,mark,gender) 
      values('ABC','Four',80,'Female')");
  $stmt->execute();
  echo "Record added ID: ". $my_conn->lastInsertId();

  • With AUTOINCREMENT: The new record will have an ID of 3 because AUTOINCREMENT ensures the ID is always higher than any previously used ID.
  • Without AUTOINCREMENT: SQLite might reuse the deleted ID, assigning ID 2 to the new record instead of 3.

try {
 $stmt=$my_conn->prepare("DROP TABLE  student");
if($stmt->execute()){
echo " 

Table deleted. ";
}
} catch (PDOException $e) {
   echo "Error: " . $e->getMessage();
}
/////////////////////
try {
  $count=$my_conn->prepare("CREATE TABLE IF NOT EXISTS
			student(id integer primary key AUTOINCREMENT, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text 
                      )");
					  
  if($count->execute()){
  echo "
 TABLE student created ";
  }else{
  echo " Not able to create student ";
  }	
///// Adding two records /////////// 
$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');");
echo "
 Number of records added : ".$count;

} catch (PDOException $e) {
   echo "Error: " . $e->getMessage();
}
/////// Delete 2nd record //////
$id=2;
$query="DELETE FROM student where id=:id";
$stmt=$my_conn->prepare($query);
$stmt->bindParam(':id',$id,PDO::PARAM_INT, 5);
if($stmt->execute()){
echo "
Successfully deleted   record with id=2 ";
echo "

Number of rows deleted  : ".$stmt->rowCount();
////////////Add one new reocrd //////////////
$stmt=$my_conn->prepare("INSERT INTO student (name,class,mark,gender) 
	values('ABC','Four',80,'Female')");
if($stmt->execute()){
	echo " 

Record added ID: ". $my_conn->lastInsertId();
}else{
	print_r($stmt->errorInfo()); 
}
//////////
}

Using our own id for the record

$stmt=$my_conn->prepare("INSERT INTO student (id,name,class,mark,gender) 
	values(2,'ABC','Four',80,'Female')");