We can build websites only with HTML. But that would leave our sites static.
We can use PHP to make them more dynamic. But this will only get us so far.
We can use SQL, to build more complex systems and to manage the content on our PHP/HTML websites, without touching the code.
WordPress and all other web applications rely heavily on server databases. Most of the dynamic information shown on websites is stored in the database tables you likely host with your hosting provider. So when you visit any website, most of the content you see is pulled from the database of this website – page and blog posts content, image URLs, layout options, usernames and passwords, and so on.
To install a blank WordPress site (or any other more complex system / app / site), you will need:
- Domain name
- Server space
Domain is needed to give an identity for your website; make it easier to remember an address to visit your website. Without domain name, you would have to visit an IP address instead of domain name with your browser.
Server is needed to:
- store all files (like php files, html files, images, and other media)
- store email system (for if you need your custom email address with your domain name)
- store the databases and data
Hosting companies (e.g Cloudways, Hostinger, GoDaddy, etc) sell domains, but also server spaces where you can manage your emails, store files, and manage your database.
Basically – all the content that you see on any WordPress site, comes from the database.
How Patchstack uses SQL databases?
Everything you see in Patchstack is (in some form) stored in our databases. Our public website runs on WordPress and uses default WordPress database structure.
Our App on the other hand, is a custom-coded platform and is not related to WordPress at all. All the data inside our App is stored in our other databases. This data includes user emails and their (encrypted) passwords; a list of sites added to the Patchstack App by our users, protection logs of users’ sites, API keys, generated reports data, account settings (user timezones, billing-related information, user seats, add-ons, and volume upgrades, custom firewall rules, etc.)
So we can query for example our “users” table with SQL syntax and manage all our users and data about their sites, from our database.
MySQL
MySQL is the most commonly used database language. A code syntax, which defines how the data is queried. Alternatively there’s MSSQL, MariaDB, PostgreSQL, which all have a bit different syntax or rules.
# Example MySQL syntax that can be used in PHP.
INSERT INTO users (username, email) VALUES ('mario64', 'super@mario.com')
# This tells the server to do the following actions in the database:
# 1. Insert a new line to a "users" database
# 2. To the "username" column, write "mario64"
# 3. To the "email" column, write "super@mario.com"
# Select all data about any site from Patchstack App
SELECT * FROM sites WHERE site_id = 189283
# This returns an array of each column from the "sites" table, where site id is 189283
# Example MySQL syntax that deletes the same row
DELETE FROM users WHERE username = 'mario64'
# This tells the server to delete a row from "users" table, where username is "mario64"
phpMyAdmin
This is the most popular interface software for managing the data in the database and its tables.
On the left side menu, there’s a list of databases; Inside the databases, you can see a nested tables “City”, “Country”, “CountryLanguage”.
Your server can have many different databases. Each database can have many different tables. Each table can have many rows and columns of data.
The structure tree or hierarchy goes Server -> Databases > Tables > Data:
- Server
- Database
- Table
- Rows and columns of data
- Table
- Database