Flat File Relational Database Example 1

For our first example, we are going to create a personal account management program.

The program will be coded in PHP and the database will consist of 2 arrays: a one-dimensional array for 'categories' and a two-dimentsional array for 'accounts'.

The 'categories' array is delimited by commas, while the two-dimensional array uses 3-character delimiters. The 'categories' array is stored as a text file and the 'accounts' array is stored as multiple text files within an 'accounts' folder.

The 'database-definitions' file defines the delimiters and the structure of the two-dimensional 'accounts' arrays.

ACCOUNTS/inc/database-definitions.php ▾

This folder holds the 'account' records:
ACCOUNTS/data/accounts

americat-express-platinum.txt ▾

big-stretch-gym.txt ▾

catflix.txt ▾

catstack.txt ▾

chasetail-national-bank.txt ▾

chasetail-spendit-card.txt ▾

doubledebt-credit-card.txt ▾

fastdriver-car-insurance.txt ▾

felinia-gas-and-electric-co.txt ▾

felinia-internet.txt ▾

felinia-savings-and-loan.txt ▾

felinia-water-dept.txt ▾

furniturescratchers-forum.txt ▾

furrbank.txt ▾

meetamate-services.txt ▾

meowmore-cellular.txt ▾

misskittycatmailcom.txt ▾

misskittymeowmailcom.txt ▾

mousetv.txt ▾

spendazon.txt ▾

the-nip-shoppe.txt ▾

wealthy-cats.txt ▾

xxx.txt ▾

This file contains a list of 'categories':

ACCOUNTS/data/categories.txt ▾

Section 2: Relationships between records

Relationships between tables are established by 'maps' - two-dimensional arrays containing pairs of values. The 'category-account' map:

ACCOUNTS/data/category-account-map.txt ▾

Section 3: Accessing Records and Maps

The 'functions' file is the database management system. This file contains code for reading and writing the records and arrays, and querying the database.

ACCOUNTS/inc/functions.php ▾

<?php

function  createRecordId ($name) {
    $newname = "";
    if ($name !== "") {       
        $newname = trim ($name);
        $newname = str_replace (" " , "-", $newname);            
        $newname = strtolower ($newname);
        $newname = preg_replace('/[^A-Za-z0-9-]/', '', $newname); 
        $newname = preg_replace('/-+/', '-', $newname);        
    }    
    return $newname;        
}

function initializeRecord ($keys){  
   
    $record = array ();
    foreach ($keys as $Id => $key) {
        $record[$key] = ""; 
    }  
    return ($record);
}

function readDatabaseRecord ($keys,  $filename) {
  
    global $dl1;
    $string = '';
    if (file_exists($filename)) {  
        $string = file_get_contents ($filename);    
    }

    $record = array();          
    $fArray1= explode ($dl1, $string);
    foreach ($keys as $Id => $key) {  
        $record[$key] = "";           
        if (array_key_exists ($Id, $fArray1)) {
            $record [$key] = $fArray1[$Id];         
        }          
    }  
    return $record;    
}


function writeDatabaseRecord ($record,  $filename) { 
  
    global $dl1;    
    $String = implode ($dl1, $record);     
   // file_put_contents ($filename, $String);      
}


// Maps for table relationships
function addMapEntry ($map, $key1, $key2) {    
    global $dl1,  $dl2;          
    
    $fArray1 = readArray ($map, $dl1);    
    $newentry = $key1 . $dl2 . $key2 . $dl2 . "\n";
    array_push ($fArray1, $newentry);
    $fArray1 =  array_unique ($fArray1);  
    writeArray ($map, $fArray1, $dl1);
}
function addMapEntryReverse ($map, $key1, $key2) {    
    global $dl1,  $dl2;          
    
    $fArray1 = readArray ($map, $dl1);    
    $newentry = $key1 . $dl2 . $key2 . $dl2 . "\n";
    array_push ($fArray1, $newentry);
    $fArray1 =  array_unique ($fArray1);       
    rsort ($fArray1);
    writeArray ($map, $fArray1, $dl1);
}

