KingWarin.de

For now there is not much error-handling implemented for all those database related functions, which is really bad.

Say I want to create a new blog post with content in German and English which is also associated to the category „General“, this results in three sql-statements that have to be triggered and are partly dependent on the results of each other.
First we create a new article in the database, for creating the content we need the ID of the new article and for creating the association of article and categories we also need the articleId. But do we want to create parts of all this if other parts fail or do we want to see an error message and no changes on the database.

At the moment there is literally no error handling. If the execution of a statement fails the following statements are not executed and the user will see a simple message that states that there was an error while trying to do that statement.
I tried to improve this by returning the error message which is returned from PDO::errorInfo; while implementing this I realized that this will work fine if there are no dependent queries or return value is boolean for success but in every other case I’ll still run into problems…

One problem is that I haven’t utilized transactions in the first place and therefore I might leave the database with obsolete, redundant and/or incomplete data.
To change this I added transactions for the parts of the code where I do have those dependent queries. This is alright as I keep my database a little cleaner, but as a trade off I now have a PDO::beginTransaction right before I try to execute the first query and one PDO::rollBack in each error-case (which typically is part of an if-else) plus an additional PDO::commit if every query was successful.
For the first case where I started to implement this the result was having one beginTransaction, one commit and three rollBack-statements. Furthermore returning the error-messages from errorInfo for this case meant switching most of the queries from returning data in the success-case and a boolean on errors to returning data on success AND data (error messages are strings too) on errors too.

First thought: Switch from associative arrays to objects for the return values of the database-queries. Well, this is an approach, but I still got to check for the return value type on each error-case and still keep those redundant rollBacks.
Then a sudden realization occurred: Why on earth am I not using exceptions?

Yeah, that could have been taken into account previously, but I just didn’t think about it earlier and, as the last PHP Version I was working with was PHP4, I didn’t even knew there are exceptions in PHP. Funny things occur, the previous day I read an article on PDO which featured some examples with exceptions.

So I threw away the changes I made today, which was not much of code, and will be redo this with exceptions and maybe also with objects for the return values.

Cheers
Sven

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

As my development machine starts to die and I’m currently setting up the replacement I came up thinking of what is necessary to port the blog-project to another system.

To have it as convenient as possible an installation-script would be nice, but there are some prerequisites that have to be met beforehand.
A script for creating new users is one of these.

So today I started off by creating a new form in the admin area which can be used to add a new user. Here a username, email, password and a salt have to be entered.
To verify there are no typos the password and the salt have to be repeated.
As can be seen on the addUser-function, on adding the user to the database the password and the salt will be sha256-hashed and afterwards both hashes are combined, hashed again and that’s what will be set as pass in the database.

Another thing that was living on my todo for quite some time now and I wanted to move today was parents for categories.
So I took the existing form for creating new categories and replaced the dummy parent textfield with a select-box where the options will be filled with the results of the getCategories-call and an empty option at the beginning in case the new category shouldn’t be a child-category.
This added the need for checking whether the parent-parameter is none and adding the new category with NULL as parent.

After creating a new category without parent and another one that was supposed to have a parent I took a look at the database and saw… no parents at all.
How come? Well, I placed a $parentId = NULL; in the first line of the createCategory-function as the parent-selection was not implemented yet.
So removing the line and adding a new category with another category selected as parent the next call to the database showed the category with the previously selected parent.

Cheers,
Sven

Today’s goal was adding the functionalities for setting and unsetting categories on each individual article.

I started off with what would be the first thing that a user could interact with, so obviously I’m talking about the checkboxes on the create article form. To achieve this I first of all had to create a small function in DBConnect which simply returns all available categories, it’s just a simple SELECT that returns all entries in the categories-table.
Displaying the categories is not much more than a foreach-loop that creates div’s for each available category (div’s because I already got some styling wandering around my head).

Done that I had to improve the processing of the create-form. This again is a small loop (this time a while-loop) that was added to the create_entry.php-File and just calls the new linkCategoriesToArticle-DBConnect-function.

Ok, creating articles and setting the categories on the fly works now, so next step is handling the categories on updating/editing existing articles.
So now I need to now which categories are already set for the article the user is editing, this means it’s time for another DBConnect-function „getCategoriesForArticle“ which is another SELECT, enhanced by a simple JOIN that returns all categories that are already linked to the article with the given id.
On the edit-form I added the same foreach-loop that is used on the creation-form, but in this case the checkboxes for the already set categories will be pre-checked (which is what I needed the new function for).
On the processing script I also added the same loop as for creating new articles, but to be able to unlink categories again I also call to the getCategoriesForArticle-function and check whether all of this categories are still activated for the article. If there are differences the newly selected categories will be added and the unselected categories will be unlinked for the article.

