How to create a Time Ordered Blog Archive Using
mySQL, PHP, HTML, and JavaScript
Michael James Scharen

michaelsbookcorner.com
November 15, 2021

It is frustrating trying to track down living examples for any web development code close to what we need. There is plenty to aid in building up skills, but yet another example will not hurt.

First of all, I would prefer to be selling books! Be that as it may, to forego the Amazon.com route with the extremely generous cut of the profits, you must do it yourself. As a caution, there need not be any coders out there who fear me and my coding skills. I will concede the match right here and now. I do have a few skills, but mostly a tenacious capacity for finding the information needed on the internet. I urge other novice programmers to look there also. For me, not even knowing what the keywords were at first for finding what I needed, the task was daunting, to say the least. That is why I am now and in the future wish to offer what tidbits might be helpful. Needless to say there are likely much simpler methods than what I have proposed, but finding them is not easy. All that work cannot be for naught!


Fig.2a - Five most recent blog articles.

To back up briefly, let’s just say that there is much more time involved in my endeavor than money. The website is free hosted by Infinityfree.net. This is great, but as they say, you get what you pay for. By that, I mean that support is close to non-existent for free accounts in most places. What you get are forums and chat rooms where people flail about with their questions until some sympathetic person can help them. This article is an attempt at payback for all of the help I have received. At the time of this writing, it is still unclear to me whether I have access to databases I can create on Infinityfree, or if that is for pay accounts only. I hope to have further updates. This article will concentrate on the mechanics for now. There were many rounds of trying to connect with databases on Infinityfree, even though that is where my website is hosted – but I have been unsuccessful.


Fig.2a - Top portion of blog archive listing page.

Fig.b) Lower portion of blog listing page illustrating the month-heading separators for easier reading.

In terms of free alternatives there are the following. First, one could host one’s own database somehow, just as one could host one’s own mail server or website. MySQL and MySQL Workbench is open source software that can be obtained at the MySQL website among other places. That requires a rather solid guarantee of up-time and/or a dedicated machine to make it happen. Virtual Private Servers may be had for $5-10 per month. The alternative is finding free hosting for mySQL which allows remote access. The solution I found is remotemysql.com. Thus far, I am happy with this solution until other areas gain traction. Now, as a warning, this site is noted to be more for the purposes of development – not production – meaning there may be up-time issues. Their website make this clear. Such issues one can live with for a while. As stated, so far, no complaints. It is in their best interest to maximize up-time so I will let it go at that. The other issue with remotemysql.com is that only one database is allowed with a scrambled name they give it. As this is in the background, most website visitors will never know the difference. Thus far, the number of tables does not seem to have any limitation nor how they are connected. This project only needs one table, in any case.

This project was begun without any knowledge of MySQL so there was the first-timers fear hurdle to get over. There are plenty of YouTube videos out there for learning MySQL. My favorite, thus far, is from WebDev Simplified. This young guy Kyle has a very easy, straightforward way of teaching and the code he types is actually readable on your screen! What a concept. I have watched quite a few of his programs on javascript, css, or any number of areas I needed help with. So first of all, get over the scary part and walk through some videos doing some practice code. My personal recommendation with MySQL is start learning the Query Commands and what they mean. A database is an amazingly powerful tool! I prefer the Query Commands over trying to enter stuff through little boxes in myPHPAdmin. MyPHPAdmin boxes have a great value later for editing, but we’ll get to that.

There is something called mySQL Workbench, which I began setting up, but for some unexplained reason, crashed. This I will tackle later. MyPHPAdmin information can be entered either though filling in the boxes, or by the Query Commands. Why they call it Query instead of Command, I do not know. So in MyPHPAdmin find the tab labeled SQL, second tab from the left. Click that and there will be a text editing field below where the Queries may be entered. A query is entered, of any complexity, with the Go button sending the command. This article is not meant to be a lesson on mySQL, javascript, HTML, or PHP. I’m not an authority on any of those. This purpose of this article is to provide an stake in the ground as a practical example for something that works and that newbies like me can build on. Others may provide what css or other tricks are needed for their particular requirements.

As stated in the outline of the problem, a table is created with all relevant and possibly relevant information about a blog post. The purpose is to present elements that might inform the visitor further on the article or be used later. I tried to list the most useful information as returning to back-fill a data table later does not sound appealing. Listed below is the code used to set up the table, blogPosts.

CREATE TABLE blogPosts (
id INT NOT NULL AUTO_INCREMENT,
title VARCHAR(255) NOT NULL,
dateFancy VARCHAR(255) NOT NULL,
datePlain VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
topic VARCHAR(255) NOT NULL,
abstract TEXT NOT NULL,
keyword1 VARCHAR(255) NOT NULL,
keyword2 VARCHAR(255) NOT NULL,
keyword3 VARCHAR(255) NOT NULL,
keyword4 VARCHAR(255) NOT NULL,
keyword5 VARCHAR(255) NOT NULL,
thumbURL VARCHAR(255) NOT NULL,
postFile VARCHAR(255) NOT NULL,
postURL VARCHAR(255) NOT NULL,
PRIMARY KEY(id)
);