function removeMapEntries ($map, $key0, $key1) {
    //Remove entries containing either or both key0 and key1
    global $dl1,  $dl2;       
   
    $fArray1 = readArray ($map, $dl1);
    
    foreach ($fArray1 as $Id => $Item1) {
        $fArray2 = explode ($dl2, $Item1);          
        if (array_key_exists (0, $fArray2) && array_key_exists (1, $fArray2)) {               
        
            if ($key0 !== "" && $key1 !== "") {
                if ( $fArray2[0] === $key0  && $fArray2[1] === $key1 ) {                       
                    unset ($fArray1 [$Id]);
                }
            }
            else if ($key0 === "" && $key1 !== "") {
                if ($fArray2[1] === $key1) {
                    unset ($fArray1 [$Id]);
                }
            }
            else if ($key0 !== "" && $key1 === "") {
                if ($fArray2[0] === $key0) {
                    unset ($fArray1 [$Id]);
                }
            }
        }
    }
    writeArray ($map, $fArray1, $dl1);
    
}

    
function selectMapEntries ($map, $key0, $key1) {
     global $dl1,  $dl2;
     $fArray1 = readArray ($map, $dl1);
    //returns an array with selected key
    global $dl1,  $dl2;
    $selectedarray = array();    
    
    foreach ($fArray1 as $Item1) {    
        $fArray2 = explode ($dl2, $Item1);       
        if (array_key_exists (1, $fArray2)) {
          
            if ($key0 && !$key1 ) {               
                if ($fArray2[0] == $key0 ){           
                    array_push ($selectedarray, $fArray2[1]);
                }
            }  
            else if (!$key0  && $key1) {
                if ($fArray2[1] === $key1) {
                     array_push ($selectedarray, $fArray2[0]);
                }
            }            
        }
    }    
    return $selectedarray;    
}


function selectMapKey ($map, $key0, $key1) {
    
    //returns an single value
    global $dl1,  $dl2;   
    $returnvalue = '';
   
    $fArray1 = readArray ($map, $dl1);
   
    foreach ($fArray1 as $Id => $Item1) {
        $fArray2 = explode ($dl2, $Item1);      
      
        if ( array_key_exists (1, $fArray2)) {
           
           if (!$key0  && $key1) {
              if ($fArray2[1] === $key1) {
                  $returnvalue = $fArray2[0];
                  break;
              }                
            }
            else if ($key0  && ! $key1) {
               
                if ($fArray2[0] === $key0){                  
                    $returnvalue = $fArray2[1];                   
                    break;                  
                }
            }
        }
    }    
    return $returnvalue;    
}

function extractFromMap($map, $key) {
    //returns a new array of either the the first or second columns
    global $dl1,  $dl2;
  
    $selectedarray = array();  
    $fArray1 = readArray ($map, $dl1);
    foreach ($fArray1 as $Item1) {
      
        $fArray2 = explode ($dl2, $Item1);       
        if ($key == 0)  { 
            array_push ($selectedarray, $fArray2[0]); 
        }
        else if ($key == 1) {   
            if (array_key_exists (1, $fArray2)) {
                array_push ($selectedarray, $fArray2[1]);                 
            }
        }       
    }    
    return $selectedarray;    
}



function moveToTrash ($table, $recordid) {   
    $oldfilename = 'data/' . $table . '/' . $recordid . '.txt';
    $newfilename = 'data/trash/' . $table . '----' . $recordid . '.txt';  
    if (file_exists ($oldfilename)) {
        //rename ($oldfilename, $newfilename);         
    }   
}

//ARRAYS 

function readArray ($filename, $delimiter){
  
    $fArray1 = array();
    if (file_exists($filename)) { 
      
        $String = file_get_contents ($filename);
        if ($String !== "") {
            $fArray1 = explode ($delimiter, $String); 
        }
    }  
    return $fArray1;    
}

 function writeArray ($filename, $array, $delimiter){    
    $String = implode ($delimiter, $array);    
    //file_put_contents ($filename, $String);          
}  
        
function addNameToArray ($filename, $name, $delimiter) {
  
    if (file_exists($filename)) {  
        $String = file_get_contents ($filename);
        $fArray1 = explode ($delimiter, $String);  
		array_push ($fArray1, $name);
        $fArray1 = array_unique ($fArray1);
		$String = implode ($delimiter, $fArray1);	
        $String = preg_replace('/,+/', ',', $String); 			
		//file_put_contents ($filename, $String);
    }
}       
function removeNameFromArray ($filename, $name, $delimiter) {  
    
    if (file_exists($filename)) {  

        $String = file_get_contents ($filename);
        $Array = explode ($delimiter, $String);   
       
        foreach ($Array as $Id => $Item) {
            $Item = trim ($Item);
            if ($Item ===  $name) {
                unset ($Array [$Id]);
            }
        }
        array_values ($Array);
        $String = implode ($delimiter, $Array);
        //file_put_contents ($filename, $String);
    }
}

//Validate 'GET'  value
function getFromQueryString ($label) {   
	$value = "";
	if (isset ($_GET[$label])) {        
		$value =  filter_var($_GET[$label], FILTER_SANITIZE_STRING) ; 
		$value = trim ($value);
	}    
	return $value;    
}

