In this part, you will figure out how to utilize SQLite in PHP programs.
Installation
SQLite3 augmentation is empowered of course as of PHP 5.3.0. It's conceivable to debilitate it by utilizing - without-sqlite3 at order time.
Windows clients should empower php_sqlite3.dll to utilize this expansion. This DLL is incorporated with Windows conveyances of PHP as of PHP 5.3.0.
For definite establishment directions, compassionately check our PHP instructional exercise and its authority site.
PHP Interface APIs
Following are significant PHP schedules which can get the job done your prerequisite to work with SQLite data set from your PHP program. On the off chance that you are searching for a more refined application, at that point you can investigate PHP official documentation.
Sr.No. | API & Description |
---|---|
1 |
public void SQLite3::open ( filename, flags, encryption_key ) Opens SQLite 3 Database. If the build includes encryption, then it will attempt to use the key. If the filename is given as ':memory:', SQLite3::open() will create an in-memory database in RAM that lasts only for the duration of the session. If the filename is actual device file name, SQLite3::open() attempts to open the database file by using its value. If no file by that name exists, then a new database file by that name gets created. Optional flags used to determine how to open the SQLite database. By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE. |
2 |
public bool SQLite3::exec ( string $query ) This routine provides a quick, easy way to execute SQL commands provided by sql argument, which can consist of more than one SQL command. This routine is used to execute a result-less query against a given database. |
3 |
public SQLite3Result SQLite3::query ( string $query ) This routine executes an SQL query, returning an SQLite3Result object if the query returns results. |
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 English text describing the most recent failed SQLite request. |
6 |
public int SQLite3::changes ( void ) This routine returns the number of database rows that were updated, inserted, or deleted by the most recent SQL statement. |
7 |
public bool SQLite3::close ( void ) This routine closes a database connection previously opened by a call to SQLite3::open(). |
8 |
public string SQLite3::escapeString ( string $value ) This routine returns a string that has been properly escaped for safe inclusion in an SQL statement. |
Associate with Database
Following PHP code tells the best way to associate with a current information base. On the off chance that data set doesn't exist, at that point it will be made lastly a data set item 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";
}
?>
Presently, how about we run the above program to make our information base test.db in the current registry. You can change your way according to your necessity. In the event that the information base is effectively made, at that point it will show the accompanying message −
Open database successfully
Create a Table
Following PHP program will be utilized to make a table in the recently made data set.
<?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();
?>
At the point when the above program is executed, it will make the COMPANY table in your test.db and it will show the accompanying messages −
Opened database successfully
Table created successfully
INSERT Operation
Following PHP program tells the best way to make records in the COMPANY table made in the above model.
<?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();
?>
At the point when the above program is executed, it will make the given records in the COMPANY table and will show the accompanying two lines.
Opened database successfully
Records created successfully
SELECT Operation
Following PHP program tells the best way to bring and show records from the COMPANY table made in the above model −
<?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();
?>
At the point when the above program is executed, it will create the accompanying outcome.
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
Following PHP code tells the best way to utilize UPDATE proclamation to refresh any record and afterward bring and show the refreshed 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();
?>
At the point when the above program is executed, it will deliver the accompanying outcome.
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
Following PHP code tells the best way to utilize DELETE explanation to erase any record and afterward bring and show the leftover 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();
?>
At the point when the above program is executed, it will deliver the accompanying outcome.
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