Much of the groundwork has been done already for this project. This post is going to focus on making things more sustainable and organized for the long term. Before this post I had been storing information for each site in the spreadsheet on my computer mentioned in the archival process series of posts. From there I would manually update a table each time I found a new site. This works well enough but I would often forget to include a site to the Sites page. With a database I can have the Sites page update automatically whenever site info is added or modified. A database works much the same way as a spreadsheet but it is stored on the web server and can send data to webpages.
Creating a Database
I’m not going to go into too much detail here because there are tons and tons of resources for people learning how to use MySQL. Frankly, 100% of my knowledge on how to use databases comes from the resources at MySQL Tutorial and random Stack Overflow questions. But generally, here is the process of using databases:
- Install MySQL if it isn’t installed already (WordPress requires it)
- Tighten the security of MySQL if it is going to face public traffic
- Create a database
- Create a table and define its columns
- Import CSV data from spreadsheet
- Create a public user to handle queries
- Write PHP code to send queries and receive data
Querying a Database from a WordPress Page
I want to preface this section with the same concession as everything else I share on this site: there are probably certainly better ways to accomplish this task but I could only think to do it this way based on previous experience.
You would think that WordPress, being written in PHP, would make it easy to insert PHP code of your own into webpages. After a lot of banging my head against the wall I read that WordPress explicitly does not allow this. Methods that I had recycled from previous projects and that I knew to work were causing bizarre errors and output on the page I had never seen before.
After more research and a failed attempt using JavaScript I learned that there are ways to insert PHP code using WordPress Plugins. I try to avoid using plugins where possible because it can introduce issues and I prefer to understand the code I use, but in the end convenience won out. The Code Snippets plugin looked like exactly what I needed and it has a large user base so it shouldn’t be abandoned any time soon.
I then created a new Snippet for the table query script and started coding away. The script is comprised of a function declaration which defines the PHP code for a database query and a function call to add the declaration to WordPress’ Shortcode library. I hadn’t heard of Shortcode until I started researching queries but it’s actually really interesting. Much like standard HTML tags, Shortcode follows a similar structure. Whereas <example>Hello!</example> would create an example tag in a webpage for the text Hello!, Shortcode’s [example]Hello![/example] would call a PHP function example with the parameter Hello!.
With the database query function implemented in Shortcode I then added it to the Sites page using the editor. Easy enough, right? If you have never written code before, just know that it is never right on the first try. I did a decent amount of debugging where I fixed mistakes such as: not putting my include statement with DB credentials in the scope of the function, not properly checking if the input wants alive sites or dead ones, forgetting to change variable names from code that I had copy-pasted, and forgetting a trailing single quote to mark the end of a link in an <a> tag. By far the scariest error occurred when I had messed up the syntax of the database query. Thankfully it wasn’t a huge deal
With the code fully fleshed out, the only step left was to actually use it. In the page editor I included the new Shortcode [build__table]alive[/build__table] (but with only one underscore or it will add the table here, too) and it actually displayed the content I had intended. There was a slight issue where it would occur at the top of the page preceding everything else. I assume this is because it executed the PHP before the page had entirely loaded. I solved this problem by simply cutting the HTML content of the page and pasting it in the Shortcode definition. It’s a simple solution but a little uglier than I would have liked.
That’s the final code, with some minor modifications so it would fit in one screencap. The content of the page is generated by having the PHP interpreter echo valid HTML tags with content that I have either hard coded in or content that is being pulled from the database. I also added a call to the get_lastpostmodified() function included with WordPress so that I don’t have to manually update the Sites page with the date whenever I modify something.
One more thing to note: I messed something up so that whenever I publish something WordPress says that it failed due to some kind of JSON error, but the new post is visible on this site anyway. Not sure what to do about it, if anything. It went away (along with the unintentional table) after I added the second underscore in the Shortcode example above, so I guess that was the problem. Lesson learned: always make sure to be careful when adding code where it is not meant/expected.