SQLite basic update and PHP PDO

Posted under » PHP on 24 April 2015

MySQL can be an overkill when all you want is a simple table and not a relational database. SQLite does more than that. It runs on all, I mean all platform and is particularly found in mobile applications.

On the desktop there are free tools you can use. I recommend the cross platform DB Browser for SQLite. Alternatively in Ubuntu, you can use SQLiteman.

For PHP, you must have the PHP library installed first.

Basic Queries with PDO

<¿php 
try {
$dbh = new PDO("sqlite:main.db");
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
$dbh->beginTransaction();
$sql = 'select * from umah limit 10';
foreach ($dbh->query($sql) as $row) {
print $row['tetel'] . " - ";
print $row['date'] . "<br>";
}
$dbh->rollback();
?>

PDO is made up of two basic classes, PDO and PDOStatement. PDO represents a connection, which internally contains a sqlite3 structure, and PDOStatement represents a statement handle, which internally contains a sqlite3_stmt structure. The query methods in the PDO class closely follow the methods in the SQLite C API. There are three ways to execute queries:

• exec(): Executes queries that don't return any data. Returns the number of affected rows, or FALSE if there is an error. This mirrors sqlite3_exec().
• query(): Executes a query and returns a PDOStatement object representing the result set, or FALSE if there is an error.
• prepare(): Compiles a query and returns a PDOStatement object or FALSE if there is an error. This offers better performance than query() for statements that need to be executed multiple times, because it can be reset, avoiding the need to recompile the query.

Additionally, transaction management in PDO can be performed through a method invocation using beginTransaction(), commit(), and rollback(). i

Basic update with PDO

<¿php 
// Create (connect to) SQLite database in file
  $my_conn = new PDO('sqlite:chinook.db');
// Set errormode to exceptions
  $my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
 
////////////Collect form data/////////////
$co='Mirage Multimedia'; 
$id= 6 ;

///////// End of data collection /// 
$query="UPDATE customers SET Company=:co WHERE CustomerId=:id";
$sql=$my_conn->prepare($query);
$sql->bindParam(':co',$co);
$sql->bindParam(':id',$id);

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

Sometimes you get the "attempt to write a readonly database" error. If you receive this error while trying to write to a sqlite database (update, delete, drop): Warning: PDO::query() [function.query]: SQLSTATE[HY000]: General error: 1 unable to open database The solution is to make the folder that houses the database file be writeable.

Please read related article for using SQLite with SQLite3 class.