personal database with web front end 101?

[cellio]

I've been using RateBeer to track beers I've tasted and how much I liked them. This is helpful to pull up on a phone in a restaurant or store. But it relies on their database; if they haven't heard of a beer (and I don't want to do very cumbersome editing to add it on the fly), I can't rate it. Untapped seems to have a larger database but a terrible mobile site.

Fundamentally, this is the wrong approach for me anyway. Sites like RateBeer and Untapped exist to collect and aggregate user-contributed content. I don't care about that. I'm not interested in "social beer". I just want to keep track of things I've tried. And this isn't really just about beer; in days of yore when I bought more books on paper, I wanted to be able to look up what I already own while standing in a bookstore, but GoodReads is not really the interface for that. Similarly, keeping track of board games I like (and variants) is not really a job for BoardGameGeek.

What I need is my own private little database, with a web front end to support both queries (searches) and data entry. I'm the only user, so I don't need anything fancy. (Web, not app, because while I'll do some data entry on the phone, anything non-trivial is going to be done on a computer with a real keyboard.)

This sure feels like a solved problem, but I'm not quite sure what to search for. (Or rather, my searches are leading me to pages like "how to use .NET to build your web form".) My web hosting comes with CPanel links to set up both MySQL and Postgres databases. I think I know the basics of raw HTML forms but I don't yet know how to hook one up to a running database, nor how to access-protect it. I'm comfortable with the SQL to create and query the tables, and while every database is a little different on this I assume I can figure out data import from CSV.

Or maybe I should be looking for something hosted, like Google Sheets but for an actual database. (I've tried importing this data into Google Sheets. Using that on my phone is pretty terrible and it doesn't really support search anyway.) So long as I can export data from someone else's service, I don't need to self-host. But if self-hosting is easy I'd prefer that.

Out of curiosity I asked ChatGPT, and it gave me some PHP with a username and password baked in and a suggestion to do better security. The code doesn't do quite what it said it would do (based on inspection), but it's broadly plausible and ChatGPT even pointed out the problems with security, input sanitation, and validation.

Any advice from my readers?

Comments

[dsrtao]

You don't really need much database capability, right? Full text search would work. In which case, I recommend a wiki like DokuWiki. Just lock it down so that only the people you trust can edit it, and figure out how you want to represent a beer's data in the markdown-like language.

[dsrtao]

and feel free to look at my mostly-recipes dokuwiki -- wiki.randomstring.org

[cellio]

Interesting; I hadn't thought about wikis at all. I'd like to be able to answer questions like "show me all the stouts" and "what else have I had from this brewery" and "what are the most recent ratings", which feel database-ish to me, but maybe tagging helps, and text search for things like a beer type or name shouldn't produce much noise in a site that's just about tracking -- not going to get noise from reviews that compare and contrast beers or articles that talk about different styles, after all. Hmm.

[hlinspjalda]

Cacophanous seconding for a home wiki. They have brought so much order and organization into my life that I can scarcely remember how I functioned beforehand. Mr. Fixer likes dokuwiki, so that's what we use. I have a separate wiki just for my research, and there's the home one that all of us use (including the family recipe database). I use indexing terms, index pages, and internal links a lot to help me keep larger things organized, like all my Tolkien research pages.

[dsrtao]

If you made a template and added beers to it:

Beer Name:
Style:
Brewery:
Rating (#):
Bitterness:
Notes (free text):

then a full-text search for "Style: stout" or "Brewery: Sheepshead" will get you those, and dokuwiki, as with many other wikis, can easily produce the list of most recent updates in a subtree.

Filesystems are databases.

[cellio]

Thanks! When you say "template", do you mean that's something DokuWiki supports natively, or do you mean something you create externally and paste in when creating a new page?

[dsrtao]

I meant the latter, but a moment's searching turned up this:

https://www.dokuwiki.org/plugin:struct

which is effectively a database frontend plugin (with a sqlite backend).

https://www.dokuwiki.org/plugin:struct:sample:simple

[siderea]

Ooooh! Commenting mostly just to get this in my email. We are also a DocuWiki family. This could be super helpful.

[fauxklore]

Library Thing works well for me for tracking books.

[cellio]

Oh cool, thanks. I should look at it.

[magid]

Seconding LibraryThing.

They're also a nice corner of the internet, with a lot of things you can do if you like, or wholly ignore if not interested.

I put my library in there after being laid off at one point, but if you have mostly books with US ISBNs, it's not too time consuming to add a lot of books fairly quickly (they also offer a bar code reader do-hickey for not a lot of money that could be useful; a friend lent me hers). I found that the time-consuming part was the Hebrew books, and ones old enough to lack an ISBN or other similar types of data.
(Um, same username there as here, if you end up using it.)

[siderea]

Okay, one small important part of this:

> nor how to access-protect it.

cPanel comes with a feature called I think "directory protect" or something like that. You just select the directory you don't want any old person to access, and you can set up a username and password for accessing that directory through the web.

What it is doing under the hood is setting up a .htaccess file, which is something you can also do manually if you prefer.

When anything in a .htaccess protected directory is accessed by HTTP(S), the browser pops up modal dialogue prompting for username and password.

[siderea]

If your cPanel hosting account supports Softaculus (not all do), there's something in there called Form Tools that might do what you want – I don't know, because I haven't played with it yet.

[jducoeur]

Usual reminder: Querki is still a thing, and this is more or less precisely its sweet spot. I could probably help you build such a site in half an hour.

[jducoeur]

To give a relatively close example, here's a randomly-chosen Booze from my "Mixology" Space.

That's about as trivial as it gets, using just out-of-the-box functionality. (The unfortunate sideways photos are a bad interaction of the browser and how my tablet takes photos -- one of these days I need to improve it.)

[cellio]

Oh, duh! I should have remembered Querki. I'll send you email. One question I'll ask here (others might benefit): can it model something akin to sets? If I want to record multiple ratings with their dates and possibly other info, can I do that? In other words, I don't want the schema to be too strict. :-) I'd like to be able to have entries like the following:

Chimay Grande Reserve (Blue)
- Brewery: Abbaye de Scourmont - Chimay
- Type: Belgian Ale, Strong, Dark
- Rating: 5 - 2024-02-01
- Rating: 4 - 2023-12-29 (large bottle)
- Rating: 5 - 2016-05-09
(RateBeer entry link)

That's a cut/paste (minus some formatting) from one of my test DokuWiki pages. RateBeer, whence I exported ratings, doesn't allow multi-ratings, so that's an example of something I'd like to add that the DB of origin didn't give me. (Also, currently all these ratings are mine 'cause it's my RB account, but in the future we'll want to be able to annotate with whose rating it is, so Dani and I could have separate rating lines there.)

[jducoeur]

Interesting question! The short answer is "probably", although I'd have to dig around and think about how best to do it.

The built-in Review property assumes that it's single-valued per person for any given target Thing. Making it multi-valued would require building a homebrew version of that property in user space; in principle that seems like it should be straightforward, but I'm not sure offhand whether I've ever tried it.

Basically, I don't think it would be quite a Set per se; rather, a Beer would have associated DatedReview objects, each of which has a Rating or Review. The display for the Beer would grab all of those, sort them by timestamp, and show them.

So not quite so simple as my Mixology Space, but I think I could support that without needing to make changes to Querki itself. Needs an experiment or two, though.