May 16, 2021 SQLite
The SQLite3 extension is enabled by default from PHP 5.3.0. You can disable the SQLite3 extension at compile time using --without-sqlite3.
Windows users must enable php_sqlite3.dll to use the extension. As of PHP 5.3.0, this DLL is included in PHP's Windows distribution.
For detailed installation guidance, it is recommended to check out our PHP tutorial and its official website.
The following are important PHP programs to meet your needs for using SQLite databases in PHP programs. If you need more details, check out the OFFICIAL PHP documentation.
| Serial number | API & Description |
|---|---|
| 1 |
public void SQLite3::open ( filename, flags, encryption_key )
Open a SQLITE 3 database.If constructs include encryption, it will try the key. If the file name filename Assign ':memory:' So SQLITE3 :: Open () will create a memory database in the RAM, which will only continue within the effective time of the Session. If the file name filename is the actual device file name, then SQLITE3 :: Open () will use this parameter value to try to open the database file.If the name of the name does not exist, you will create a new database file that named the name. Optional Flags is used to determine if the SQLite database is opened.By default, open when SQLITE3_Open_ReadWrite | SQLITE3_Open_CREATE is used. |
| 2 |
public bool SQLite3::exec ( string $query )
This routine provides a shortcut to execute the SQL command, and the SQL command is provided by the SQL parameter, which can be composed of a plurality of SQL commands.The program is used to perform a result of a result of a result of a given database. |
| 3 |
public SQLite3Result SQLite3::query ( string $query )
This routine executes a SQL query, return one if the query return result is returned SQLite3Result Object. |
| 4 |
public int SQLite3::lastErrorCode ( void )
This routine returns the numerical result code for the last failed SQLite request. |
| 5 |
public string SQLite3::lastErrorMsg ( void )
This routine returns the English text description of the most recent failure SQLite request. |
| 6 |
public int SQLite3::changes ( void )
This routine returns the number of database rows that have been updated or inserted or deleted in the last SQL statement. |
| 7 |
public bool SQLite3::close ( void )
This routine is turned off before calling the database connection. |
| 8 |
public string SQLite3::escapeString ( string $value )
This routine returns a string, in the SQL statement, for security reasons, the string has been properly escaped. |
The following PHP code shows how to connect to an existing database. If the database does not exist, it is created and a database object is 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 program above and create our database test in the .db. Y ou can change the path as needed. If the database is successfully created, the message shown below is displayed:
Open database successfully
The following PHP snippets will be used to create a table in a 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 executes, it creates a COMPANY .db in the test table and displays the message shown 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 executes, it creates a given record in the COMPANY table and displays the following two lines:
Opened database successfully Records created successfully
The following PHP program shows how to get and display records from the COMPANY table you 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 procedure is executed, it produces 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 shows how to use the UPDATE statement to update any record, and then get and display the updated record 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 procedure is executed, it produces 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 shows how to use the DELETE statement to delete any records, and then get 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 procedure is executed, it produces 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