Convert Spreadsheet to Database

<?php

include ('inc/functions.php');
include ('inc/header.php');
 $pageid = getFromQueryString ('page');
if (! $pageid) {
    $pageid = 'home';
}
include 'pages/' . $pageid . '.php';
include ('inc/footer.php');
?>

<?php
//Remove previous records
$array1 = scandir ('data/records');
foreach ($array1 as $item1) {
    if (substr ($item1, 0, 1) !== '.') {
        unlink ('data/records/' . $item1);
    }
}
$spreadsheetkeys = readArray ('data/spreadsheetkeys.txt', ',');
$recordkeys = readArray ('data/recordkeys.txt', ',');

$file = fopen('temp.csv',"r");
$count = 0;
$recordnumber = 0;
while(! feof($file) ){
    $array1 = fgetcsv($file);   
    if ($array1) {   
        if ($count === 0) { 
            $count++;   
        }        
        else  {   
            //create a temporary record with values in the order of the original csv file
            $temprecord = array();              
            foreach ($spreadsheetkeys as $id => $key) {
                if (array_key_exists ($id, $array1)) {   
                   $temprecord[$key] = ""; 
                   $item1 = $array1[$id]  ;
                   $item1 = preg_replace('/[^A-Za-z0-9-?!@\s\/]/', '', $item1);                       
                   $temprecord[$key] =  $item1;
                }
            }        
            //move values from temporary record to database record
            $record = initializeRecord ($recordkeys);           
            foreach ($spreadsheetkeys as $key) {
                if (in_array ($key, $recordkeys)) {                                   
                    $record[$key] = $temprecord[$key];
                }
            }     
            
            //Create record id
            $recordid = '';
            $number = 0;
            if ($key1) {              
                $recordid = $temprecord[$key1]; 
                if ($key2) {                        
                    $recordid .=  "-" . $temprecord[$key2]; 
                    if ($key3) {                               
                        $recordid .= "-" . $temprecord[$key3]; 
                        
                    }
                }
                if ($key4 === 'number-first') {
                    $recordnumber++;
                    $recordnumber = str_pad ($recordnumber, 5, 0, STR_PAD_LEFT);
                    $recordid  = $recordnumber  . "-".  $recordid;   
                } 
                else if ($key4 === 'number-last') {
                    $recordnumber++;
                    $recordnumber = str_pad ($recordnumber, 5, 0, STR_PAD_LEFT);
                    $recordid .=  "-" . $recordnumber ;   
                }
            }

            if ($recordid ) {
                $recordid = str_replace (' ', '', $recordid);
                $recordid = strtolower ($recordid);                    
                $recordid = preg_replace('/[^A-Za-z0-9-]/', '', $recordid);  
                if (file_exists ('data/records/' . $recordid . '.txt')) {
                    echo "File already exists: " . $recordid . "<br>";
                }
                else {
                    writeDatabaseRecord ($record, 'data/records/' . $recordid . '.txt'); 
                }
            }
            
        }
      
    }        
}  
fclose($file);

?>

<?php
echo "<div class = 'page'>";
echo "<h2>Database Records</h2>";
echo "<div class = 'column1'>Record Id</div>";
$recordkeys = readArray ('data/recordkeys.txt', ',');
foreach ($recordkeys as $key) {
      echo  showWords ($key). ",";
}
echo "<br><br>";

$array1 = scandir ('data/records');
foreach ($array1 as $item1) {
    if (substr ($item1, 0, 1) !== '.') {
        $recordid = str_replace ('.txt', '', $item1);
        echo "<div class = 'column1'>";
        $record = readDatabaseRecord ($recordkeys, 'data/records/' . $recordid . '.txt');
        echo "<a class = 'record'  href = 'display-record.php?recordid=" . $recordid . "'>" . $recordid . "</a>";
        echo "</div>";
        echo "<div class = 'column2'> ";
        foreach ($recordkeys as $key) {            
            echo $record[$key] . "," ;           
        }
        echo "</div><br><br>";    
    }   
}
echo "</div>";
?>

</main>
</body>
</html>

<?php

//delimeters for records
$dl1 = "%%%";
$dl2 = "%#%";

