KingWarin.de

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:

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.

Cheers
Sven

Ahoy,

so last time I created the basic login script, pretty much no hassle there…

While creating that script I also set up the first sql-Statement and a simple dbconnect.php script.
Well, simple is almost to much, the file literally only consisted of the standard opening and closing php tags and furthermore one additional line
$con = new PDO('mysql:host='.HOST.';dbname='.DB, USER, PASS);

Didn’t look that clean and not at all satisfying to me, so while I wanted to continue with the script which will be used to create categories I felt the urge to do something about that piece of code inside of that dbconnect-File.
So I took that file and created a new class Connection which houses a private PDO-connection and several public functions that will take care of the sql-statement related stuff.
– Huge plus, if I’m going to use another db, like postgres or even such crazy stuff like a Lotus Notes database I just have to switch out that connection class, not much hassle inside of the other scripts.
(Just kidding about that Lotus Notes stuff, I don’t know why one should go through the pain of using Notes voluntarily, but I got a professional background on working with Lotus Notes, so why not toss it in)

So I took the whole statement stuff that was living inside of the login-function (located within the helpers) and turned that into the first public function getActiveUserByMail which sets out a SELECT statement to the database and tries to retrieve a user by the given eMail and return the result as an associative array.

Having done that first thing to try is whether the login still works. Besides tackling some php-errors that originated from forgetting to create a constructor-function (and missing the n on that function after creating it), everything worked out pretty nice.

So I could go on creating the next public function createCategory which I think doesn’t need further explaining.
The processing script is just taking in the POST variables, verifying they are set and passing them to the createCategory function.
Along comes a small form for the creation, this, again, is not a big thing. A simple php-File which checks whether the user is allowed to see that page and redirects to the login-page if he is not.
If the user is allowed to create new categories he’ll see a small form where he can set the category name and select the parent category (if any).
As it really is that simple, it wasn’t a surprise that everything worked nice and clean on first try.

Right now I’m adding some more functions to the connection class, mainly the functions that come to my mind at the moment because I’m a little stuck with writing the scripts/forms and need to get all other stuff out of my head.

So long
Sven

Ok, I start to get a feeling of stupidity, but I guess that’s ok.

Yesterday I told you about how I forgot to add a users-Table and showed the updated graph with the added table etc.
Now there comes the next point… As I feel obliged to add at least some security I’ll spice it up a little by adding a salt field to the users.

The salt will be used while cooking up the hash in the login process, we’ll see some of that later on.

Another thing is I created a github-Repo where I will subsequently create the project.
Sometimes it might be possible to see improvements and/or new code earlier than on this blog.
As of writing this, the repo mainly contains the Readme, a small leaflet containing some notes and the sql-statements needed for creating all tables (so far).
Annotations are welcome…
Repo can be found On my GitHub Account

Right now I’m starting on the first PHP part by writing a small login script (which I think shouldn’t be much of a hassle…)

More to come soon.

Cheers
Sven

You could just say D’Oh…

Starting to create the tables on the sql-database I realised I missed one essential table:
Users
Obviously there has to be a place where I store the users (or authors) that are allowed to write posts.

So added a new table Users with fields:

• userId
• userAlias (also known as Nickname)
• userMail
• userPass

So db-overview now looks like this:

Ahoi,

just like said in the last post I needed to do some refinement on the multilanguage part of the database design.
So I added another table Content which will hold the actual articles per language, in order to get it nice the heading and content moved from Articles to this new table. Also there is now a languageId in this table which is used to distinct between all available languages.

LanguageId is part of the second new table Languages which will accommodate the available languages. It simply consists of the languageId, the language (aka the display-string) and an languageIcon.

Here’s an update overview-image

Not much for today but I hope to keep it coming…

So long,
Sven

Ok, so starting the development first step (at least for me) is quite obvious.
Think about the data handling and data storage.

As the primary project goal is learning PHP, this step is somewhat small, but still it’s needed.
My first thought some time ago today was „What tables and which fields do you need?“
So I furiously started scribbling on a piece of paper and soon realised what was drawn on the paper is not what I need.
It was a great mess trying to get all the stuff that is possible in the design, instead I should start low and focus on what I really need in order to get the base functionalities.

Well, what is the baseline in terms of functionality a blog needs? Obviously the blog-posts (or articles), so one table for the articles might be a good start.
Of course that table needs some fields. Again the question, what is the baseline? That’s an easy one – Headline and Content should do the trick, plus a unique id for internal useage.
Ok, that’s a little bit to basic, so add a creation date, author (maybe sometime I want to invite someone to participate on the blog, who knows…)
More advanced, sometimes there is not enough time to finish a new post straight, so maybe a status for „draft/published“ should be available, so add that too.
Last there is a feature I want for the blog that could should be taken into account too:
Multilanguage support. So for now there will be two fields for the headline (german and englisch) and also two fields for the content.
Yes, this is something that definitely should be refactored and yes I shouldn’t wait too long before refactoring as this is ugly, hardcoded and not easily extensible, but for today I’ll leave it just like it is.

Now we can display articles, but wait, maybe there should be a refinement in favor of organizing several blog-posts together. Of course I’m talking about categories, so add another table for the categories. This is a very simple table, as it just needs three fields: Category-Name, Category-Id and advanced a Parent-Category-Id.

Not much of a fuzz, so what’s next?
Do you need comments? Well, I don’t really need them, but it would be rather nice to have the feature, so there is table number three.
What is the baseline? Of course an unique id (like quite always). Next a Name (single field, just a nickname maybe?) and the comment itself.
I’d like to know if a comment is old or new, so creation date is a must! And sometimes I might want to contact the user who left a comment, so add a field where an emailaddress can be added.
Maybe said user want’s to share his own page/blog with me (and obviously the world), so why not adding a field for website?

Ok, looking sweet and easy so far – still we need a place where we can define in which categories each article is contained; same for the comments.
This leaves me with a basic db design displayed in the following pic.

Like said, I have to refactor the multilanguage part of the articles table. I’m already thinking about the possible solutions (and which might be the best or at least most suitable for my needs/wishes).

So long.