The names of the fields, hopefully, are self-explanatory with a few exceptions. I have two dates entered called datePlain and dateFancy. All entries in the table are text, by the way. The field dateFancy is for dates written out as October 27, 2021. With the month written out + comma, these are what are displayed in the post listing. The other entry, datePlain is entered in the compact form, 2021-10-27. This could also be displayed, but for this exercise it is used for parsing the archived list of posts by time -- separated by month/year headers. All October posts will appear between the October, 2021 header and the September, 2021 headers to help the visitor zero in on the month more easily and to break up the listings somewhat. Refer to Fig 2. above. As one can see in the table set-up, all data is entered as text and all are VARCHAR save for the abstract which might be a bit longer. I preferred to display the dateFancy for the listing page rather than datePlain.

Next, the table, blogPosts, needs to be populated with data. To do this, the mySQL query similar to this is written into the SQL field as shown. I kept copies of these in a text file so I could cut and paste later and make sure I didn’t mess things up! The veterans can do this in their sleep, no doubt.

************************************

INSERT INTO blogPosts (title, dateFancy, datePlain, author, topic, abstract, keyword1, keyword2, keyword3, keyword4, keyword5, thumbURL, postFile, postURL) VALUES ('The Cruel Solar Winds', 'November 1, 2021', '2021-11-01', 'Michael J. Scharen', 'Physics', 'How the magnetosphere protects from cosmic ray particles.','magnetosphere', 'aurora', 'synchrotron', 'cosmic rays', 'magnetic field', '../blogThumbs/2021-11-01.jpeg', 'post-2021-11-01.html', '2021/post-2021-11-01.html'),

('The One Book Everyone Must Read', 'September 21, 2021', '2021-09-21', 'Michael J. Scharen', 'History', 'Examination of the book -- Government: Indicted -- by Marc Stevens proving government is a criminal enterprise', 'propaganda', 'society', 'government', 'mind control', 'authority', '../blogThumbs/2021-09-21.png', 'post-2021-09-21.html', '2021/post-2021-09-21.html'),

('Propaganda Coup: The Roman Empire Never Died','September 17, 2021','2021-09-17','Michael J. Scharen','History', 'How and why Vespasian and the Roman Empire invented Christianity','Roman Empire', 'propaganda', 'Flavius Josephus', 'Vespasian', 'Christianity', '../blogThumbs/2021-09-17a.jpeg', 'post-2021-09-17a.html', '2021/post-2021-09-17a.html'),

('The Moon Does Not Orbit the Earth', 'September 17, 2021', '2021-09-17', 'Michael J. Scharen', 'Astronomy', 'Dispelling the lazy language which states that the Moon orbits Earth or that Earth orbits the Sun.', 'orbits', 'center-of-mass', 'science', 'short cut', 'moon', '../blogThumbs/2021-09-17b.jpeg', 'post-2021-09-17b.html', '2021/post-2021-09-17b.html'),

('Starlink Terms of Service', 'August 22, 2021', '2021-08-22', 'Michael J. Scharen', 'Government', 'Commentary on Elon Musk’s statement that no Earth governments would hold sway in SpaceX colonies on Mars.', 'anarchy', 'Mars', 'colony', 'SpaceX', 'freedom', '../blogThumbs/2021-08-22.jpeg', 'post-2021-08-22.html', '2021/post-2021-08-22.html');

************************************

INSERT INTO blogPosts (title, dateFancy, datePlain, author, topic, abstract, keyword1, keyword2, keyword3, keyword4, keyword5, thumbURL, postFile, postURL) VALUES ('What is a Conspiracy Theory?', 'July 13, 2021', '2021-07-13', 'Michael J. Scharen', 'Society', 'No conspirators need to be identified. All it takes is for someone to point out the obvious contradictions in public policy to be branded.','conspiracy', 'observation', 'propaganda', 'critical thinking', 'ostracism', '../blogThumbs/2021-07-13.jpeg', 'post-2021-07-13.html').

('Appeal to Authority', 'June 2, 2021', '2021-06-02', 'Michael J. Scharen', 'Propaganda', 'Having credentials and having actual knowledge are often two different things.','logical fallacies', 'critical thinking', 'public trust', 'truth', 'fiction', '../blogThumbs/2021-06-02.jpeg', 'post-2021-06-02.html', '2021/post-2021-06-02.html'),

('Electrons are Not Like Planets', 'September 13, 2021', '2021-09-13', 'Michael J. Scharen', 'Physics', 'Dispelling the persistent notion that electrons circle atomic nuclei like planets, per the popular diagrams','atomic nuclei', 'electrons', 'orbitals', 'education', 'science', '../blogThumbs/2021-09-13.png', 'post-2021-09-13.html', '2021/post-2021-09-13.html'),

