Flat File Database Demo 2

How to create sortable table columns using a flat file database and PHP.

1st December 2004 · Last updated: 8th December 2011
 

Sections/Permalinks

  1. Introduction
  2. The Demo
  3. The PHP Code
  4. Explanation
  5. Summary
  6. Further Reading
  7. Comments

Introduction

In my first Flat File Database Demo I mentioned that you could build on the code to make a table with sortable columns. I was asked in the comments to show how, so here is the code.

Basically a loop reads the data from a text file line by line, and an array is used to store each line. This means when the loop has finished, we can use the array to sort it before outputting the data as a table. Depending on a variable used, which is added to the web address of the page, the array is sorted by any of the table columns.

Have a look at the demo in action. I'll then go through the code and explain it.


RUN THE DEMO

The PHP Code

UPDATE 21st May 2010: the code below is subject to possible updates over time so check the actual demo itself for the latest code.

Firstly, the code below needs to go at the very top of your HTML file - before even the doctype or <html> tag. Each table column is a link with a different variable added, as you'll see by the code further on. So we need to process these first. The columns relate to a fictional car-owner's club, which stores a variety of details about its members in a flat file database.

$sortby = $_SERVER['QUERY_STRING'];
$set = '';
if ($sortby == '') {
 $header = 'First Name';
 $sortby = 'firstname';
 $set = 'y';
}
if ($sortby == 'surname') {
 $header = 'Surname';
 $set = 'y';
}
if ($sortby == 'city') {
 $header = 'City';
 $set = 'y';
}
if ($sortby == 'points') {
 $header = 'Points';
 $set = 'y';
}
if ($sortby == 'car') {
 $header = 'Car';
 $set = 'y';
}
if ($sortby == 'colour') {
 $header = 'Colour';
 $set = 'y';
}
if ($sortby == 'age') {
 $header = 'Age';
 $set = 'y';
}
if ($set == '') {
 echo 'The variable at the end of the web address did not match one required by the code. Please check the web address for errors.';
 exit;
}

If the variable is missing, we can use this to set a default column sort that uses the First Name. But if the variable is wrong, an error message is output instead and the page stops.

Because the above code gives you a variable $header, this can be used first to give a unique header to the page, depending on how you are sorting the data. Here is the line of code needed to do that:

echo '<h1>Flat File Database Sorted By <span class="sortby">'.$header.'</span></h1>';

Dropping back into HTML, the following code gives you the column headers, with each one a link to sort the column by using a variable. Note that the first link doesn't need a variable as this is the default view.

<table summary="List of demo fields">
<tr>
<th><a href="?">First Name</a></th>
<th><a href="?surname">Surname</a></th>
<th><a href="?city">City</a></th>
<th><a href="?points">Points</a></th>
<th><a href="?car">Car</a></th>
<th><a href="?colour">Colour</a></th>
<th><a href="?age">Age</a></th>
</tr>';

Next is the code needed to sort the database and output the table cells:

$fp = fopen('flat-file-data2.txt','r');
if (!$fp) {echo 'ERROR: Unable to open file.</table></body></html>'; exit;}
$row = 0;
while (!feof($fp)) {
 $row++;
 $line = fgets($fp,1024); //use 2048 if very long lines
 $field[$row] = explode('|', $line);
 if ($sortby == 'firstname') {$sortkey = strtolower($field[$row][0]);} //firstname
 if ($sortby == 'surname') {$sortkey = strtolower($field[$row][1]);} //surname
 if ($sortby == 'city') {$sortkey = strtolower($field[$row][2]);} //city
 if ($sortby == 'points') {$sortkey = $field[$row][3];} //points
 if ($sortby == 'car') {$sortkey = strtolower($field[$row][4]);} //car
 if ($sortby == 'colour') {$sortkey = strtolower($field[$row][5]);} //colour
 if ($sortby == 'age') {$sortkey = $field[$row][6];} //age
 array_unshift($field[$row], $sortkey); //add sortkey to start of array
}
fclose($fp);
sort($field);
reset($field);
$arrays = count($field) - 1;
$loop = -1;
while ($loop < $arrays) {
 $loop++;
 echo '
<tr>
<td>'.$field[$loop][1].'</td>
<td>'.$field[$loop][2].'</td>
<td>'.$field[$loop][3].'</td>
<td>'.$field[$loop][4].'</td>
<td>'.$field[$loop][5].'</td>
<td>'.$field[$loop][6].'</td>
<td>'.$field[$loop][7].'</td>
</tr>';
}
echo '
</table>';

Explanation

A loop reads the data from the flat file database. It is a standard text file where each line contains data to match each column, separated by the pipe ("|") symbol. The loop reads each line until the end of the file is reached. A variable $row is incremented on each loop cycle. Its use is to define each line as part of an array later.

I found that the PHP command asort() fails sometimes to sort an array alphabetically properly, so I've added an extra variable $sortkey to the array, which is used to sort with. The array is defined as $field[$row] and contains each field of data that was first extracted from the database using explode.

Once the loop is finished, we're left with an array that contains the entire database, plus the variable $sortkey we can use to sort it. To do this, just use sort($field); then reset($field);. Depending on the column we wish to sort, the variable $sortkey will always contain the data for that column. Hence when the array is sorted, it is always in the way we want, for example, by Surname. The sorting is alphabetical and also works well with numbers.

