Here we are updating the name and mark columns of the record with id = 3 .
// 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";
$sql=$my_conn->prepare($sql);
$sql->bindParam(':name',$name,PDO::PARAM_STR, 25);
$sql->bindParam(':mark',$mark,PDO::PARAM_INT, 15);
$sql->bindParam(':id',$id,PDO::PARAM_INT, 5);
if($sql->execute()){
echo "Successfully updated record ";
}
else{
print_r($sql->errorInfo()); // if any error is there it will be posted
$msg=" 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($sql->execute()){
echo "Successfully updated record ";
echo "<br><br>Number of rows updated : ".$sql->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";
$sql=$my_conn->prepare($query);
$sql->bindParam(':class',$class,PDO::PARAM_STR, 5);
$sql->bindParam(':number',$number,PDO::PARAM_INT, 10);
if($sql->execute()){
echo "Successfully updated records ";
echo "<br><br>Number of rows updated : ".$sql->rowCount();
}
else{
print_r($sql->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