WEB DEVELOPMENT

How to Add a Database to a Website

A complete solution with step-by-step instructions

David Littlefield
Level Up Coding

Image by Ruslan Zaplatin

Summary:

The main benefit of reading this article is that you will learn how to host a database on a web server, connect your website to the database, and perform the four basic operations that are used to manage persistent data. This will allow your website to store, organize, access, and filter every type of data that can be tracked about your customers, products, and business. However, this will require you to know what data you want to track, and configure the tables, queries, programming logic, and interface to track it.

We will use Linode as our cloud service provider because their products are easy to use, affordable, scalable, and their customer service is outstanding. We will use Nginx as our web server because their product is free, handles high website traffic, and powers 60% of the top 100k websites in the world.

# How to Put a High Performance Website on the Internet:
01. How to Host a Website in the Cloud
02. How to Protect a Website From Threats
03. How to Optimize a Website to Load in 1 Second
04. How to Add a Database to a Website
05. How to Protect a Database from Threats
06. How to Backup a Database in the Cloud
07. How to Scale a Website to Handle High Traffic
08. How to Add a Node.js Application to a Website
09. How to Add a Python Application to a Website
10. How to Add a Payment Method to a Website

Create the Virtual Machine:

The virtual machine is an emulated computer system with its own CPU, memory, storage, and network interface that exists on the physical hardware of our cloud service provider. This allows us to install our web server, host our database, and upgrade our hardware as needed for a small monthly cost. In this section, we will create the virtual machine that will contain our database using the least expensive monthly plan available.

# create virtual machine
1. create an account on linode
2. click "create" button
3. click "linode" menu item
# select linux distribution
1. click image dropdown menu in "choose a distribution" section
2. click "ubuntu 20.04 LTS" menu item
# select datacenter to store virtual machine
1. click region dropdown menu in "region" section
2. select same region as your virtual machines from before
# select monthly plan
1. click "shared cpu" tab in "plan" section
2. click "nanode 1gb" radio button
# finish virtual machine
1. scroll down to "linode label" section
2. enter "database-1" into "linode label" text field
3. enter password into "root password" text field
4. click "create linode" button
5. wait until linoide finishes
6. click "power on" link
7. click "power on linode" button
# create private ip address
1. click "network" tab
2. click "add ip address" button
3. click "private" radio button
4. click "allocate" button
# write down private ip address of database
1. scroll down to "ip addresses" section
2. write down "ipv4 – private" ip address
# restart "database-1" linode
1. click "reboot" button
2. click "reboot linode" button

Install the MySQL Database:

The MySQL Database is a relational database management system that organizes data into tables that are protected using access control. This allows us to control which users can create, request, update, or delete the data that’s stored in our database. In this section, we will install MySQL, configure our basic security settings, and enable remote connections.

# open console as root user on linode
1. open "linodes" page on linode
2. click "database" linode
3. click "launch lish console" link
4. press "return" key
5. enter "root" into "login" prompt
6. enter your password into "password" prompt
7. press "return" key
8. paste commands into console
# update package information
sudo apt-get update
# install mysql
sudo apt-get install --yes mysql-server
# enable firewall
sudo ufw enable
# allow mysql to bypass firewall
sudo ufw allow mysql
# open mysql directory
cd /etc/mysql/mysql.conf.d
# allow mysql to receive connections from any ip address
sudo sed "s|127.0.0.1|0.0.0.0|g" -i mysqld.cnf
# set mysql to run at startup
sudo systemctl enable mysql
# restart mysql
sudo systemctl restart mysql

Prepare the MySQL Database:

The MySQL Client is a command-line program that provides the frontend interface to manage our database. This allows us to manipulate the data in our database by executing SQL statements interactively, or by executing them from a text file. In this section, we will download and execute our text file to create our database, table, and data that we’ll load into our website.

  • The password must contain mixed-case letters, a number, and a symbol.
# download mysql file
curl -o /etc/mysql/database.sql https://gist.githubusercontent.com/david-littlefield/74266b53347d2605fac6f53a27277f4c/raw
# store database name
database="website"
# store table name
table="records"
# change "placeholder" to unique username
user="placeholder"
# change "placeholder" to desired password
password="placeholder"
# add database to mysql file
sudo sed "s|#database_placeholder#|$database|g" -i /etc/mysql/database.sql
# add table to mysql file
sudo sed "s|#table_placeholder#|$table|g" -i /etc/mysql/database.sql
# add user to mysql file
sudo sed "s|#user_placeholder#|$user|g" -i /etc/mysql/database.sql
# add password to mysql file
sudo sed "s|#password_placeholder#|$password|g" -i /etc/mysql/database.sql
# open mysql as root user
sudo mysql -u root -p
# run mysql file
source /etc/mysql/database.sql
# exit mysql
exit

Replace the Static Website:

The Static Website is a website that displays the same content to all of our users. It serves the web browser with HTML files that are pre-built using hardcoded text, and CSS and JavaScript files that are stored on our web server. This type of website is best for sites that feature content, update infrequently, and provide non-interactive or non-personalized content.

The Dynamic Website is a website that displays unique content to all of our users. It serves the web browser with HTML files that are generated in real-time using server-side languages, and CSS and JavaScript files that are stored on our web server. This type of website is best for sites that manage content, update frequently, and provide interactive or personalized content.

The website that we’ve hosted, protected, and optimized, is a static website that’s helped us learn each of these topics individually, incrementally, and with minimal complexity. It also allows us to do a performance comparison between static and dynamic websites. In this section, we will change our static website into a dynamic website by simply loading a different php file.