function initializeRecord ($keys){     
    $record = array ();
    foreach ($keys as $Id => $key) {
        $record[$key] = ""; 
    }  
    return ($record);
}
function readDatabaseRecord ($keys,  $filename) {
    //Creates 'record' variable by assigning keys and values to associative array
    //To use:  $record = readDatabaseRecord($keys, $filename);
    global $dl1;
    //Assign null values to each key
    $record = array();  
    foreach ($keys as $Id => $key) {
        $record[$key] = ""; 
    }
    //If text string is found, assign values to each key
    if ($filename && file_exists($filename)) {        
        $string = file_get_contents ($filename);  
        $Array1= explode ($dl1, $string);
        foreach ($keys as $Id => $key) { 
            if (array_key_exists ($Id, $Array1)) {
                $record [$key] = $Array1[$Id]; 
            }        
        }          
    } 
    return $record;  
}


function writeDatabaseRecord ($record,  $filename) { 
    //Stores record array as text file
    global $dl1;    
    $String = implode ($dl1, $record);     
    file_put_contents ($filename, $String);      
}

//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 getFromQueryString ($label) {  
	$value = "";
	if (isset ($_GET[$label])) { 
        $value =  $_GET[$label];	
        if (specialChars($value) || strlen ($value) > 300) {            
            //Invalid input
            $value = '';
        }            
	}   
	return $value;    
}


