Try to understand (and edit) the database

[biber]

Some years ago I set up a site http://malabu.de which consists of a number of galleries. I built it with the "image-extra" module from justb3a to add further informations to the images. Meanwhile this module seems to be obsolete and I added fields as Custom Fields in a template "field-images".

Recently I took a look at the database and now I would like to understand what's going on there:

The most interesting table seems to be the table "field_images" with 1844 records according to all images. Here an example: 

A look at one record shows a range of empty fields and the field "filedata" with a collection of contents, 2 examples:

 

Quote

{"_109":{"data":"K\u00fcrbis auf Bl\u00e4tterteig","data1015":""},"_108":"Eine Rolle Bl\u00e4tterteig, darauf eine Packung Sauce Hollandaise und drei ganz fein gehackte Knochlauchzehen verteilen. T\u00fcchtig mit Kr\u00e4utern der Provence und gr\u00fcnem Pfeffer aus der M\u00fchle w\u00fcrzen. Dann einen kleinen oder einen halben Hokkaido in feine Scheiben schneiden und darauf verteilen, nochmals mit Kr\u00e4utern und etwas Kr\u00e4utersalz w\u00fcrzen, ein wenig K\u00e4se drauf und im auf 200\u00b0 vorgeheizten Backofen 20-25 Minuten backen. Ist lecker und dazu pa\u00dft auch gut ein Salat.","_110":"K\u00fcrbis auf Bl\u00e4tterteig","_111":"","_112":"2024-02-12 00:00:00","_116":"","_117":"Eine Rolle Bl\u00e4tterteig, darauf eine Packung Sauce Hollandaise und drei ganz fein gehackte Knochlauchzehen verteilen. T\u00fcchtig mit Kr\u00e4utern der Provence und gr\u00fcnem Pfeffer aus der M\u00fchle w\u00fcrzen. Dann einen kleinen oder einen halben Hokkaido in feine Scheiben schneiden und darauf verteilen, nochmals mit Kr\u00e4utern und etwas Kr\u00e4utersalz w\u00fcrzen, ein wenig K\u00e4se drauf und im auf 200\u00b0 vorgeheizten Backofen 20-25 Minuten backen. Ist lecker und dazu pa\u00dft auch gut ein Salat."}

 

Quote

{"_109":{"data":"In Borgwedel","data1015":""},"_108":"Warten auf den Bus. Es geht nach Hause.","_110":"Borgwedel","_111":"","_112":"2024-02-13 00:00:00","ix":"110_Borgwedel"}

In the database I find other files like field_im1, field_im_2, field_iname_1, field_iname_2, field_kopf etc. which sound like fields I have in my field_images-template, see here:

But they do not contain any records.

Now my questions:

1. May I delete empty fields from the record?

2. May I delete empty files?

3. How can I  transfer parts of the filedata-field which seem to be caption to the caption-field? Of cause not record for record, but for all images at once

4. Can phpMyAdmin show UTF-8 (show 'Umlaute')

filedata_example.txt filedata_example_2 .txt

[cwsoft]

I would never change or modify the database directly unless there is a real need to do so. If the frontend is not broken (e.g. German umlauts displayed correctly, images show up etc.), I wouldn‘t touch the database at all. If you want to get a deeper understanding than create a local copy of your site and give it a shot. Checked some pages in the frontend and umlauts are displayed correctly so there is no need to change the values stored in the database at all from my point of view.

[biber]

Dear @cwsoft, thanks for your answer.

I'm an old-fashioned hobby programmer who likes to be the master of his data. I simply wrote my first web pages in HTML in an editor and knew exactly which tag produced which result. I love PW because it gives me freedom, but sometimes I like to look behind the curtain and understand what's happening.

Of course, I won't change the database without a backup. But it's still my challenge to split data from one field and transfer it to other fields. Maybe this is a PHP task, I'm not so fit in there yet.

[cwsoft]

@biberI am not the one you need to convince. It‘s your data and you can do with your database whatever you like. I was just puzzled by what you want to achieve or whats your problem is or if there is any at all. All still not very clear to me to be honest. What caught my attention was that I got the impression that you wanted to alter the text representation in the database, as the database values don‘t show up UTF8 chars but store German Umlauts in an encoded representation. Thats where my advice came from, not to alter or manipulate DB charset/collation manually, if text shows up correct in the frontend. Doing so will most likely corrupt your database and you need to go deep inside the rabbit hole to get charset/collation and data right.

Of course you can modify, copy, rename or delete fields with PHPMyAdmin or from the Adminer module from the backend. Adminer is part of the Tracy Debugger module for example. 

However, for me it‘s just not clear, what you try to do and why it‘s necessary to do. If it‘s just for experiment, than I would just go with phpMyAdmin or Adminer and change some content and see what happens.

By the way. I write most of my websites with plain HTML5, CSS3 and vanilla Javascript/Typescript and or PHP 8. Just for projects with multiple users or changing content, I prefer Processwire CMS/CMF which saves me a lot of work. However in those cases I rely on the PW core and API and only modify/dig deeper into the core or database if there is a real need for me to do so.

Edited August 27 by cwsoft

[biber]

@cwsoftEven though I've been building websites for a few years, I'm still a beginner in many topics. I started with PW about 10 years ago. To add fields to the images of a gallery I installed the "image-extra-module", which is no longer maintained. The function has now arrived at the core, but the module left some oddities in the database at that time, which today cause problems with ordering and sorting. My question was how I can clean up here without having to re-enter all content.

[cwsoft]

I don‘t know the outdated module and how it stores text and maps that to images. From your first table, one sees some null values for gif images but not for jpg for example. When did the issues start? Have you or your ISP upgraded the PHP version used etc. Is it really needed to remove the module or could sorting etc. be fixed by fixing the module? Have you checked error logs and enabled debug mode (e.g. on a localhost copy) to better spot the error? How far are you in the process of implementing the old module just with core functions? Have you tried to e.g. loop over the gallery pages and extract the images text into a textfile or CSV by using the PW API e.g. from a custom template file etc.?

Edited August 27 by cwsoft

[biber]

Hi @cwsoft, thanks for your questions and your help.

You are right, I should no longer worry about the database as long as the rsult fits.

At the moment I am working with a local copy of the site in order not to publish half-finished results. I implemented variable sorting (by name, date or newest first) by visitor, and had some problems finding the right conditions, especially with the reverse part. The result I found looks a bit crazy, but it works.

So I think, we can close this topic here.