PHP opening an SQLITE database.

SQLite3 is a popular database and is a good place to learn about SQL. It uses a local file.

This opens the database,

 W:\>sqlite3 ./db/mydb.sq3 
 

These run commands

 sqlite3 ./db/mydb.sq3 ".help" 
 sqlite3 ./db/mydb.sq3 "select rowid,* from gb "
 W:\>sqlite3 ./db/mydb.sq3 "select 'hello world' " "select datetime() "
 W:\>sqlite3 ./db/mydb.sq3 "select datetime() "
 

Within a .php page, a system command can be used, if allowed.

 <?php
   system( './sqlite3 ./db/mydb.sq3 ".output ./db/backup3.sql" ".dump" ');
 ?>
 	
 

SQLITE database - SQL crib

SQL create table and insert syntax

 BEGIN TRANSACTION;
 
 CREATE TABLE GB(  
 	name 	   TEXT ,
 	email 	   TEXT ,
 	postedOn   TEXT ,
 	IPaddress  TEXT ,
 	userfield1 TEXT ,
 	userfield2 TEXT ,
 	userfield3 TEXT ,
 	userfield4 TEXT ,
 	comments   TEXT
 );
 
 INSERT INTO "gb" VALUES( 'name',
 'email',
 'Sat, 27 Jan 2007 09:05:42 UTC+0100',
 '212.56.108.219 | dougrice.plus.com ',
 'userfield1',
 '5',
 'userfield3',
 'userfield4',
 'test' );
 
 COMMIT;
 

SQL insert , update, delete syntax

 
   INSERT INTO table 
   VALUES ;	
 
   -- use update to change values.
   UPDATE table SET
   name  ='value' , name2 ='value'
   WHERE rowid = last_insert_rowid();
   
   DELETE FROM table WHERE rowid = 1 ;
   
 

SQLITE CMD DELETE

 sqlite3 ./db/mydb.sq3 "delete from gb where rowid > 15 and rowid < 20" "select rowid,* from gb "
 

-- Example of  PHP opening an SQLITE database.

 <?php
     if ($db = sqlite3_open( './sqlDB' ) ) { 
 
         printf( "opi:".$db."\n" );
         $result = sqlite3_query($db, 'select * from GB ');
 
         // var_dump() serializes the variables and is useful debug
 
         while (  $row=sqlite3_fetch_array( $result )  ) { 
             printf(" ============ \n" );
             var_dump( $row ); 
         } 
     } else {
         printf( "err:".$db." ".$sqliteerror."\n" );
         die($sqliteerror);
     }
 ?>
 

-- another Example of  PHP opening an SQLITE database.

 <?php
 
 // https://www.sqlitetutorial.net/sqlite-php/connect/ 
 // this should be better protected.
 try {  
   $conn = new PDO(
     'sqlite:.\db\mydb.sq3',
     null,
     null,
     null
   );
 
   // set the PDO error mode to exception
   //$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   } catch(PDOException $e) {
     echo "Connection failed: " . $e->getMessage()."<BR>";
   }
   echo "Connected successfully<BR>";
 
 echo "<PRE>";
 ?>
 
 

Now we are connected , do some SQL

 <?php
 
 
   $sql = "SELECT 'A world full of HOPE - connected to database\n' AS _msg  ";
 
   $sth = $conn->query( $sql );
   
   // fetch all rows into array, by default PDO::FETCH_BOTH is used
   $rows = $sth->fetchAll();
   
   foreach($rows as $row) {
     //printf( "$row[0] $row[1] $row[2] <BR>\n");
     printf( " $row[0] <BR>\n" );
   }
   
   echo "<HR>";
 
 
 

-- Examples of  javascript Guestbook using a SQLITE database.

 -- sqlite3 gb.db ".dump" > gb.sql
 

 -- sqlite3 gb.db ".read gb.sql"
 

 BEGIN TRANSACTION;
 
 CREATE TABLE GB(  
 	name 	   TEXT ,
 	email 	   TEXT ,
 	postedOn   TEXT ,
 	IPaddress  TEXT ,
 	userfield1 TEXT ,
 	userfield2 TEXT ,
 	userfield3 TEXT ,
 	userfield4 TEXT ,
 	comments   TEXT
 );
 

 COMMIT;