PHP provides two SQLite extensions by default since version 5.0. The latest SQLite extension is known as sqlite3 extension that is included in PHP 5.3+. The sqlite3 extension provides an interface for accessing SQLite 3. The sqlite3 includes class interfaces to the SQL commands. In addition, it allows you to create SQL functions and aggregate using PHP. PHP introduced the PDO interfaces since version 5.1. The PDO is the latest PHP solution that provides a unified database access interface. Note that PDO is just an abstract layer that allows you to use a common library to access any databases. In the context of SQLite, it needs sqlite3 extension to access SQLite database. The PDO_SQLITE extension provides the PDO driver for the SQLite 3 library. It supports
standard PDO interfaces, and also custom methods for creating SQL functions and aggregates using PHP. In this section, we will walk you through the steps of using PDO to access SQLite databases. In this chapter, you will learn how to use SQLite in PHP programs. InstallationSQLite3 extension is enabled by default as of PHP 5.3.0. It's possible to disable it by using --without-sqlite3 at compile time. Windows users must enable php_sqlite3.dll in order to use this extension. This DLL is included with Windows distributions of PHP as of PHP 5.3.0. For detailed installation instructions, kindly check our PHP tutorial and its official website. PHP Interface APIsFollowing are important PHP routines which can suffice your requirement to work with SQLite database from your PHP program. If you are looking for a more sophisticated application, then you can look into PHP official documentation.
Connect to DatabaseFollowing PHP code shows how to connect to an existing database. If database does not exist, then it will be created and finally a database object will be returned. <?php class MyDB extends SQLite3 { function __construct() { $this->open('test.db'); } } $db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } ?> Now, let's run the above program to create our database test.db in the current directory. You can change your path as per your requirement. If the database is successfully created, then it will display the following message − Open database successfully Create a TableFollowing PHP program will be used to create a table in the previously created database. <?php class MyDB extends SQLite3 { function __construct() { $this->open('test.db'); } } $db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF CREATE TABLE COMPANY (ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL); EOF; $ret = $db->exec($sql); if(!$ret){ echo $db->lastErrorMsg(); } else { echo "Table created successfully\n"; } $db->close(); ?> When the above program is executed, it will create the COMPANY table in your test.db and it will display the following messages − Opened database successfully Table created successfully INSERT OperationFollowing PHP program shows how to create records in the COMPANY table created in the above example. <?php class MyDB extends SQLite3 { function __construct() { $this->open('test.db'); } } $db = new MyDB(); if(!$db){ echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Allen', 25, 'Texas', 15000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Teddy', 23, 'Norway', 20000.00 ); INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 ); EOF; $ret = $db->exec($sql); if(!$ret) { echo $db->lastErrorMsg(); } else { echo "Records created successfully\n"; } $db->close(); ?> When the above program is executed, it will create the given records in the COMPANY table and will display the following two lines. Opened database successfully Records created successfully SELECT OperationFollowing PHP program shows how to fetch and display records from the COMPANY table created in the above example − <?php class MyDB extends SQLite3 { function __construct() { $this->open('test.db'); } } $db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF SELECT * from COMPANY; EOF; $ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n"; echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n"; echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close(); ?> When the above program is executed, it will produce the following result. Opened database successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 20000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully UPDATE OperationFollowing PHP code shows how to use UPDATE statement to update any record and then fetch and display the updated records from the COMPANY table. <?php class MyDB extends SQLite3 { function __construct() { $this->open('test.db'); } } $db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF UPDATE COMPANY set SALARY = 25000.00 where ID=1; EOF; $ret = $db->exec($sql); if(!$ret) { echo $db->lastErrorMsg(); } else { echo $db->changes(), " Record updated successfully\n"; } $sql =<<<EOF SELECT * from COMPANY; EOF; $ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n"; echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n"; echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close(); ?> When the above program is executed, it will produce the following result. Opened database successfully 1 Record updated successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 2 NAME = Allen ADDRESS = Texas SALARY = 15000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully DELETE OperationFollowing PHP code shows how to use DELETE statement to delete any record and then fetch and display the remaining records from the COMPANY table. <?php class MyDB extends SQLite3 { function __construct() { $this->open('test.db'); } } $db = new MyDB(); if(!$db) { echo $db->lastErrorMsg(); } else { echo "Opened database successfully\n"; } $sql =<<<EOF DELETE from COMPANY where ID = 2; EOF; $ret = $db->exec($sql); if(!$ret){ echo $db->lastErrorMsg(); } else { echo $db->changes(), " Record deleted successfully\n"; } $sql =<<<EOF SELECT * from COMPANY; EOF; $ret = $db->query($sql); while($row = $ret->fetchArray(SQLITE3_ASSOC) ) { echo "ID = ". $row['ID'] . "\n"; echo "NAME = ". $row['NAME'] ."\n"; echo "ADDRESS = ". $row['ADDRESS'] ."\n"; echo "SALARY = ".$row['SALARY'] ."\n\n"; } echo "Operation done successfully\n"; $db->close(); ?> When the above program is executed, it will produce the following result. Opened database successfully 1 Record deleted successfully ID = 1 NAME = Paul ADDRESS = California SALARY = 25000 ID = 3 NAME = Teddy ADDRESS = Norway SALARY = 20000 ID = 4 NAME = Mark ADDRESS = Rich-Mond SALARY = 65000 Operation done successfully How can I use SQLite in PHP?In this chapter, you will learn how to use SQLite in PHP programs.. Installation. SQLite3 extension is enabled by default as of PHP 5.3. ... . PHP Interface APIs. ... . Connect to Database. ... . Create a Table. ... . INSERT Operation. ... . SELECT Operation. ... . UPDATE Operation. ... . DELETE Operation.. How do I connect to sqlite3?Connecting to SQLite. Open the connections page in preferences, see managing connections for more information.. Click the Add new Connection button at the top of the connections page.. Select SQLite from the list.. Give a Connection name for your own internal reference.. How do I run a SQLite file?If you are using Linux or a Mac, open a terminal window instead a command prompt. Open a command prompt (cmd.exe) and 'cd' to the folder location of the SQL_SAFI. sqlite database file. run the command 'sqlite3' This should open the SQLite shell and present a screen similar to that below.
Is there a GUI for SQLite?The SQLiteStudio tool is a free GUI tool for managing SQLite databases. It is free, portable, intuitive, and cross-platform. SQLite tool also provides some of the most important features to work with SQLite databases such as importing, exporting data in various formats including CSV, XML, and JSON.
|