English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
Since PHP 5.30 onwards, SQLite is enabled by default3 extension. It can be used when compiling. --without-sqlite3 Disable SQLite3 extension.
Windows users must enable php_sqlite3.dll is required to use this extension. Since PHP 5.3.0 onwards, this DLL is included in the PHP Windows distribution.
For detailed installation instructions, it is recommended to view our PHP tutorial and its official website.
The following important PHP programs can meet your needs for using the SQLite database in PHP programs. For more details, please refer to the PHP official documentation.
| Serial Number | API & Description |
|---|---|
| 1 | public void SQLite3::open ( filename, flags, encryption_key ) open an SQLite 3 database. If the build includes encryption, it will try to use the key. If the filename filename is assigned to ':memory:'then SQLite3::open() will create a memory database in RAM, which will only last during the session's validity period. If the filename filename is the actual device file name, then SQLite3::open() will attempt to open the database file using this parameter value. If a file with this name does not exist, a new database file named with this name will be created. Optional flags used to determine whether to open the SQLite database. By default, when using SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE when opened. |
| 2 | public bool SQLite3::exec ( string $query ) This routine provides a shortcut for executing SQL commands, where the SQL commands are provided by the sql parameter and can consist of multiple SQL commands. This program is used to execute a query that does not return results on the given database. |
| 3 | public SQLite3Result SQLite3::query ( string $query ) This routine executes an SQL query and returns a SQLite3Result Object. |
| 4 | public int SQLite3::lastErrorCode ( void ) This routine returns the numeric result code of the last failed SQLite request. |
| 5 | public string SQLite3::lastErrorMsg ( void ) This routine returns the English text description of the last failed SQLite request. |
| 6 | public int SQLite3::changes ( void ) This routine returns the number of database rows updated or inserted or deleted by the last SQL statement. |
| 7 | public bool SQLite3::close ( void ) This routine closes the previously called SQLite3::open() opens the database connection. |
| 8 | public string SQLite3::escapeString ( string $value ) This routine returns a string, which has been properly escaped in the SQL statement for security reasons. |
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 directory test.db. You can change the path as needed. If the database is successfully created, the following message will be displayed:
Open database successfully
The following PHP code segment is 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 a table in the following location: test.db The following message is displayed when the COMPANY table is created in the database below:
Opened database successfully Table created successfully
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 record in the COMPANY table and display the following two lines:
Opened database successfully Records created successfully
The following PHP program demonstrates how to retrieve and display records from the previously created 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
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 results:
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
The following PHP code demonstrates how to use the UPDATE statement to update any record and then retrieve 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 results:
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
The following PHP code demonstrates how to use the DELETE statement to delete any record and then retrieve 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 results:
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