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 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 the path as needed. If the database is created successfully, a message like the following is displayed:
Open database successfullyCreate 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 executes, it creates the COMPANY table in test.db and displays the message shown below:
Opened database successfully
Table created successfullyINSERT 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 creates the given record in the COMPANY table and displays the following two lines:
Opened database successfully
Records created successfullySELECT operation
The following PHP program shows how to get 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 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 successfullyUPDATE operation
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 program 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 successfullyDELETE operation
The following PHP code shows how to use the DELETE statement to delete any record 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 program 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