In total not much of a hassle and didn’t took to much time to implement.

So long
Sven

Ok, last time I was stuck a little, today I had a day off from work which I used to spend some more time on this project.

So what has happend?
In brief:
I created several forms and the scripts for processing that forms. The forms are mainly for creating and updating the blog-posts (or „articles“), creating categories and adding languages.

I started out creating the small and easy forms and scripts, more accurately I started with the form for new languages. This is absolute low-level as it consists only of two fields, one for the language name and another one for the path to the language icon (although I might have to figure out something better for the icons).
Processing is similiar, check if both values are set and insert them into the corresponding database-table (which is languages).
Those two files were created within some minutes and working fine, not much of a problem here.
Well, working on some other parts I realized that I should take a little care of errors. As I’m not quite sure on how I want to check whether a language is already existing when trying to create a new one I, for now, went with displaying all existing languages at the bottom of the create form.

Creating categories is also a small and not very exciting form so I won’t spend much words on this, although there is one thing I haven’t done yet (but it’s already on my todo).
At the moment, when creating a new category there is no possibility to specify whether there should be a parent category. This is something I will take care of soon.

Creating new blog posts
I started this one with some headaches. First shot just had a field for the articles heading (which will be used only for internal reasons) and a create and dismiss button.
Hitting the create button the new article was created in the database and the user was redirected to a page where he could create the content for one language.
For a first shot this was ok No it wasn’t, I’ve just been lazy. So I’ve almost immediately redone that and added the form for creating the first language content into the original form for creating the article itself and enhanced that form with a checkbox the user could set to immediately create another language-content after the article was created.
But this still means jumping from one page to another and to the next and that’s something I usually don’t like and therefore I want to avoid.
So I pretty much ended up redoing the form again until it was what it is now.
That is a form where the user must add the (internal-) heading and all possible languages are displayed right away. It’s the users choice to create the content for as many of those languages right away.
This is not a good looking form at the moment, but I already got some ideas on that.

Next I wanted to proceed with editing the articles. So I started to write on a form for editing. Again first shot was the absolute minimum and only displaying the heading and the content fields.
While I always had to access that form by typing in the url and specifying the articleId in the url-bar of the browser I decided to intercept the writing of this form by creating a small page where all existing articles will be displayed and can be opened for editing by navigating through the admin-area.

Said that I first created the function that fetches all the articles information from the database. Displaying that data is no sorcery. A simple foreach-loop iterating over the articles array returned by the getArticles-Function that outputs an div displays the heading, the current status, creation date and the userId of the user that created that entry (which will be enhanced so the username is displayed instead of the id) and a link to get to the edit form.

Done that I went back to the edit form.
Ok, this already has some fields but it’s not the state I wanted to achieve, so I added another foreach loop that iterates over the existing contents and displays them in the form, making them editable in place.
But there still is the possibility that onenone or more new language contents should be created.
The first shot just drew another line in the form with a heading and content textfield and a select-box for language.
Closing with the already known „Do you want to create another language-content?“-checkbox.
Still not exactly what I wanted, so go on Sven…
I need to know for which languages there is no content created for the current article, so I opened up my MySQL-console and started to figure out what statement I’d need to get the data I want.
Well, here comes my first real problem. Having spent quite some time on that I’m stuck with an ugly SELECT-SUBSELECT-statement at the moment (see below), but I’m quite sure the same result can be obtained with some sort of JOIN, but I’m just not getting there right now.

SELECT l.languageId, l.language, l.icon FROM languages l
WHERE l.languageId NOT IN
(SELECT c.languageId FROM content as c WHERE c.articleId=:aId)
My ugly SELECT-SUBSELECT… If someone has ideas on a nicer solution feel free to contact me

Ok, with that SUBSELECT stuff I’ll get the result I want, so I can go on.
I now added another loop that creates blank form-lines with language pre-selected if there are any languages without content left. These lines do have a checkbox which indicates whether that content should be saved or not and all of this works pretty fine.
But it’s not good looking at all (but optics are something I’ll work on in a later stage of development), but again, I already got some ideas for that.

Ok, that’s what we got so far, some stuff is still missing (e.g. specifying the categories for the articles, creating users, managing comments,…), but most of the basic (admin) functionality is already there (which is quite surprising for me). I guess next steps I’ll take on are specifying the categories on articles and user creation.

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