PHP SQLite update records using parameters and returning number of records updated

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 WHERE condition with UPDATE query we will update records with new data.

Here we are updating the name and mark columns of the record with id = 3 .

How to update SQLite record by using Parameterized Queries with PDO in PHP : A6

// 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);
 
// Query to collect all columns of a particular record


////////////Collect form data/////////////
$name='plus2net'; // New name for student id=3
$mark=50;         // New mark for student id=3      
$id=3;            // id of row to be updated.  
///////// End of data collection /// 
$query="UPDATE student SET name=:name,mark=:mark WHERE id=:id";
$stmt=$my_conn->prepare($query);
$stmt->bindParam(':name',$name,PDO::PARAM_STR, 25);
$stmt->bindParam(':mark',$mark,PDO::PARAM_INT, 15);
$stmt->bindParam(':id',$id,PDO::PARAM_INT, 5);

if($stmt->execute()){
echo "Successfully updated record ";
}
else{
print_r($stmt->errorInfo()); // if any error is there it will be posted
echo " Database problem, please contact site admin ";
}

We can get number of rows or records updated by the query by using rowCount(). We can replace this part of the code to print the number of records updated.

if($stmt->execute()){
echo "Successfully updated record ";
echo "<br><br>Number of rows updated : ".$stmt->rowCount();
}

We will update mark of all students of class='Four' by increasing by 5.

Here is the code.

<?php 
// 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);
$number=5;
$class='Four';
$query="UPDATE student SET mark=mark+:number WHERE class=:class";
$stmt=$my_conn->prepare($query);
$stmt->bindParam(':class',$class,PDO::PARAM_STR, 5);
$stmt->bindParam(':number',$number,PDO::PARAM_INT, 10);

if($stmt->execute()){
echo "Successfully updated records ";
echo "

Number of rows updated : ".$stmt->rowCount();
}
else{
print_r($stmt->errorInfo()); // if any error is there it will be posted
echo " Database problem, please contact site admin ";
}

$my_conn = null;
?>

Output is here

Successfully updated records

Number of rows updated : 9

Handling multiple conditions:

We are modifying only the SQL query here. Since the data is directly embedded in the query (without user input or external sources), we are specifying the condition mark < 70 within the query itself.

$query="UPDATE student SET mark=mark+:number WHERE class=:class AND mark<70";

This query updates students' marks, but only for those who have less than 70, in the specified class.

Using try - catch block for error handling

$name='plus2net'; // New name for student id=3
$mark=50;         // New mark for student id=3      
$id=3;            // id of row to be updated.  
///////// End of data collection /// 
$number=5;
$class='Four';
$query="UPDATE student SET mark=mark+:number WHERE class=:class AND mark<70";
try{
$stmt=$my_conn->prepare($query);
$stmt->bindParam(':class',$class,PDO::PARAM_STR, 5);
$stmt->bindParam(':number',$number,PDO::PARAM_INT, 10);

if($stmt->execute()){
echo "Successfully updated records ";
echo "<br><br>Number of rows updated : ".$stmt->rowCount();
}
else{
print_r($stmt->errorInfo()); // if any error is there it will be posted
echo " Database problem, please contact site admin ";
} 
} // end of try block 

catch(PDOException $e) 
	{
		// Print PDOException message
		echo $e->getMessage();
	} 
	
$my_conn = null;

Podcast on SQLite database management using PHP PDO

Download sample script for SQLite with instructions on how to use.

SQLite Display records Delete records

PHP SQLite PDO Functions

Subscribe to our YouTube Channel here