Creating a simple database (presenting data online)

Before getting sidetracked with refinements such as using if() and choose(), I was talking about using spreadsheets to write HTML, to make it easier to post data online. But when you do that it’s very not easy to change the data, except by completely overwriting it with a new table. So if you’ll want to update the data regularly, for example once a month, setting up a simple database and a PHP script to interpret and display the data.

Setting up a database for a PHP script is very similar to setting up a table to display the data. The two main differences are that number formatting is the only spreadsheet formatting that matters, and if you add an index column at the beginning with some sort of consecutive numbers in it then it will be much more adaptable.

The first step is to compile the data you want to present into a single area. Here’s a breakdown of the steps needed for that. You can see the steps laid out in this example, with one step per worksheet. The final result is in the Present_PHP worksheet.

Step 1: Extract a list of things you’re going to display data about
These will form the row headers in your table. Just copy the column from your original data (the Data_Original worksheet in the example) set that has everything you need, then get rid of any duplicates (see the Data_DuplicatedDates worksheet for an example).

Step 2: Set up the column headers for a table
You can use dates if you’re displaying something that happens over the source of time, such as monthly statistics, or you can use brief descriptions such as ‘start’ and ‘end’ for dates, or “HW 1”, “HW 2”, and “Test 1” if you’re recording grades. Just remember that in your displayed table your columns will default to being as wide as the widest thing in them, so if your column headers are much wider than your data you’ll be wasting a lot of visual space. You can override that if you know enough HTML or CSS, but if you do so then your headers might end up either very tall or else broken in strange places, so it’s generally best to keep them as short as possible while still making sense to the reader.

In the example I used dates, and you can see that I used another incremental formula to make adding all the dates easier. This is significant since each time I update these dates, I’ll be adding another column with another date. It’s not that big a deal to type in the new date, but it’s faster and more guaranteed to be accurate to simply copy the formula and paste it to a new column.

Since I used concatenate() and countif() on a different worksheet in this set of examples, I decided to use countifs() in this one. If you’re using a spreadsheet that can handle it then it’s the simpler option. Column B holds the library names, and row 1 holds the dates, so the formula I used was =COUNTIFS(SourceData_LibraryName,$B2,SourceData_Year,YEAR(C$1),SourceData_Month,MONTH(C$1))

Looking at that formula you may note that I named the references to the source data, since it came from another sheet and so would be hard to find the right column for at a glance. For the references within this table I just used dollar signs to make them fixed references so I could copy the formula across rows and columns.

Step 4: Set up an index column
This isn’t technically necessary, but it can make a lot of things easier and it doesn’t hurt anything since no-one but you will ever see this version of the data, so I recommend it.

After that, there are two easy ways to set up an index column. One is to use the row() function, and the other is to use an iterative formula. Generally, I prefer to have the header row indexed as 0, so in cell A1 I would either put =row()-1, or simply 0. Then in A2 I would either copy the same =row()-1 formula, or else use =A1+1. Thereafter, all the cells in column A would be the same as A2. This is why I recommend waiting until after you’ve set up all the row headers, so you know how far down this column should go.

Step 5: Save the file
This step is just to make absolutely sure all your changes are saved in a format you can re-use later.

Step 6: Save as a .CSV file
In case you haven’t read this post, CSV stands for Comma Separated Values. It strips all the fonts, borders, backgrounds, and other formats, and discards the formulas. The only thing it retains is the values, but it only saves what’s shown when you look at the spreadsheet. That’s why number formatting matters. If you see $1.10 in the cell, then that’s what will appear in CSV file even though what’s actually stored in the spreadsheet is 1.1. Likewise, it will store a date as a date, not as the number it’s actually stored as.

You can save in .CSV format by following the instructions here. Be sure to remember to be on the worksheet you want saved — it’ll only save one, and discard the rest.

That’s it for the spreadsheet side of things! The next step would just be to upload the .CSV file to the server, where the PHP script can extract and display it. The PHP for this isn’t too hard. For NCknows I used two files: one to create a drop-down list with all the libraries, and another to display that statistics for that library. If you’d like to see the code, or if you’d like me to build one or more PHP pages for you, feel free to write me at gexner@swissarmyspreadsheets.com.