# open console as non-root user
1. open "linodes" page on linode
2. click "website-1" linode
3. click "launch lish console" link
4. press "return" key
5. enter your unique username into "login" prompt
6. enter your password into "password" prompt
7. press "return" key
8. paste commands into console
# switch from static website to dynamic website
sudo sed "s|index static.php|index dynamic.php|g" -i /etc/nginx/nginx.conf
# restart nginx
sudo systemctl restart nginx

Connect the Website to the Database:

The Configuration File is a php file that contains our MySQL credentials that our website needs to connect to our database and execute queries. It uses our credentials to make the connection, store the connection, and specify how to handle errors. In this section, we will store our credentials by adding our database name, ip address, user, and password to our configuration file.

  • It uses a server-side language that isn’t visible from the web browser
# change "placeholder" to database name from earlier
database="placeholder"
# change "placeholder" to ip address of database
host="placeholder"
# change "placeholder" to database username from earlier
user="placeholder"
# change "placeholder" to database password from earlier
password="placeholder"
# add database to configuration file
sudo sed "s|#database_placeholder#|$database|g" -i /var/www/html/includes/configuration.php
# add host to configuration file
sudo sed "s|#host_placeholder#|$host|g" -i /var/www/html/includes/configuration.php
# add username to configuration file
sudo sed "s|#user_placeholder#|$username|g" -i /var/www/html/includes/configuration.php
# add password to configuration file
sudo sed "s|#password_placeholder#|$password|g" -i /var/www/html/includes/configuration.php

Select the Records in the Database:

The SELECT statement is an SQL statement that retrieves the records from one or more of the tables in our database. This allows us to request one, some, or all of the records by combining it with other SQL statements. In this section, we will request all of the records, generate the HTML with the data from those records, and render the HTML as content on our website.

# load website without cache
1. open chrome web browser
2. click "..." menu
3. click "more tools" menu item
4. click "developer tools" menu item
5. click "network" tab
6. check "disable cache" checkbox
7. enter your domain name into address bar
8. press "return" key

Insert a Record into the Database:

The INSERT statement is an SQL statement that inserts one or more records in a table in our database. This allows us to add records by providing data for one, some, or all of the columns in a table. In this section, we will parse the data from the text fields on our website, create more data with the parsed data, download the related image, and add the data to to database.

# add image to "recent" page
1. click "upload" tab
# enter unsplash url into "unsplash url" text field
https://unsplash.com/photos/UoqAR2pOxMo
# enter location into "location" text field
Da Nang, Vietnam
# enter description into "description" text field
The Non Nuoc beach is located at the foot of the Marble Mountains and extends over 5 km. This beach has calm waves and crystal clear blue water all year round. You can also eat locally caught fresh fish at one of the restaurants. It is also an ideal place for sports such as surfing, windsurfing, volleyball, etc.
# create record
1. click "upload" button

Update a Record in the Database:

The UPDATE statement is an SQL statement that updates one or more records in a table in our database. This allows us to change one, some, or all of our records by combining it with other SQL statements. In this section, we will parse the data from the text fields on our website, parse more of the data from the current record object, and change the data in our database.

# open "edit" page
1. click "Da Nang, Vietnam" image
# remove description
1. select description from "description" text field
2. press "delete" key
# enter description into "description" text field
Son Tra peninsula is located about 8 km from the city center and has many beautiful beaches such as But beach, Tien Sa beach, Nam beach, Rang beach, Bac beach and Con beach. These beaches are all very beautiful at the foot of mountains with jungle and clear blue sea. Apart from relaxing on the beach and swimming, you can also go into the jungle, visit pagodas, ride a scooter around the peninsula and snorkel.
# update record
1. click "save" button

Delete a Record from the Database:

The DELETE statement is an SQL statement that deletes one or more records from a table in our database. This allows us to remove one, some, or all of our records by combining it with other SQL statements. In this section we will parse the record id from the query parameter in the URL, remove the related image, and remove the data from our database with the record id.

# open "edit" page
1. click "Da Nang, Vietnam" image
# delete record
1. click "delete" button
# measure performance with gtmetrix
1. open gtmetrix
2. enter your domain name into "enter url to analyze" text field
3. click "test your site" button
4. scroll to "page details" section
5. review "fully loaded time" metric

Review the Database Process:

The Database is manipulated by our website with a chain of different classes that are stored in multiple php files. This process can be traced in the php files that are stored on our web server. In this section, we will customize and review the php file that demonstrates a consolidated version of this process.

# add database to demo file
sudo sed "s|#database_placeholder#|$database|g" -i /var/www/html/demo.php
# add host to demo file
sudo sed "s|#host_placeholder#|$host|g" -i /var/www/html/demo.php
# add username to demo file
sudo sed "s|#username_placeholder#|$username|g" -i /var/www/html/demo.php
# add password to demo file
sudo sed "s|#password_placeholder#|$password|g" -i /var/www/html/demo.php
# change "placeholder.com" to your domain name
domain_name="placeholder.com"
# view url to demo php file
echo $domain_name/demo.php
# open demo php file in web browser
1. copy url to demo php file
2. paste url into address bar
3. press "enter" key

Next Steps:

The following articles contain a gem by themselves but a treasure together.

# How to Put a High Performance Website on the Internet:
01. How to Host a Website in the Cloud
02. How to Protect a Website From Threats
03. How to Optimize a Website to Load in 1 Second
04. How to Add a Database to a Website
05. How to Protect a Database from Threats
06. How to Backup a Database in the Cloud
07. How to Scale a Website to Handle High Traffic
08. How to Add a Node.js Application to a Website
09. How to Add a Python Application to a Website
10. How to Add a Payment Method to a Website

No responses yet

What are your thoughts?