SQLite – PHP

SQLite is  a serverless, zero-configuration SQL database engine.  It is not more than 512KB.  This article addresses, connecting SQLite with PHP.

I downloaded the command line tool for windows. It’s very simple database engine, to use. The database files have extension “.db”. These can be moved between platforms without any changes. You don’t need a command line tool to work on PHP. PHP has got inbuilt classes to deal with SQLite. But command line tool will be useful.

If you have to open a database using command line tool, navigate to sqlite3.exe from your command prompt. and issue sqlite3.exe <database name>

c:\sqlite\sqlite3.exe test.db

You will get a shell like, sqlite> and a blinking cursor waiting for commands.

Database name is “test.db”.

This article addresses, connecting SQLite with PHP. We can use either SQLite3 class or PDO class, PDOStatement class, PDOException class . PDO – PHP Data Object.

I am using PDO class.  Lets assume a database test.db. You can create a database with the command line tool or it will create on it’s own.

Opening/Creating a  Database:           $dh=new PDO(“sqlite:test.db”);

Creating a table:

$query=$dh->prepare(“create table tb1(id int, name text)”);
$query->execute();

It creates a table tb1

Inserting data into tables: 

$query=$dh->prepare(“insert into tb1(‘id’,’name’) values(123,’goutham’)”);
$query->execute();

———————————————————————————————————

Now open sqlite command line tool and issue: .tables. It will show only one table – tb1.

Note*: There is a period(.) in “.tables”

Now issue: select * from tb1;

it will return  “123 | goutham”. But where are the column names. To on them, issue .header on. Now if you give the previous select command it will display with column names. But there are no separations. So to get separations between column names and the values you have to issue: .mode column. issue the select statement again, now you will find the separations.

Note*: There is a period(.) in “.header on”, “.mode column”

Code:

<!--?php $dh=new PDO("sqlite:test1.db"); $query=$dh--->prepare("create table tb1(id int, name text)");
$query->execute();
$query=$dh->prepare("insert into tb1('id','name') values(123,'goutham')");
$query->execute();
?>

References:

http://www.php.net/manual/en/book.pdo.php

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s