project-overview | Bill's Kiosk 42

Information Kiosk:
Photography
Website Construction
Techie Stuff

Welcome to Using a Database in your website project.

The DRY principal (Don't Repeat Yourself) is the first function of a database. Hard coding sequential iterations of the same but different items is a pain and really not necessary. The AI creators are only too well aware that everything is data. So we are going to show you how to get started in the wonderful world of databases.

For this demo, please refer to my site Fab Four Wordle for a live example. Four friends play Wordle every day, and we keep track on this site. The daily update takes me less than 2 minutes to enter the scores and upload the SQLite database to my online service. Foundation 6 and Stacks make it all come together.

Intro - Database for Your Website

If you have not used a Database in your website yet, you may believe that it's a huge undertatking, employing vast rooms of mainframes and heavy duty software, but we have some really cool tools right in Stacks. We'll get you up to speed in no time.

My choices of tools in the Stacks environment include:

SQLite - database manager - already a part of your Mac OS
TablePlus - to manage the data - CRUD (Create, Read, Update and Delete)
Feeds - to bring the data to your web page
Foundation 6 and Stacks to make it all come together on your site.

SQLite

Let's start with SQLite. Rather than steal content from the web, I will refer you to an excellent Tutorial on SQLite https://www.sqlitetutorial.net/ This covers most of what you need to learn about this free DBM. Of note, it is also "serverless", unlike the very good and popular MySQL.

SQLite uses dynamic data types for tables. It means you can store any value in any column, regardless of the data type. This is really convenient.

There are several Client tools to help handle your data. After lots of trials, I settled on a paid version of TablePlus, both for MacOS and for iOS. It handles many other databases and server connections in addition to SQLite.

What about large databases? Can they be handled by SQLite? Well in theory, the limit is north of 250TB, so just get on with your project. Your iPhone, iPad, and MacBook all use SQLite to keep things in order.

Feeds Stack

Get Feeds

To get your data from the db to your site, you need Feeds. It is designed to work in Stacks and will handle your data very smoothly. Keep in mind, retrieving and presenting data is the job for Feeds, CSS, other Stacks, while the raw data itself lives in the dtatbase. In fact, Feeds generally does not pass any formatting along with data. This separation of function is a good thing.

SQL

SQL is the language of databases. Although SQLite has some peculiarities or "gotchas", it generally follows the SQL norms. In particular, if you are familiar with MySQL, you will have to make some adjsutment to your queries, but it is not a huge deal. The official documnets are well done in presenting the necessary information.

Database SQLite

There are lots of ways to create an SQLite database. We will walk through using TablePLUS Available for $89 (free trial available).

Open TablePlus, select + for a new connection. Select Sl (SQLite) - Create.

Name: projectDemo

Database Path: In your Rapid Weaver setup, add a data Folder under /rw_common and use this path. /yoursite.com/rw_common/data

Click on Connect - you get a blank page. At the bottom, click on the + to create a new table. Top panel, type in "project" . Your table has been initiated with an "id" column. In the cell below, double click to add more columns.

For data-types, gameDate can be TEXT. The player names are all INTEGER, since it is recording their scores.

Feeds is the magic stack that handles all your data needs. Since SQLite lives on your computer, the setup is quite simple.

Drop a Feeds stack on the page, click on the + and select SQLite Feed. The DB Path will be /rw_common/data/project.sqlite3.

Query - All Data from Table

Table Name - project

Now open the csv file, copy the contents, and paste them in the CSV Data field on the right hand side. This will be your sample data.

Below the SQLite Feed, drop your stacks for presenting the data. Using the Twigs macro style {{field name}} within a Block grid, we get our data nicely presented on the page.

Finally

When you are all done, Publish the pages and the /rw_common/data/project.sqlite3 files to your server. If you make changes to the data, you do so on your local computer, then upload the sqlite3 file to your online server. (Transmit or Filezilla will easily handle SFTP uploads)

That is a basic setup for using SQLite to house your data, allowing you to add more data easily without having to rewrite the page presentation. As always with dtabases, pieces of data (rows) are best kept plain. Make it all look good with stacks.

Before Database Setup

If you refer to the example, there is last month's winner panel on the home page. It is constructed simply using a Markdown table and some Font Awesome chess pieces:

|**Last Month Result**|||||

|----|----:|----:|----:|----:| |November 2023|Gerry 98|Frank 100|Bill 110|Jule 116|

... to produce this:

Last Month Result
November 2023	Gerry 98	Frank 100	Bill 110	Jule 116

Having to update this format every day calls for a Database solution.

Date formatting: Wed - 01^st, Thu - 02^nd, Fri - 03^rd, Sat - 04^th

I want to share with you some solutions I have discovered in formatting dates, just to save you endless hours of mucking about yourselves.

Stacks 5

Foundation 6

SQLite or MySQL

Feeds

To see the results, go here: https://w3.billbooth.ca/nov23Games

First, we start with the standard "date" field in MySQL or SQLite database: YYYY-MM-DD. On your page put {{gameDate}} in a Markdow container which will print out (2023-11-24). (case matters with database column names. Here I have used gameDate as my column name.). Twig allows us to format the date using |date() function. ( | is the pipe symbol above the Return key)

D = Day of week
d = date number
M = month
Y = yyyy

gameDate|date(’D, d') note the single quotes around the Day and date parameters) outputs Mon, 06^th

To add the little "th" or "nd" after the day number, use some if statement below. The first line checks for the day number. If it is the first(1), twenty first(21), or thirty first(31), it will add st to the day number, else it will chck for 2 or 22 to add nd and so on. Lastly, the rest get the most prevalent th. The "" formats to superscript if you want.

Each if statement {% if ... %} needs a closing {% endif %} - note the 3 {% endif %} at the end.

{% if gameDate|date('d') in [1,21,31] %}{{gameDate|date('D - d')}}^st{% else %} {% if gameDate|date('d') in [2,22] %}{{gameDate|date('D - d')}}^nd{% else %} {% if gameDate|date('d') in [3,23] %}{{gameDate|date('D - d')}}^rd {% else %} {{gameDate|date('D - d')}}^th {% endif %}{% endif %}{% endif %}

Have fun. Bill Booth

Contact Bill Booth bill@billbooth.ca

Home of Kiosk - 42