How to use sqlite database with xampp using php

Question

How to install sqlite or sqlite 3 database with xampp server and how to use sqlite or sqlite 3 database using php scripts or programs...

Answer 1

To activate SQLite3 in Xampp (v3.2.2). Open xampp/php/php.ini, un-comment the line ;extension=sqlite3 (retrieve the ;), save the php.ini and restart your Xampp server.

Answer 2

Creating the sqlite Database

You have two possibilities to achieve that:

1. Writing your DDL-statements (CREATE TABLE...) in a .sql file and execute it using the sqlite command line (assuming your CREATE TABLE statements are in a file called tabledef.sql):

   cat tabledef.sql | sqlite3 yourdbname.db
   

2. Use PHP to execute the DDL-statements on a connected database (see "Executing statements").

Connecting to a sqlite Database

You should definitely use PDO to do that:

$dbh = new PDO('sqlite:/path/to/your/database.db', '', '', array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
));

If the database does not exist, then it is created. But you need write-access to the directory that contains the database. If you permit write-access only to the database file, sqlite fails because it also needs to create a lock file in the same directory.

Executing statements

Now that you have a connection you can do your stuff with the database, for example execute some DDL-statements:

$dbh->exec("CREATE TABLE IF NOT EXISTS mytable (
    mypk INTEGER PRIMARY KEY AUTOINCREMENT,
    myvalue TEXT);");

Or if you need to dynamically generate SQL statements, use prepared statements:

$statement = $dbh->prepare("INSERT INTO mytable (myvalue) VALUES(?)");

$statement->execute(array("hello"));
$statement->execute(array("world"));

This is only a small overview, for further information you should check out the PDO manual and the sqlite Documentation.