Introduction

The FlatSQL package is a small but very powerful set of PHP classes for database management but stores information into "flat files" rather like MySQLite. It provides equivalents to many of the common SQL commands.

This package was created and developed by Anthony Ogundipe, CEO of Africoders.

Features

This package does not require MySQL or any database engine or php plugin for that matter to run, it is entirely standalone and requires just PHP to run.

Setup

Now we need to create the database object and set it up. In this example we are using a database called CMS. The database is created automatically if it does not exist.

require_once('ffdb/dbase.php'); $db = new Flatsql(); $db->query("use dbase cms");

INSERT Statement

To insert a row, use the following sql statements. An autoincrement ID is generated by default. And oh, there is no create table function, table is created automatically if it does not already exist. The return value is the newly generated ID. Notice that the first field called uid is the auto-generated I.D - this is required be default.

$db->query("insert into users(uid,first,last,city,phone) value(Null,'Anthony','Ogundipe','Lagos','08027034534')"); echo $db->newId; //latest id

SELECT Statement

If you are familiar with MySQL or sql in general, then the commands below will not be new to you. These commands are to select or retrieve data that are already existing inside a table.

$row=$db->query("select * from users"); //select all the data available
$row=$db->query("select first,last from users"); //select first and lastname only

$row=$db->query("select last,phone,first from users"); 

$row=$db->query("select first,last from users where city='Lagos' order by uid asc INTEGER_COMPARISON"); //ordering data 

$row=$db->query("select first,last from users where city='Lagos' order by first asc limit 1"); //limiting data returned 

$row=$db->query("select first,last from users where city='Egbe'"); //where clause

$row=$db->query("select first,last from users where uid='2'"); 

$row=$db->query("select first,last from users where uid>'2' and first='Okon'"); //multiple conditions

$row=$db->query("select first,last from users where city='Lagos' and phone='070603223456'"); 

$row=$db->query("select first,last from users where city='Lagos' and phone='08027034534' "); 

$row=$db->query("select first,last from users where city='Lagos' order by phone asc"); 

$row=$db->query("select first,last from users where city='Lagos' and phone='08027034534' order by phone asc");

The result will be an array of arrays. You can loop through the associative arrays to get the individual data.

UPDATE Statement

To update records stored in tables, you can use regular MySQL statements.

$db->query("update users set last='White',city='Broad Street' where uid<'3'");
$db->query("update users set last='White' "); //this will update the entire column

DELETE RECORDS

MySQL delete statements are useful here also.

$db->query("delete from users where uid='1'"); //delete conditionally
$db->query("delete from users"); //delete entire rows - truncate

DELETE TABLE

To delete an entire table and all the data it contains.

$db->query("drop table users");

TRUNCATE TABLE

To delete all the records stored inside a table.

$db->query("truncate table users");

COMBINING WITH FLAT FILE PACKAGE

You can use flat file commands concurrently as shown below. However, flatfile discussion is outside the scope of this tutorial.

$db = new Flatsql(); 
$db->query("use dbase cms"); 
$aSingleRow = $db->selectUnique('cms.txt', 0, '2'); 
var_dump($aSingleRow);

Note

This package is a wrapper for the Flatfile Class created by Luke Plant. In fact, the wrapper extends the flat file package and provides all the functionalities of the flat file package which are not mentioned here since this is outside of the scope of this tutorial.

Quick Start

You can run the 3 examples in the examples folder to give you a quick boost of how this package works.

|-- examples |-- test1.php |-- test2.php |-- test3.php |-- ffdb |-- datadir

Documents

For more information on how to use this flat sql package see:

• Visit our wiki for documentation.
• FlatSQL Forum for issues.

You can chat with us on facebook http://facebook.com/dhtml5

License

flatsql's code in this repo uses the MIT license, see our LICENSE file.