SQLite - PHP
Installation
Since PHP 5.3.0, the SQLite3 extension is enabled by default. You can use--without-sqlite3disable the SQLite3 extension.
Windows users must enable php_sqlite3.dll to use this extension. Since PHP 5.3.0, this DLL is included in the Windows distribution of PHP.
For detailed installation instructions, it is recommended to view our PHP tutorial and its official website.
PHP Interface API
The following are important PHP programs that can meet your needs for using SQLite databases in PHP programs. If you need more details, please refer to the official PHP documentation.
| No. | API & Description |
|---|---|
| 1 | public void SQLite3::open ( filename, flags, encryption_key ) Opens a SQLite 3 database. If the build includes encryption, it will try to use the key. If the filenamefilenameis set to':memory:', then SQLite3::open() will create an in-memory database in RAM, which only lasts for the duration of the session. If the filename filename is an actual device file name, then SQLite3::open() will use this parameter value to try to open the database file. If a file with that name does not exist, a new database file with that name will be created. The optional flags are used to determine whether to open the SQLite database. By default, it opens when SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE is used. |
| 2 | public bool SQLite3::exec ( string $query ) This routine provides a shortcut to execute SQL commands, which are provided by the sql parameter and can consist of multiple SQL commands. This program is used to execute a result-less query on a given database. |
| 3 | public SQLite3Result SQLite3::query ( string $query ) This routine executes an SQL query and returns anSQLite3Resultobject. |
| 4 | public int SQLite3::lastErrorCode ( void ) This routine returns the numeric result code of the most recent failed SQLite request. |
| 5 | public string SQLite3::lastErrorMsg ( void ) This routine returns the English text description of the most recent failed SQLite request. |
| 6 | public int SQLite3::changes ( void ) This routine returns the number of database rows updated, inserted, or deleted by the most recent SQL statement. |
| 7 | public bool SQLite3::close ( void ) This routine closes the database connection previously opened by calling SQLite3::open(). |
| 8 | public string SQLite3::escapeString ( string $value ) This routine returns a string that has been properly escaped for use in an SQL statement for security reasons. |
Connect to Database
The following PHP code shows how to connect to an existing database. If the database does not exist, 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 in the current directorytest.db. You can change the path as needed. If the database is created successfully, the message shown below will be displayed:
Open database successfully
Create Table
The following PHP code snippet 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 willtest.dbcreate the COMPANY table in it, and display the following message:
Opened database successfully Table created successfully
INSERT Operation
The following PHP program shows how to create records in the COMPANY table created above:
<?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 display the following two lines:
Opened database successfully Records created successfully
SELECT Operation
The following PHP program shows how to fetch and display records from the COMPANY table created earlier:
<?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 Operation
The following PHP code shows how to use the 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 Operation
The following PHP code shows how to use the 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 successfullyOther Extensions