PHP Operation SQLite

Connecting to a 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.

<?PHPclassMyDBextendsSQLite3 {function__construct () {$this->open (' test.db '); }   }   $db=NewMyDB (); if(!$db){      Echo $db-lasterrormsg (); } Else {      Echo"Opened Database successfully\n"; }?>

Now, let's run the above program to create our database test.dbin the current directory. You can change the path as needed. If the database was created successfully, the message shown below is displayed:

Open Database successfully

Create a table

The following PHP code snippet will be used to create a table in the previously created database:

<?PHPclassMyDBextendsSQLite3 {function__construct () {$this->open (' test.db '); }   }   $db=NewMyDB (); if(!$db){      Echo $db-lasterrormsg (); } Else {      Echo"Opened Database successfully\n"; }   $sql=<<<EOF CREATE TABLE Company (ID INT PRIMARYKEYNotNULL,NAME TEXT notNULL,Age INT notNULL,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 Successfullytable created successfully

INSERT operation

The following PHP program shows how to create a record in the company table created above:

<?PHPclassMyDBextendsSQLite3 {function__construct () {$this->open (' test.db '); }   }   $db=NewMyDB (); if(!$db){      Echo $db-lasterrormsg (); } Else {      Echo"Opened Database successfully\n"; }   $sql=<<<EOF INSERT into company (ID, Name,age,address,SALARY) VALUES (1, ' Paul ', +, ' California ', 20000.00 ); INSERT into company (ID, Name,age,address,SALARY) VALUES (2, ' Allen ', +, ' Texas ', 15000.00 ); INSERT into company (ID, Name,age,address,SALARY) VALUES (3, ' Teddy ', at 20000.00, ' Norway ', ); INSERT into company (ID, Name,age,address,SALARY) VALUES (4, ' Mark ', +, ' 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 Successfullyrecords created successfully

SELECT operation

The following PHP program shows how to get and display records from the company table created earlier:

<?PHPclassMyDBextendsSQLite3 {function__construct () {$this->open (' test.db '); }   }   $db=NewMyDB (); 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 "; }   Echo"Operation Done Successfully\n"; $db-close ();?>

When the above program executes, it produces the following results:

= 1= = =  20000= 2= = =  15000= 3  == = 20000 = 4 = = rich-=    65000 done successfully

UPDATE 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:

<?PHPclassMyDBextendsSQLite3 {function__construct () {$this->open (' test.db '); }   }   $db=NewMyDB (); 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 "; }   Echo"Operation Done Successfully\n"; $db-close ();?>

When the above program executes, it produces the following results:

 opened database successfully  1 Record Updated Successfullyid  = 1name  = = californiasalary  = 25000id  = 2name  = allenaddress  = Texassalary  = 15000id  = 3name  =< Span style= "color: #000000;" > teddyaddress  = norwaysalary  = 20000id 
    = 4name  = markaddress  = Rich-mondsalary  = 65000operation  done  successfully 

DELETE operation

The following PHP code shows how to delete any records using the DELETE statement, and then gets and displays the remaining records from the company table:

<?PHPclassMyDBextendsSQLite3 {function__construct () {$this->open (' test.db '); }   }   $db=NewMyDB (); if(!$db){      Echo $db-lasterrormsg (); } Else {      Echo"Opened Database successfully\n"; }   $sql=<<<EOF DELETE from the 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 "; }   Echo"Operation Done Successfully\n"; $db-close ();?>

When the above program executes, it produces the following results:

opened database successfully1= 1= = =  25000= 3=  ==  20000= 4= = rich-=  65000   Done successfully

PHP Operation SQLite