Tuesday, 28 June 2016

PHP sqlite3 class example

For some reason I like to run php on the command line in Win 10. To do this I use git bash (MINGW64) as my command line interface, you can also use cmd and powershell, but I like git.

I often use file_put_contents to write files for config users etc especially early on in the design of a project as this allows me to scaffold a project (ie: you need some users to build and test the users functions).

Having learn't of sqlite I wanted to get it to work on my rig and setup.

After some experimentation and research I build the following script which does the job on php 7.x running on win 10, using git bash cl.

Of course this only an example script which acts as notes to build something more practical, but it contains everything to get a simple db, table and some data in and out.

/** * Test script for implementing sqlite3. * I run this file on win 10 git bash command line using php v7.0. */ // I just like to know the script fired. print "running.\n"; // ----- Main program // Instansiate the sqlite3 class. $db = new myDb(); // Create a new database or open an existing one. openDb('test'); // Call exec method passing it create table sql. $db->execDb($db->createTableSql()); // Call either exec or query methods on an insert sql. // Note: either comment this out or change the name in getInsertSql() method // otherwise you will get Unique constraint (set on name in users) error. //$db->execDb($db->getInsertSql()); $db->queryDb($db->getInsertSql()); // Call query to retrieve all the data in the table. $result = $db->queryDb($db->getSelectSql()) or die('select failed.'); // pump out the results of the select query. while ($row = $result->fetchArray(SQLITE3_ASSOC)) { print_r($row); } // ----- End main /** * Running php on git bash cmmand using the class extends is the only way I have * found to get it to work. * The folowing class demonstraites the basic implementation of this technique. */ class myDb extends sqlite3 { // Not used in this example, but you might need it for something. public $dbHandle; function __construct () { } /** * Creates or opens the db file. * @param string $dbName This is the path and file name. */ function openDb ($dbName) { $this->dbHandle = $this->open($dbName); } /** * Calls the sqlite exec method on any sql sent to it. * @param string $sql * @return boolean true = success */ function execDb ($sql) { // exec does not return result sets. return $this->exec($sql); } /** * Calls the query method on any sql sent. Returns a result set if there is one. * @param string $sql * @return resultSet */ function queryDb ($sql) { // query returns result sets. return $this->query($sql); } // ----- The sql statements used in this example. function createTableSql () { return "CREATE TABLE IF NOT EXISTS users ( username STRING PRIMARY KEY, password STRING); "; } function getInsertSql () { return "INSERT INTO users VALUES ( 'brian', 'secret' );"; } function getSelectSql () { return "SELECT * FROM users;"; } }

No comments:

Post a Comment