Friday, June 25, 2010

How to connect SQLite in PHP

Introduction

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions.

This allows developers to create code which is portable across many databases and platforms. and this Focus on data abstraction rather than database abstraction


LIst of Databases supported by PDO


* MS SQL Server (PDO)

* Firebird/Interbase (PDO)

* IBM (PDO)

* Informix (PDO)

* MySQL (PDO)

* Oracle (PDO)

* ODBC and DB2 (PDO)

* PostgreSQL (PDO)

* SQLite (PDO)

* 4D (PDO)


Here I am giving the setps to connect SQLite database in php before you start setting up u need to enable the extension=php_pdo_sqlite.dll and
extension=php_sqlite.dll (in case php 5 >) or php_pdo.dll then restart your apache server


Step1 : connect to the SQLite databse using PDO. By using PDO the database creation becomes even easier. Simply specify the path to the database file and it will be loaded. If the database file does not exist, PDO will attempt to create it.

<?php
try {
         // connect to SQLite from PDO database
         $dbh = new PDO("sqlite:/path/to/database.db");

}
catch(PDOException $e)
{
         echo $e->getMessage();//this getMessage throws an exception if any
     
}
?>

Step2 :now run the query by using the database object created
$result = $dbh->query($query);
while($row = $result->fetch())
{
         print("$row["fieldname"]");
}

Sample code
Here i am Creating database name "database" and the table TestTable.

<?php
try {

         // connect to SQLite from PDO database

         $dbh = new PDO("sqlite:database.db");

}
catch(PDOException $e)

{
          echo $e->getMessage();        
}
    $query = 'CREATE TABLE TestTable ' .
     '(Title TEXT, Name TEXT, Year INTEGER)';

    $result = $dbh->query($query);

if(!$dbh->query($query))
{
       echo "table not created ";

}

$query =
             'INSERT INTO TestTable (Title, Name, Year) ' .
            'VALUES ("Mr", "Rakesh", 2010); ' .

              'INSERT INTO TestTable (Title, Name, Year) ' .
             'VALUES ("Mr", "Ram", 2008)' ;
if(!$dbh->query($query))
{
            echo "row not inserted ";
}
$query = "SELECT * FROM TestTable";

if($result = $dbh->query($query))

{

      while($row = $result->fetch())
     {
             print("Title: {$row['Title']}  " .
             "Name: {$row['Name']}".
             "Year: {$row['Year']}");
      }
}
else
{
          echo "not selected ";
}

No comments: