Simple Flat-File Database Tutorial Using PHP
Do you need a simple way to manage a small amount of data on your website?
Maybe you are a crazy cat lady and you would like to keep track of all your cats. You will need a 'cats' database with records for individual cats and a way to both display your cat data and make modifications.
The following steps will show you how to set up an application, "All My Cats", to manage your cats using a flat-file database. You can follow along and copy each file as it it presented, or you can or you can download the 6 files and one folder that make this application run. Download the 'Cats' Database Files .
"All My Cats" provides three web pages - One for displaying cat data, one for adding new cats and one for editing existing cat data. A single database table is used: 'cats-table'
Figure 1
This page lets you add a cat to the database:
Figure 2
This page lets you make modificaitons to a particular cat's data:
Figure 3
Database: file folder that contains a collection of data
Table: file folder within the database folder
Row or record: text file within the table folder.
Field or column: sections of the text record separated by delimters
Keys: the primary key is the file name.
Steps to creating the "All My Cats" application:
Step 1 - Create folders and define 'cats' table
Create a folder: 'all-my-cats'. This will hold the data and the code to manipulate the data.
Inside 'all-my-cats', create a folder: 'cats-table'.
Now let's define the structure of the 'cats' table. We will be creating the define-cats-table.php' file to do this. A single line of code is needed:
<?php
$catkeys = array ("Name", "Color", "Sex", "Age", "Extra");
?>We will use these keys to convert the character string in a cat record into an associative array.
If we need another key later, it can be added to the end of the array.
Step 2: Build a page with a form to collect cat data (as in Figure 2)
Notice that 'define-cats-table.php' is included at the beginning so that each input field can corresond to one of these keys.'add-cat.php' -first part
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Add Cat'</title>
<link rel= 'stylesheet' type='text/css' href= 'style.css'>
</head>
<body>
<main class = 'centered' >
<h1><a href = 'index.php'>All My Cats</a></h1><br>
<h2>Add a Cat</h2><br>
<!-- SHOW FORM -->
<form method='post' action='add-cat.php'>
<?php
foreach ($catkeys as $key) {
echo "<b>" . $key . "</b> <input name = '" . $key . "' type = 'text'><br><br>";
}
?>
<br><br>
<input type = 'submit' name = 'submit' value='Update'/>
</form>
</main>
</body>
</html>
When the 'submit' button is clicked, the form will be sent back to this same file for processing. Let's add the processing code to the top of the file, right after the table definition.
'add-cat.php' - second part
<?php
include ("define-cats-table.php");
//RETRIEVE FORM INPUT
if ($_SERVER ["REQUEST_METHOD"] == "POST" ) {
//ASSIGN FORM INPUT TO CAT RECORD
foreach ($catkeys as $key) {
if (isset ($_POST[$key])) {
$catrecord [$key] = trim($_POST[$key]);
}
}
//CHECK INPUT FIELDS FOR ERRORS
$error = 0;
if ($catrecord['Name'] === ""){
$error = 1;
echo "<h4>The 'name' field is missing</h4>";
}
else {
//USE NAME TO CREATE A UNIQUE ID
$newid = str_replace(" ", "-", $catrecord['Name']);
$newid = strtolower ($newid);
$catid = $newid;
// CHECK IF ID ALREADY EXISTS
$filename = "cats/" . $catid . ".txt";
if (file_exists ($filename)) {
$error = 1;
echo "<h4>Cat with this name aleady exists</h4>";
}
}
if ($error === 0) {
//INPUT IS VALID - CONVERT CAT ARRAY INTO A STRING
$catstring = implode ("???", $catrecord);
$catstring = html_entity_decode ($catstring, ENT_QUOTES );
//WRITE CAT RECORD
file_put_contents ("cats-table/" . $catid . ".txt", $catstring);
echo "<h4>" . $catrecord ['Name'] . " has been updated sucessfully!</h4>";
}
}
?>
Here's the complete version of 'add-cats.php' with both parts included:
'add-cats.php'<?php
include ("define-cats-table.php");
//RETRIEVE FORM INPUT
if ($_SERVER ["REQUEST_METHOD"] == "POST" ) {
//ASSIGN FORM INPUT TO CAT RECORD
foreach ($catkeys as $key) {
if (isset ($_POST[$key])) {
$catrecord [$key] = trim($_POST[$key]);
}
}
//CHECK INPUT FIELDS FOR ERRORS
$error = 0;
if ($catrecord['Name'] === ""){
$error = 1;
echo "<h4>The 'name' field is missing</h4>";
}
else {
//USE NAME TO CREATE A UNIQUE ID
$newid = str_replace(" ", "-", $catrecord['Name']);
$newid = strtolower ($newid);
$catid = $newid;
// CHECK IF ID ALREADY EXISTS
$filename = "cats/" . $catid . ".txt";
if (file_exists ($filename)) {
$error = 1;
echo "<h4>Cat with this name aleady exists</h4>";
}
}
if ($error === 0) {
//INPUT IS VALID - CONVERT CAT ARRAY INTO A STRING
$catstring = implode ("???", $catrecord);
$catstring = html_entity_decode ($catstring, ENT_QUOTES );
//WRITE CAT RECORD
file_put_contents ("cats-table/" . $catid . ".txt", $catstring);
echo "<h4>" . $catrecord ['Name'] . " has been updated sucessfully!</h4>";
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Add Cat'</title>
<link rel= 'stylesheet' type='text/css' href= 'style.css'>
</head>
<body>
<main class = 'centered' >
<h1><a href = 'index.php'>All My Cats</a></h1><br>
<h2>Add a Cat</h2><br>
<!-- SHOW FORM -->
<form method='post' action='add-cat.php'>
<?php
foreach ($catkeys as $key) {
echo "<b>" . $key . "</b> <input name = '" . $key . "' type = 'text'><br><br>";
}
?>
<br><br>
<input type = 'submit' name = 'submit' value='Update'/>
</form>
</main>
</body>
</html>
Now, we are ready to add our first cat to the table!
Here's what you should have in your 'all-my-cats' folder: 'define-cats-table.php', 'add-cats.php' and the empty 'cats-table' folder. I have included a 'style.css'file, too which will be used later.
'style.css'
main {
max-width: 800px;
display: block;
margin: auto;
}
.centered {
text-align: center;
}
.field {
display: inline-block;
width: 130px;
margin: 5px auto;
}
h1, h2, h3, h4 {
text-align: center;
}
input {
margin: 5px;
font-size: 16px;
}
If you are on your own computer, you can enter ' localhost/all-my-cats/add-cat.php'. Hopefully, you will see the page with the form. Go ahead and add a cat! Or 2 or 3!
Now, wouldn't it be nice to see a display of all the cats you have added? Like in figure 1?
Step 3 - Display table data (Figure 1)
In order to access our data, we need to retrieve it and convert it into a a usable format. The 'read-cat-record.php' file does this by converting a text record into an associative array. That way each field can be accessed by its key.
'read-cat-record.php'<?php
//CREATE AN EMPTY CAT RECORD ARRAY
$catrecord = array();
foreach ($catkeys as $key) {
$catrecord[$key] = "";
}
//READ SELECTED CAT RECORD AND ASSIGN VALUES TO ARRAY
$catstring = "";
$catfilename = "cats-table/" . $catid . ".txt";
if (file_exists ($catfilename) ){
$catstring = file_get_contents ($catfilename);
if ($catstring !== "") {
$array1= explode ("???", $catstring);
foreach ($catkeys as $id => $key) {
$catrecord[$key] = "";
if (array_key_exists ($id, $array1)) {
//catrerecord is an associative array
$catrecord [$key] = $array1[$id];
}
}
}
}
?>For this example, the display page is 'index.php'. First, the HTML page is created. Then, a header is displayed showing each of the keys from 'define-cats-table.php'.
'index.php' - first part<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>'All My Cats'</title>
<link rel= 'stylesheet' type='text/css' href= 'style.css'>
</head>
<body>
<main>
<h1><a href = 'index.php'>All My Cats</a></h1><br>
<?php
//BUILD HEADER
$catid = "";
include ("define-cats-table.php");
echo "<div class = 'field'><b>Id</b></div>";
foreach ($catkeys as $key) {
echo "<div class = 'field'><b>" . $key . "</b></div>";
}
echo "<hr>";Under the header, let's show each cat with associated data. Just read through the 'cats-table' folder and retrieve each cat record.
'index.php' - second part //RETRIVE EACH RECORD IN the 'CATS-TABLE' FILE
$array1 = scandir ("cats-table");
foreach ($array1 as $item) {
if ($item !== "." && $item !== "..") {
$item = trim ($item);
$catid = str_replace (".txt", "", $item);
//SHOW ID AS A LINK TO THE ADD/UPDATE PAGE
echo "<br><br><div class = 'field'> <a href = 'update-cat.php?catid=" . $catid . "'>" . $catid . "</a></div>";
include ("read-cat-record.php");
foreach ($catrecord as $field) {
echo "<div class = 'field'>" . $field . "</div>";
}
}
}
?>
<br><br><br>
<i> Click on Cat Id to edit.</i><br><br>
<a href = 'add-cat.php'>Add A New Cat</a>
<br><br>
</main></body></html>
This is the complete version of 'index.php'
'index.php<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>'All My Cats'</title>
<link rel= 'stylesheet' type='text/css' href= 'style.css'>
</head>
<body>
<main>
<h1><a href = 'index.php'>All My Cats</a></h1><br>
<?php
//BUILD HEADER
$catid = "";
include ("define-cats-table.php");
echo "<div class = 'field'><b>Id</b></div>";
foreach ($catkeys as $key) {
echo "<div class = 'field'><b>" . $key . "</b></div>";
}
echo "<hr>";
//RETRIEVE EACH RECORD IN the 'CATS-TABLE' FILE
$array1 = scandir ("cats-table");
foreach ($array1 as $item) {
if ($item !== "." && $item !== "..") {
$item = trim ($item);
$catid = str_replace (".txt", "", $item);
//SHOW ID AS A LINK TO THE ADD/UPDATE PAGE
echo "<br><br><div class = 'field'> <a href = 'update-cat.php?catid=" . $catid . "'>" . $catid . "</a></div>";
include ("read-cat-record.php");
foreach ($catrecord as $field) {
echo "<div class = 'field'>" . $field . "</div>";
}
}
}
?>
<br><br><br>
<i> Click on Cat Id to edit.</i><br><br>
<a href = 'add-cat.php'>Add A New Cat</a>
<br><br>
</main></body></html>
At this point you should have 5 files along with the 'cats-table' folder:
Entering "localhost/all-my-cats' should provide a display of all the cats currently in the table.
Step 4 - Provide an editing page (Figure 3)
Our user would also like to be able to edit a cat record from the link in index.php, so another file is needed -'edit-cat.php', similar to 'add-cat.php'
'edit-cat.php'<?php
include ("define-cats-table.php");
$catid = "";
if (isset ($_GET["catid"])){
$catid = filter_input(INPUT_GET, "catid", FILTER_SANITIZE_STRING) ;
$catid = trim ($catid);
}
include ("read-cat-record.php");
//RETRIEVE FORM INPUT
if ($_SERVER ["REQUEST_METHOD"] == "POST" ) {
//ASSIGN FORM INPUT TO CAT RECORD
foreach ($catkeys as $key) {
if (isset ($_POST[$key])) {
$catrecord [$key] = trim($_POST[$key]);
}
}
//CREATE STRING FROM CAT RECORD
$catstring = implode ("???", $catrecord);
$catstring = html_entity_decode ($catstring, ENT_QUOTES );
//WRITE CAT RECORD
file_put_contents ("cats-table/" . $catid . ".txt", $catstring);
echo "<h4>" . $catrecord ['Name'] . " has been updated sucessfully!</h4>";
}
//DISPLAY FORM
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>'Edit a Cat Record '</title>
<link rel= 'stylesheet' type='text/css' href= 'style.css'>
</head>
<body>
<main class = 'centered' >
<h1><a href = 'index.php'>All My Cats</a></h1><br>
<h2>Update <?php echo $catrecord['Name'] ;?></h2><br>
<!-- SHOW 'GO-BACK' ARROW -->
<a href = 'index.php'>← Go Back</a><br><br><br>
<!-- SHOW FORM -->
<form method='post' action='update-cat.php?catid=<?php echo $catid ;?>'>
<?php
foreach ($catrecord as $key => $value) {
echo "<b>" . $key . "</b> " ;
echo "<input name = '" . $key . "' type = 'text' value = '" ;
echo $value . "'><br><br>";
}
?>
<br><br>
<input type = 'submit' name = 'submit' value='Update'/>
</form>
</main>
</body></html>
At this point you should have all 6 files along with the 'cats-table' folder:
Congratulations if you made it this far. Stayed tuned for more Fun with Flat Files!