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.

[fauxklore]

Library Thing works well for me for tracking books.

[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.