![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
personal database with web front end 101?
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?
no subject
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.)
no subject
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:
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.)
no subject
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.