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;