Behind The Scenes Of Our New Website & Database

Behind The Scenes Of Our New Website & Database

We show you how our new Website works, making changes in the database and seeing the results real time in the browser.

We just launched our brand new Website and haven't yet built a nifty content management system so it's a bit of a hack to add content.

It occurred to us this was an ideal opportunity to show how the Database is used to build the Website content. We share how we have designed the database to allow for articles, teasers and even multiple categories.

Our current tools are mySQL and TablePlus.

Even during our development work we are still using Raspberry Pi's. All our website work and our database are all run off a Raspberry Pi 4 before we put any new changes live.

Our staging server is a Raspberry Pi 4 which runs the Web Server and Database. This always has the most up to date content and functionality. We access it on our local network and everyone can make changes, and look at each other's changes.

When we are happy with our new content and features we excitedly release it to the world by pushing it to our live server which is hosted elsewhere.

The database we use is called MySql and we mainly administer it using the shell/terminal but for this video we are using a client application called Table Plus.

A database is made up of tables, the first table we look at is called posts and represents articles or posts we have in our database.

A table has columns sometimes called fields. The columns have a name and a type, the type is usually text, a number, or date but there are others. For example, our posts table has a column called title which contains text (the title of a post/article).

The content of the table is stored in rows, one entry per row. We looked at the contents of our posts table and picked out various column values we had.

The very words you are reading as we type we are written using Markdown.

## Markdown

The very words you are reading as we type we are written using *Markdown*.

Markdown is a simple markup language. This is then turned into HTML for the web browser and you'll see the 2 hash signs are turned into headings and the asterisks produces italics.

The body column of the posts table is where we store the main content of a post/article. Of course the Database doesn't know it's Markdown, it will store any text we give it.

Tables often have columns to store unique id numbers. This can link rows from one table to another which is how our categories work.

posts

id title
27 article a
28 article b

Each row in posts has an id column. We have a category table containing all our categories (projects, electronics etc.) and each of those also have an id column.

categories

id name
2 electronics
3 hacking

We have another table called category_post which we can use to link posts to categories using these id numbers.

category_post

id category_id post_id
1 2 23

So if we have a post with id=27 and a category with id=2 we can put those values into the category_post table to link that post and category together.

If we want multiple categories per post no problem it's just another row entered into the category_post table.

If you want to know more we can show you anything you like. More of the database, some SQL queries perhaps, HTML, JavaScript, reach out and tell us what you are interested in.

We are adding more content and functionality all the time, the site is already different to when we began this article.

We are working on a back end content management system (CMS) to simplify the process of adding content as it's pretty clunky to do this directly through the Database using a shell session.

Finally please spread the word about our new Website, we have such a small budget there is nothing to spend on marketing. We enabled Ads on our youtube channel to help pay for equipment for our tutorials so it would be great to get new subscribers and more hits. Many thanks from the GurgleApps team.