Next we need to output the array into table cells. But how do we know how many lines there are in it? Just use count($field); and it will tell you.

Now a loop can be started that goes through the array until the number of lines in it is exhausted. To reference a piece of data in the array, the format needed looks like this: $field[$loop][1]. The array was called $field, remember? While $loop comes from the variable used to count each line of the first loop. The last number (shown in square brackets) gives you the stored field you want from the relevant line of the array. So say you wanted the third field of data from the tenth line in the array, you'd do it like this: $field[9][2]. But wait, you cry, those numbers are one less! Correct - arrays always start from 0, so the first line and the first field would be $field[0][0]. This is handy because the first field stored is the sort key, so we can ignore that and count the fields from [1].

The table cells are then output using the relevant array fields. Easy, eh? Of course these can be put in any order - you don't have to follow the order used in the database. You might even wish to miss fields out.

Summary

An array is usually defined in one line of code that fills it with variables. But here we're using multiple arrays. The database fields are stored as separate arrays in a master array, just like a grid. This means we can access any part of the arrays, and also sort them. Combining this with a basic table of cells gives you a dynamic page - the table can be rearranged by the user. I've used this code to output long lists of information with section headers to break up the lists. By adding anchors in each section header, I was able to put links at the top of the page to go straight to them. I would detail the code in another demo, but it really is long and messy! Yet it gives one example of how arrays can be used with a flat file database. I am sure there are many more ways. Feel free to experiment with my code and see what you can come up with. Let me know if it was useful!

Further Reading

See my Flat File Database Demo which is the basis of the above code but much simpler.

Comments (7)

Comments are locked on this topic. Thanks to everyone who posted a comment.

  1. Chris Hester:
    I've redone parts of the code to make it even more efficient. What I found today is this: you don't need to declare the variable on the URL in this form - "file.php?sortby=surname" - there's a shorter way. I replaced $sortby = $_GET['sortby'] on the first line of code with $sortby = $_SERVER['QUERY_STRING']. So now all you need for the column header links is just this: "?surname" (or "?car" etc). No need to have "$sortby=" at all!

    This technique also works with multiple variables, but you need to split the Query String down first.

    Posted on 2 December 2004 at 7:38 pm
  2. Chris Hester:
    Here is an even better method that uses JavaScript (meaning faster sorting as the page is not reloaded). It also allows the columns to be sorted in reverse order:

    http://www.kryogenix.org/code/browser/sorttable/

    Posted on 9 December 2004 at 2:29 pm
  3. Dante Evans:
    Would it be possible to just use file_get_contents instead of fopen and fread?

    Posted on 2 January 2005 at 5:21 am
  4. Darren P:
    Hi Chris,

    I really like your flat file database 2 demo. I was wondering whether making each cell sortable or certain columns sortable would be too complex coding-wise?

    In addition is there a way to hide a column from appearing in the case of a primary key/auto-increment field (Col Id)?


    For example:

    Col Id   --¦-- Col Name --¦-- Col Ph   --¦-- Col Zip
    
    =====================================================
    00000001 --¦-- EEEEEEEE --¦-- 11111111 --¦-- ########
    00000002 --¦-- BBBBBBBB --¦-- 22222222 --¦-- $$$$$$$$
    00000003 --¦-- CCCCCCCC --¦-- 33333333 --¦-- %%%%%%%%


    If I were to click on any of the above cells it would sort the table according to that value. So if I click on $$$$$$$$ It would sort the table starting with those records that have $$$$$$$$ in Col Zip.

    Is this achievable with php? I'm not proficient in php at all so forgive the simplicity of my question.

    Cheers

    Darren

    Posted on 28 January 2005 at 4:06 am
  5. Chris Hester:
    "In addition is there a way to hide a column from appearing in the case of a primary key/auto-increment field (Col Id)?"

    Yes, that's how the code works already. The first entry for each row is stored in an array, but you don't see it output. (I do this to ensure the table sorts properly.)

    As for sorting by cell, I haven't thought of that one. A good idea! In theory, it would be easy - PHP is more than flexible enough. As to the exact code, I'm not sure. I guess you'd just make the cell a link to sort the whole table again, using a variable.

    Have a go with the code yourself. PHP's great fun and quite easy to learn.

    Posted on 28 January 2005 at 9:52 am
  6. mark:
    wow!!! this is exactly what i have been looking for!! both this and part 1 were very helpful, informative and well written. now i can only hope that you do a "1.1 How to search a Flat File Database" which would include search commands and outputting to html... then this section would be the perfect complement because you could sort the results by whatever column you like.

    regardless, this is great, thanks!

    Posted on 31 January 2005 at 8:20 pm
  7. Phil:
    Hi, this is really good, and very helpful to someone like me trying to learn PHP. I am fooling around with it and am having just a little bit of difficulty with a minor issue. The flat file I would like to use is a text file log containing the IP addresses of comment spammers to my blog. It is generated by the blogging engine I use. It will always contain an extra end of line character (paragraph mark?) at the end of the file. That creates an empty row, which looks odd when it gets sorted. Is there a way to simply delete the empty row after or during the process of creating the array? Like if the row is blank, then delete it? Where in the code would this go? I have blindly tried inserting some code that I found searching for an answer for this, but so far no luck.

    Any advice greatly appreciated, and thanks!

    Phil

    Posted on 12 February 2005 at 7:11 am