//Filter input text
function sanitizeFormInput($text) {
    global $dl1, $dl2;  
    $text = trim($text);       
    $text = str_replace ($dl1, '', $text);
    $text = str_replace ($dl2, '', $text);
    return $text;
  }

  function showWords ($text) {
    $fancytext = ucwords (str_replace ('-', ' ', $text));
    return $fancytext;
}

//CHECK FOR DUPLICATE EMAILS
function checkForDuplicateEmail ($sendtoemail, $message) {
    global $dl1, $dl2;  
    $error = false;
    $emailarchive = readArray ('data/email-archive.txt', $dl1);
    $emailentry = date ("Y-m-d") . $dl2 .$sendtoemail . $dl2  . $message . "\n";	
    if ( in_array ($emailentry, $emailarchive)) {
        $error = true;
        echo "<div class = 'error'>Message is a duplicate</div>";
    }
    return $error;
}

function randomString () {    
   $String = "";
   $chars = "abcdefghijklmanopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
   $size = strlen($chars);
   for ($i = 0; $i < 7; $i++) {
       $String .= $chars[rand(0, $size - 1)];
   }
   return $String; 
}

function assignPostValuesToRecord ($postarray, $keys) {
   
    //Assign  values to record   
    $record = array();      
    foreach ($keys as $Id => $key) {
        $record[$key] = ""; 
        if (isset ($postarray[$key])) {
            $record[$key] =  sanitizeFormInput ($postarray[$key]); 
        }
    }    
    return $record;   
    
}
function saveToLog ($text) {
    
    global $dl1, $dl2;    
    $currenttime = date ("h-i:sa");       
    $date = date ("Y-m-d");
    $filename = 'data/log.txt';
    $String = file_get_contents ($filename);
    $String = $String . "\n\n "  . $date . " " . $currenttime . "  ". $text;
    file_put_contents ($filename, $String);
}
?>

For example, to find all the stories by an author, you would use the 'selectMapEntries' function and specify the 'story-author' map and the author record id. By leaving the story record variable emtpy, all the stories by the author will be returned in an array.

The home page queries the database using the category-account map. Accounts associated with each category are listed, and if there are no relationships found, the account is listed as 'Uncategorized.'

ACCOUNTS/pages/home.php ▾


<a class = 'menuitem' href = 'index.php?page=add-update-account'>Add New Account</a> 

<?php

$categories = readArray ('data/categories.txt', ',');
echo "<div>";
sort($categories);
foreach ($categories as $cat) { 
    $cat = trim ($cat);
    $selectedaccounts = selectMapEntries ('data/category-account-map.txt',  $cat, '');

    echo "</div><div class = 'box'>";
    echo "<h4>" . ucwords(str_replace ('-', ' ', $cat)) . "</h4><br>";    
    
    $array1 = scandir ("data/accounts");
   
    foreach ($array1 as $item) {
        if ($item !== "." && $item !== ".."){
            $accountid = str_replace (".txt", "", $item);
            if (in_array ($accountid, $selectedaccounts)) {
                      
                $accountrecord = readDatabaseRecord($accountrecordkeys, 'data/accounts/' . $accountid . '.txt');
                
                echo "<a  class = 'gridcolumn' href = 'index.php?page=display-account&account=" . $accountid . "'>" . $accountrecord['name'] .  "</a>";    
            }
        }
    }
    
}
echo "</div>";


echo "<h2>All Accounts</h2>"; 
echo "<div class = 'box'>";
$array1 = scandir ("data/accounts");
foreach ($array1 as $item) {
    if ($item !== "." && $item !== ".."){
        $accountid = str_replace (".txt", "", $item);
        $accountrecord = readDatabaseRecord($accountrecordkeys, 'data/accounts/' . $accountid . '.txt');
        echo "<a  class = 'gridcolumn' href = 'index.php?page=display-account&account=" . $accountid . "'>" . $accountrecord['name'] . " </a>";
        
    }
}   
echo "</div>";
echo "<br><br><br><h2>Uncategorized</h2>";
$array1 = scandir ("data/accounts");
foreach ($array1 as $item) {
    if ($item !== "." && $item !== ".."){
        $accountid = str_replace (".txt", "", $item);
        $selectedarray = selectmapEntries ('data/category-account-map.txt', '', $accountid);
       
        if (! $selectedarray) {           
            $accountrecord = readDatabaseRecord($accountrecordkeys, 'data/accounts/' . $accountid . '.txt');      
            echo "<a  class = 'gridcolumn' href = 'index.php?page=display-account&account=" . $accountid . "'>" . $accountrecord['name'] .  "</a>";   
        }        
    }
}

    ?>

Website Projects

Flat File Relational Database Example 1

Section 2: Relationships between records

Section 3: Accessing Records and Maps