('Pure Neutronium', 'October 04, 2021', '2021-10-04', 'Michael J. Scharen', 'Physics', 'Article in response to question on Quora.com for what results might be had for mining neutron stars -- if we could.','neutron star', 'space mining', 'Star Trek', 'science fiction', 'neutrons', '../blogThumbs/2021-10-04.jpeg', 'post-2021-10-04.html', '2021/post-2021-10-04.html');

************************************

This is quite a mouthful, but represents the population of the table in different steps or groups headlined by the INSERT INTO blogPosts (…) containing the names of all of the fields in the table. The VALUES lists, in parentheses, are the actual data values for each post which follow in the same order as the field/column names from the INSERT INTO portion. The table name we are inserting into is blogPosts. Any number of entries can be listed with VALUES (), (), separated by commas as shown here (ended by semi-colon). Above are three separate queries for adding to the table with the first a group of five, then another group of five, then two more.

So now that we’ve got a handy-dandy mySQL table out there on the inter-webs, what can we do with it? The table needs to be accessed by the website/page so we can manipulate the data. For the purposes of this article and especially for learning, all of the code will be displayed in the open, such as php connection and javascript. I’m well aware there are ways to include a separate config or connect.php file with server and password info for hooking a php file up with the database. I am leaving that out of this discussion for the sake of clarity and brevity (such as it is). For the naysayers, that is why this connection code is on our primary page. Connecting to the database at remotemysql.com requires code looking like this. I placed it at the beginning of a .php file for the archive page.

php $db = mysqli_connect('remotemysql.com','O2YC3uWggl','byfzdUsabc','O2YC3uWggl') or die('Error connecting to MySQL server.');

In the parentheses, the first slot is for the mySQL server. The second is the user name with the third being the user’s password. The last slot is the name of the database. Other places one can give a meaningful name but not at remotemysql.com. Then launch into the page as normal.


Fig.3 - First portion of archive page indicating the php code for connecting to the database and table blogPosts as indicated above.

Then, just into the tag for our archive page is this powerful nugget. The connection is now open to the table. This code runs a query which pulls in all records from the table blogPosts in DESCending order by date – the field datePlain, as described. We could get just the latest five by adding LIMIT 5 after DESC or pick out any set of articles by month with an offset, etc.. There are lots of possiblities. The variable $rows[] is appended with the data from each individual $row until complete. Next is changing the php variable $rows into a javascript array variable we can play with.


Fig.4 - Code just after body tag to read data from the mySQL table called blogPosts.

The entire code will be available, but the most important bit is this included at the beginning of the script. The console.log() statements are a good sanity check that we are getting the data we need.


Fig.4 - First line of code inside JavaScript translates data from PHP to javascript array variable called table. This is all the data brought in by the previous php routine from mySQL table blogPosts.

So now we have the deceptively simple two dimensional array variable named table in javascript which contains all of the information we need, or as much as we’ve requested. The syntax for this data is the following in the array table[][]. The first [] elements are the numbered records containing the data for each blog post. Table[0][*] is everything we have on the first table record returned. In our case this is the latest post we have since it is sorted in reverse order by date. It could be any record depending on how we made the query to sort. With twelve records we have them ordered 0, 1, 2, … 10, 11. The second dimension contains all of the attributes in order for that particular blog post. These include, title, author, abstract, date in the order entered into the database and numbered in that order. In this case title[0][14] refers to the local URL or path to find the post html file. This is to be injected into the html anchor tag generated for the page to call up the post.

Output the Blog Post Listings

To generate our archive listings, each post must be analyzed in turn along with the next one in the chain. We want to know from one listing to the next if the month of publication changes so that a month header can be inserted at the proper point. For that reason, the while loop only goes to (table.length – 1) so we don’t run out of next-entries.

There needs to be a header right away before the first post summary is displayed, so this is a special case. The javascript code allows for handling this where the counter still is zero. Immediately afterward a post summary is generated with the associated functions.

Next, with no upcoming month changes, the information is displayed as created by the function which creates a p element, then replaces the innerHTML with a string made up of HTML elements and data values taken from the table such as title, date, URLs for the post and a thumbnail image src.

The last if statement looks for changes in the month so that month/year headers can be placed in the proper place between posts of subsequent months. As mentioned, the loop only goes to the second to the last post in the list since at the end there is not next post to compare with. The last post is then generated as a single statement to end the output.

Wrapping Up

One can be very creative with how the listings are output. I have key words in my table but have not utilized them as yet. Part of the reason why I torture myself is that I was tearing my hair out trying to deal with WordPress. I’m sure there are WP gurus out there by the hundreds of thousands who swear by it, but WP just did not seem flexible or forgiving to do what wish to do. (Since I don’t always know what that is.) As such, I have to teach myself various tricks as I go along and I’m sure I am not the only one who feels this way.

The code is included here with the passwords and database names masked. There are plenty of comment points and console.log()s to help debug any issues, but that takes time to track everything down. Patience is a virtue they say. Was Patience a web developer?

Download blog Archives page code.

Download