Some settings

Today I wanted to start creating the actual blog-site, so what a visitor will see when entering the blog.

Opening vim and adding a new index.php file for editing I realized, just after inserting the opening and closing php-tags that I might create something like a settings-page on the admin-panel first.
Why? one could ask. Well, first of all is also first thing that came to my mind when thinking about the index-page: the blog-title.
Yeah, I kind of forgot all that settings stuff until now, including such simple things like the blog-title, a blog-description and similar stuff. I haven’t figured out yet what should be part of the settings, but I’ll start it simple and keep adding parts whenever I feel they need to be in there…

So I decided to drop the index.php and go on with doing just that and create that one part first and yes, this also includes a little database work because I have to store the settings somewhere.

Let’s begin with that db part. As I’m actually writing this post while coding and figuring out the bits and pieces some parts might seem a little confusing, sorry for that.
If I’m using a settings table, what should the structure be?
Hammering on my keyboard without pre-thinking I ran into the first „problem“ after typing
CREATE TABLE IF NOT EXISTS `settings` ( `title` which really isn’t that much.
But right here comes my problem that stopped me from hammering in the next columns, e.g. „description“.
Yes, the problem is right there in that sentence, it’s the columns… If I keep going with that approach I’ll need to update the table-structure every time I want to add a new setting and also the table wouldn’t hold more than one entry – ugly.
As there is a running WordPress-instance here I decided to take a look at how the settings are handled here. Opening the database it was pretty obvious that the table I’m looking for is wp_options, which has four columns, namely option_id, option_name, option_value and autoload. As of now I don’t know what the autoload-column is used for, id, name and value are pretty self-explanatory.
I think I’ll just take the WordPress approach and go for a table of that structure but with an additional display-column in which the name of the column for the settings-page will be stored (yes, it’s actually more of a description).

Fast and easy, so next I’ll create two new methods, getSettings and setSettings on the Connection class.
I think there is nobody who needs explanation what the getSettings-method is doing, even the setSettings leaves no doubt what it’s doing.
For the set I went with a INSERT-statement, but with an added ON DUPLICATE KEY UPDATE, so I can use the same statement for adding as well as updating the settings.

As the settings-table has a display-column the settings-page can be created mostly within a simple loop.
So I fetch all available settings from the database and create a new line inside of a form for each entry where the fields name will be the settingId, display will be used as label and the value will be inserted.
Add a surrounding form-tag, a submit- and dismiss-button and we’re good to go.

On the processing script the incoming values will look something like:

1: Random Title
2: since 2017
4: This is a blog about coding

where the key is the settingId and the value is the value (obviously…)
So I’ll loop over the contents of $_POST (which feels ugly and somehow wrong) and append an array with id and value to a settings array.
The settings-array is then passed to the setSettings function and the user will be redirected to the admin-panel.

As the database-structure changed (and there are some changes that were just described) here is an updated graph of it:
Current database-schema

Last but not least I did some cleanup on the ConnectionSELECT-statements with PDO::lastInsertId and switched some other SELECTs from PDO::prepare to PDO::query as there’ve been some where the prepare just doesn’t make any sense.


Leave a Reply