function specialChars($str) {
     global $dl1, $dl2; 
  
    if (strpos ($str, "<") !== false || strpos ($str, ">") !== false | strpos ($str, $dl1) !== false | strpos ($str, $dl2) !== false ) {
        return true;
    }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>CSV-DATABASE </title>
    <link rel="stylesheet" href = 'inc/style.css'>
</head>
<body>    
      <main>   
        <header>
          <a href = '../../spreadsheet-to-database'>&larr; Return</a>
         <h2><a href = 'index.php'>Create a flat-file database from a spreadsheet</a></h2>
         
         <a class = 'menuitem' href = 'index.php?page=home''>Home</a>
         <a class = 'menuitem' href = 'index.php?page=display-table'>View records in table</a> 
         <a class = 'menuitem' href = 'index.php?page=update-field-names'>Update Field Names</a>
       
       </header>

<?php


$file = fopen('temp.csv',"r");
$error = false;
$spreadsheetkeys =  array();
$record = array();
$count = 0;
while(! feof($file) && $count === 0){
    $array1 = fgetcsv($file);   
    if ($array1) {   
        $count ++;  
        
        foreach ($array1 as $item1) {
            $item1 = preg_replace('/[^A-Za-z0-9-?!,\s\/]/', '', $item1); 
            $item1 = str_replace (' ', '', $item1);
            $item1 = strtolower($item1);
            array_push ($spreadsheetkeys, $item1); 
        }        
        writeArray ('data/spreadsheetkeys.txt', $spreadsheetkeys, ',');  
    }
}
fclose($file);

?>

<form method = 'post' action = 'index.php'>
    <h3>Select up to 3 column headings to combine into a primary record key</h3>
    <div class = 'third-column'>
        <h4>Key part 1</h4>
        <?php
        foreach ($spreadsheetkeys as $key) {
            echo "<input id = '" . $key . "-1' type = 'radio' name = 'key1' value = '" . $key . "'/>";
            echo "<label for = '" . $key . "-1' >" . $key . "</label><br>";
        }
        ?>
    </div><div class = 'third-column'>
        <h4>Key part 2</h4>
        <?php
        foreach ($spreadsheetkeys as $key) {
            echo "<input id = '" . $key . "-2' type = 'radio' name = 'key2' value = '" . $key . "'/>";
            echo "<label for = '" . $key . "-2' >" . $key . "</label><br>";
        }
        ?>
    </div><div class = 'third-column'>
        <h4>Key part 3</h4>
        <?php
        foreach ($spreadsheetkeys as $key) {
            echo "<input id = '" . $key . "-3' type = 'radio' name = 'key3' value = '" . $key . "'/>";
            echo "<label for = '" . $key . "-3' >" . $key . "</label><br>";
        }
        ?>
        </div><br><br><br>
    <div class = 'third-column'>
        <b>Add a number to the beginning or end of the record key</b><br><br>
        <input id = 'number-first'  type = 'radio' name = 'key4' value = 'number-first'/>
        <label for = 'number-first' >number First</label><br>
        <input id = 'number-last'  type = 'radio' name = 'key4' value = 'number-last'/>
        <label for = 'numberlast' >number Last</label><br>

    </div><div class = 'third-column'>
        <b>Select fields to be included in record</b><br><br>
        <?php
        foreach ($spreadsheetkeys as $key) {
            echo "<input id = '" . $key . "' type = 'checkbox' name = 'recordkeys[]' value = '" . $key . "'/>";           
            echo "<label for = '" . $key . "' >" . $key . "</label><br>";            
        }        
        ?>   
   </div>
    <br><br><input class = 'submitbutton' type = 'submit' name = 'submit-keys-and-fields' value = 'Submit'>
</form>  

body {       
    font-family: 'Arial', sans-serif;
    line-height: 100%;
    text-align:center;
}
h1,h2,h3 {
    text-align: center;
}
h4 {
    margin: 10px 0;

}
h4 i {
    font-weight: normal;
}
.page, .page2 {
    text-align: left;   
    max-width: 100%;    
    margin: 50px auto;
}
.page2 {
    width: 600px;
}
.column1, .column2, .third-column {   
    width: 300px;
    display: inline-block;
    box-sizing: border-box;
    padding: 0 10px;
    vertical-align: top;
    max-width: 100%;
}
.column2 {
    width: auto;
}
.third-column {
    width: 30%;
    width: 300px;
    text-align: left;
    border: 1px solid #bbb;
    margin: 0 5px;
    padding: 10px;
}
.error {
    color: red;
}
a {
    color: darkmagenta;
    text-decoration: none;
}
header {
    margin: 30px auto 60px auto;
}
 .submitbutton {    
    color: white;      
    margin:   5px auto;
    padding: 5px 7px;       
    font-size: 14px;    
    cursor: pointer;    
    text-decoration: none;        
    display: block;       
    border-radius: 2px;    
    font-style: normal;
    width: 140px;
	text-align: center;
    background-color: darkmagenta;
}
 .menuitem {	
    padding: 5px 20px;
    box-sizing: border-box;   
    cursor: pointer;       
	display: inline-block;
    font-size: 14px;
    background-color: #eee;
    
}

<?php


$recordkeys = readArray('data/recordkeys.txt', ',');
$recordid = getFromQueryString ('record');
$record = readDatabaseRecord ($recordkeys, 'data/records/' . $recordid . '.txt');


echo "<div class = 'page2'>";
echo "<h2>View Record</h2>";
echo "<h3>" . $recordid . "</h3>";

foreach ($recordkeys as $key) {
    if ($key) {
        echo "<b>" . ucwords (str_replace ('-', ' ', $key)) . "</b>: ";
        echo $record[$key]  . "<br><br>";
    }
     
}
echo "</div>";

?>

<?php

echo "<div class = 'page'>";
echo "<h2>Database table</h2><br><br>";
echo "<div class = 'column1'>Record Id</div>";
$recordkeys = readArray ('data/recordkeys.txt', ',');
foreach ($recordkeys as $key) {
      echo  ucwords (str_replace ('-', ' ', ($key))) . ", ";
}
echo "<br><br>";

$array1 = scandir ('data/records');
sort ($array1);
foreach ($array1 as $item1) {
    if (substr ($item1, 0, 1) !== '.') {
        $recordid = str_replace ('.txt', '', $item1);
        echo "<div class = 'column1'>";
        $record = readDatabaseRecord ($recordkeys, 'data/records/' . $recordid . '.txt');
        echo "<a class = 'record'  href = 'index.php?page=display-record&record=" . $recordid . "'>" . $recordid . "</a>";
        echo "</div>";
        echo "<div class = 'column2'> ";
        foreach ($recordkeys as $key) {            
            echo $record[$key] . ", " ;           
        }
        echo "</div><br><br>";    
    }   
}
echo "</div>";

?>

<?php

if ($_SERVER ["REQUEST_METHOD"] == "POST") {     
  
    if (isset ($_POST['submit-csv'])) {  
        //If not input, use default CSV file   
        copy ('data/FCS.csv', 'temp.csv');  
        if ($_FILES['uploadfile1']['name'] !== ''){          
            $imagebase1 = $_FILES['uploadfile1']['name'];      
            $imagename = pathinfo ($imagebase1, PATHINFO_FILENAME);
            $extension = strtolower (pathinfo ($imagebase1, PATHINFO_EXTENSION));
            if($extension ===  'csv' ) {                 
                $fileTmpName = $_FILES['uploadfile1']['tmp_name'];                     
                move_uploaded_file($fileTmpName, 'temp.csv');   
            }    
        }     
        include ('inc/identify-csv-column-headings.php');  
        include ("inc/key-and-field-selection-form.php");       
    }
    
    else if (isset ($_POST['submit-keys-and-fields'])){  
        $recordkeys = array();
        $key1 = $key2  = $key3 = $key4 = '';

        if (isset ($_POST['key1'])) {
            $key1 = $_POST['key1'];
            $key1 = preg_replace('/[^A-Za-z0-9-?!,\s\/]/', '', $key1); 
            $key1 = str_replace (' ', '', $key1);
            $key1 = strtolower($key1);
        }
        if (isset ($_POST['key2'])) {
            $key2 = $_POST['key2'];           
            $key2 = preg_replace('/[^A-Za-z0-9-?!,\s\/]/', '', $key2); 
            $key2 = str_replace (' ', '', $key2);
            $key3 = strtolower($key3);
        }
        if (isset ($_POST['key3'])) {
            $key3 = $_POST['key3'];           
            $key3 = preg_replace('/[^A-Za-z0-9-?!,\s\/]/', '', $key3); 
            $key3 = str_replace (' ', '', $key3);
            $key3 = strtolower($key3);
        }
        if (isset ($_POST['key4'])) {
            $key4 = $_POST['key4'];            
        }
        if (isset ($_POST['recordkeys'])) {
            $recordkeys = $_POST['recordkeys'];          
        }
        

        //Create record key array, including any new keys                     
        if ($key1 ){                
            writeArray ('data/recordkeys.txt', $recordkeys,',');
            include ('inc/convert-csv-file-to-records.php');
            echo "<h4>Records have been created</h4>";
            if (file_exists ('temp.csv'))  {           
                 unlink ('temp.csv');   
            }   
        }
        else {
            echo "<div class = 'error'>Missing keys</div>"; 
            if (file_exists ('temp.csv'))  {           
                unlink ('temp.csv');   
            }   
        } 
    }
}

else { 
?>
<h4>Choose a CSV file ot click 'Enter' to use the default file</h4>
<form action="index.php?page=home" method="post" enctype="multipart/form-data">    
    <label for = 'uploadfile1'>csv file </label>
    <input id = 'uploadfile1'  type="file" name="uploadfile1"  /><br><br>  

    <input class = 'submitbutton' type = 'submit' name = 'submit-csv' value = 'Enter' />
</form>  
<?php
}
?>

<?php


$recordkeys = readArray('data/recordkeys.txt', ',');
 
if ($_SERVER ["REQUEST_METHOD"] == "POST") { 
    
    $newkeys = array(); 
    foreach ($recordkeys as $key) {
        if (isset ($_POST[$key])) {
            if ($key) {
                //leave out empty values
                array_push ($newkeys, $_POST[$key]); 
            }
        }
    }
    //Additional keys
    if (isset ($_POST ['keystring'])) {   
        if ($_POST['keystring']) {
            $keystring = $_POST['keystring'];
            $array1 = explode (',', $keystring);    
            $newkeys = array_merge ($newkeys, $array1);
         }
    }   

    writeArray ('data/recordkeys.txt', $newkeys, ',');
    $recordkeys = readArray('data/recordkeys.txt', ',');
}
?>
<div class = 'page2'>
<h2>Update  Field Names</h2><br>
<form method = 'post' action = 'index.php?page=update-field-names'>
    <?php
    foreach ($recordkeys as $key) {
        if ($key) {        
            echo "<label for = '" . $key . "'>" . $key . "</label> &nbsp; &nbsp;";        
            echo  "<input id = '" . $key . "' type = 'text' name = '" . $key  . "' value = '" . $key . "' /><br><br>";   
        }  
    }
    ?>
    <label for = 'keystring'><b>Enter fields to be added to record, separated by commas</b></label><br>
    <textarea id = 'keystring' name = 'keystring'></textarea>

    <input class = 'submitbutton' type = 'submit' name = 'submit' value = 'Enter' />
</form>

LASTNAME,FIRSTNAME1,Firstname2,Streetname,Street Number,EMAIL
Gigglesnark,Henrietta,,Cheeseburger Circle,3,Hgiggle@meowmail.com
Dieter,Isley,,Cheeseburger Circle,5,BNFeliniov@catmail.com
Dieter,Ralph,,Cheeseburger Circle,1,BitPan16@meowmail.com
Softpaw,Mittens,,Cheeseburger Circle,7,HRod19@catmail.com
Feliniov,Boris,Natasha,Cheeseburger Circle,6,Aarmstrong@meowmail.com
Pantherson,Bitsy,Snippy,Cheeseburger Circle,4,GRachio618@meowmail.com
Softpaw,Blackie,,Cheeseburger Circle,2,IsleyD@meowmail.com
Armstrong,Mercedes,Snow,Cheeseburger Circle,9,Ralph@meowmail.com
Chaserson,Prissy,,Feathertoy Circle,8,MittensS@purrmail.com
Snowball,Larry,Lucy,Feathertoy Circle,9,BlackieSoftpaw@meowmail.com
Kerspeckles,Wilhelmina,Buddy,Feathertoy Circle,7,MerArm67@meowmail.com
Ritzsimmons,Mitzy,,Hideaway Court,1,Prissy1@purrmail.com
Rodgers,Willow,,Hideaway Court,2,LarryLucySnow@meowmail.com
Rodgers,Squeekie,,Hideaway Court,3,WKersp671@catmail.com
Rodgers,Holly,,Feathertoy Circle,6,Mitzy55@catmail.com
Twitchitail,Tom,Ginger,Feathertoy Circle,5,WRod22@catmail.com
Rodgers,Tanster,,Feathertoy Circle,4,SqueekRod@meowmail.com
Pantherson,Peter,,Feathertoy Circle,3,Twitch17@purrmail.com
Cat,Kitty,,Feathertoy Circle,2,TanROd@meowmail.com
Patterson,Princess,,Longtail Court,1,PeterP@purrmail.com
Softpaw,Simkin,,Longtail Court,2,KC6722@purrmail.com
Cat,Baby,,Longtail Court,3,PrincessP@catmail.com
Calico,Gordon,Ophelia,Longtail Court,4,SSoft2525@purrmail.com
Cat,Bob,,Lower Treetrunk Drive,1,BC4938@meowmail.com
Patterson,Catzilla,,Lower Treetrunk Drive,2,GOCalico414@catmail.com
Feliniov,Ivan ,Caterina,Lower Treetrunk Drive,3,bobcat772@catmail.com
Armstrong,Antonio,,Mousetail Blvd,1,CatzillaP@catmail.com
Pawclaw,Martin,Betty,Mousetail Blvd,2,ICFeliniov18@catmail.com
Bluewhisker,Josephina,,Mousetail Blvd,3,MBPawclaw@catmail.com
Rodgers,Scottie,,Mousetail Blvd,4,Jblue222@meowmail.com
Patterson,Pinkie,,Mousetail Blvd,5,Scotrod78@catmail.com
Cat,Bunny,,Mousetail Blvd,6,PinkieP@purrmail.com
Patterson,Pinkie,,Mousetail Blvd,7,BunnC25@catmail.com
Shaggyback,Snow,,Mousetail Blvd,8,SardinesRUS@catmail.com
Sheddington,Fluffy,,Mousetail Blvd,9,Sshaggy@catmail.com
Rachio,Goldy,,Lower Treetrunk Drive,4,Fluffy1@purrmail.com
Jones,Cleopatra,,Lower Treetrunk Drive,5,Cleo30BC@catmail.com
Thagorus,Py,,Lower Treetrunk Drive,6,PyThag314@meowmail.com
Mewton,Isaac,,Lower Treetrunk Drive,7,IMew1790@purrmail.com
Dribble,Rocco,,Pasatuna Avenue,3,Rocco1@meowmail.com
Frankenfluff,Buttons,,Pasatuna Avenue,4,Buttons1@purrmail.com
Rockafurrer,Mimi,,Pasatuna Avenue,2,Mimimi@catmail.com

lastname,firstname1,firstname2,email

lastname,firstname1,firstname2,streetname,streetnumber,email

Dieter%%%Ralph%%%%%%BitPan16@meowmailcom

Softpaw%%%Blackie%%%%%%IsleyD@meowmailcom

Gigglesnark%%%Henrietta%%%%%%Hgiggle@meowmailcom

Pantherson%%%Bitsy%%%Snippy%%%GRachio618@meowmailcom

Dieter%%%Isley%%%%%%BNFeliniov@catmailcom

Feliniov%%%Boris%%%Natasha%%%Aarmstrong@meowmailcom

Softpaw%%%Mittens%%%%%%HRod19@catmailcom

Armstrong%%%Mercedes%%%Snow%%%Ralph@meowmailcom

Cat%%%Kitty%%%%%%TanROd@meowmailcom

Pantherson%%%Peter%%%%%%Twitch17@purrmailcom

Rodgers%%%Tanster%%%%%%SqueekRod@meowmailcom

Twitchitail%%%Tom%%%Ginger%%%WRod22@catmailcom

Rodgers%%%Holly%%%%%%Mitzy55@catmailcom

Kerspeckles%%%Wilhelmina%%%Buddy%%%MerArm67@meowmailcom

Chaserson%%%Prissy%%%%%%MittensS@purrmailcom

Snowball%%%Larry%%%Lucy%%%BlackieSoftpaw@meowmailcom

Ritzsimmons%%%Mitzy%%%%%%Prissy1@purrmailcom

Rodgers%%%Willow%%%%%%LarryLucySnow@meowmailcom

Rodgers%%%Squeekie%%%%%%WKersp671@catmailcom

Patterson%%%Princess%%%%%%PeterP@purrmailcom

Softpaw%%%Simkin%%%%%%KC6722@purrmailcom

Cat%%%Baby%%%%%%PrincessP@catmailcom

Calico%%%Gordon%%%Ophelia%%%SSoft2525@purrmailcom

Cat%%%Bob%%%%%%BC4938@meowmailcom

Patterson%%%Catzilla%%%%%%GOCalico414@catmailcom

Feliniov%%%Ivan %%%Caterina%%%bobcat772@catmailcom

Rachio%%%Goldy%%%%%%Fluffy1@purrmailcom

Jones%%%Cleopatra%%%%%%Cleo30BC@catmailcom

Thagorus%%%Py%%%%%%PyThag314@meowmailcom

Mewton%%%Isaac%%%%%%IMew1790@purrmailcom

Armstrong%%%Antonio%%%%%%CatzillaP@catmailcom

Pawclaw%%%Martin%%%Betty%%%ICFeliniov18@catmailcom

Bluewhisker%%%Josephina%%%%%%MBPawclaw@catmailcom

Rodgers%%%Scottie%%%%%%Jblue222@meowmailcom

Patterson%%%Pinkie%%%%%%Scotrod78@catmailcom

Cat%%%Bunny%%%%%%PinkieP@purrmailcom

Patterson%%%Pinkie%%%%%%BunnC25@catmailcom

Shaggyback%%%Snow%%%%%%SardinesRUS@catmailcom

Sheddington%%%Fluffy%%%%%%Sshaggy@catmailcom

Rockafurrer%%%Mimi%%%%%%Mimimi@catmailcom

Dribble%%%Rocco%%%%%%Rocco1@meowmailcom

Frankenfluff%%%Buttons%%%%%%Buttons1@purrmailcom