How to Add a Database to a Website

adminMarch 5, 2023

0 81 7 minutes read

How-to create a website with a database backend

YOU ARE READING: How to Add a Database to a Website AT Vccidata_En

Create a virtual machine:

The virtual machine is an emulated computer system with its own CPU, memory, storage and network interface that resides 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 hold our database using the cheapest monthly plan available.

# Create virtual machine1. Create an account on Linode2. Click “Create” button3. Click on the “Linode” menu item# Select Linux distribution1. click the image drop-down menu in the “Select Distribution” section2. Click on the “ubuntu 20.04 LTS” menu item# Select the data center to store the virtual machine1. In the Region section, click the Region2 dropdown menu. Select the same region as your virtual machines from# select monthly plan1. In the Plan section, click the Shared CPU tab2. Click the Nanode 1GB radio button# Quit Virtual Machine1. scroll down to the “Linode Label” section2. Enter “database-1” in the “linode label” text box3. Enter the password in the “Root Password” text box4. Click the Create Linode button5. wait for linoide to finish6. Click “Turn on” link7. Click the “Turn on Linode” button# Create a private IP address1. Click “Network” tab2. Click the Add IP Address button3. Click the “Private” radio button4. Click the “Assign” button.# Write down the private IP address of the database1. scroll down to the “IP addresses” section2. Note the IP address “ipv4 – private”# restart “database-1” Linode1. Click “Restart” button2. Click the Restart Linode button.

Install the MySQL database:

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

Reading: How-to create a website with a database backend

# Open the console as root on Linode1. Open the Linodes page on Linode2. Click on “Database” linode3. Click the Launch Lish Console link4. press the “Return” key5. Type “root” in the “login” prompt6. Enter your password in the “Password” prompt7. press the “Return” key8. Paste commands into console# update package infosudo apt-get update# install mysqlsudo apt-get install -yes mysql-server# enable firewall sudo ufw enable# allow mysql to bypass firewall sudo ufw allow mysql# open mysql directorycd /etc/mysql/mysql.conf.d# allow mysql , to receive connections from any IP addresssudo sed “s|127.0.0.1|0.0.0.0|g” -i mysqld.cnf# set mysql to run at startupsudo systemctl enable mysql # restart mysqlsudo systemctl restart mysql

Preparing the MySQL database:

The MySQL client is a command line program that provides the front-end interface for managing available from our database. This allows us to manipulate the data in our database by executing SQL statements interactively or from a text file. In this section we will download and run our text file to create our database, spreadsheet and data that we will load into our website.

The password must be case sensitive, a number and an icon.

# download mysql filecurl -o /etc/mysql/database.sql https://gist.githubusercontent.com/david-littlefield/ 74266b53347d2605fac6f53a27277f4c/ raw# save database namedatabase=”website”# save table nametable=”records”# change “placeholder” to unique username user=”placeholder “# Change “placeholder” to desired passwordpassword=”placeholder”# Add database to MySQL filesudo sed “s|#database_placeholder#|$database| g” – i /etc/mysql/database.sql# Add table to MySQL filesudo sed “s|#table_placeholder#|$table|g” -i /etc/mysql/database.sql # User to MySQL file addsudo sed “s|#user placeholder#|$user|g” -i /etc/mysql/database.sql# add pass word in mysql filesudo sed “s|# password_placeholder#|$password|g” -i /etc/mysql/database.sql# open mysql as root usersudo mysql -u root -p# run mysql filesource /etc/mysql/database.sql# exit mysqlexit

Replace the static website:

The static website is a website that provides all our users with the same content indicates. It feeds the web browser with HTML files pre-built with hard-coded text, CSS and JavaScript files stored on our web server. This type of website is best for websites that contain content, are updated 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 feeds the web browser with HTML files generated in real-time using server-side languages, as well as CSS and JavaScript files stored on our web server. This type of website is best suited for websites that manage content, update it frequently, and provide interactive or personalized content.

The website we have hosted, protected and optimized is a static website dedicated to us each of these topics individually, incrementally and with minimal complexity helped in learning. It also allows us to compare performance between static and dynamic websites. In this section, we’ll change our static website to a dynamic website simply by loading a different PHP file.

# Open console as non-root user1. Open the Linodes page on Linode2. Click on “website-1” linode3. Click the Launch Lish Console link4. press the “Return” key5. Enter your unique username in the “Login” prompt6. Enter your password in the “Password” prompt7. press the “Return” key8. Paste commands into console# switch from static website to dynamic websitesudo sudo sudo sed “s|index static.php|index dynamic.php|g” -i /etc/nginx/nginx.conf# restart nginxsudo systemctl restart nginx

Connect the website to the database:

The configuration file is a PHP file that contains our MySQL credentials that our website needed to connect to our database and run queries. It uses our credentials to establish the connection, save the connection, and specify how to handle errors. In this section we store our credentials by adding our database name, IP address, user and password to our configuration file.

It uses a server-side language that is not visible to the Web browser

# Change “placeholder” to the database name from earlierdatabase=”placeholder”# Change “placeholder” to the IP address of the database host=”placeholder”# Change “placeholder” to database username from earlieruser=”placeholder”# Change “placeholder” to database password from earlierpassword=”placeholder “# Add database to configuration file sudo sed “s|#database_placeholder#|$database|g” -i /var/www/html/includes/configuration.php# Add host to configuration filesudo sed “s|#host_placeholder#|$host|g” -i /var/www/html/includes/configuration.php# Username to KO Add configuration filesudo sed “s|# user_placeholder#|$username|g” -i /var/www/html/includes/configuration.ph p# Add password to configuration filesudo sed ” s|#password_placeholder#|$password|g” -i /var/www/html/includes/configuration.php

Select the records in the database:

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

# Load the website without a cache1. Open Chrome web browser2. Click “…” menu3. Click on the menu item “Additional tools”4. Click on the menu item “Developer tools”5. Click “Network” tab6. Check the Disable Cache box7. Enter your domain name in the address bar8. Press the “Return” key

Insert a record into the database:

The INSERT statement is an SQL statement that inserts one or more records into a table in our database. This allows us to add records by providing data for one, some, or all 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 associated image and add the data to the database.

# Add image to “last” page 1. Click the Upload tab# Enter the Unsplash URL in the Unsplash URL text boxhttps://unsplash.com/photos/UoqAR2pOxMo# Enter the location in the “Location” text box Da Nang, Vietnam# Enter a description in the “Description” text boxNon Nuoc Beach is located at the foot of the Marble Mountains and stretches for 5 km. This beach has calm waves and crystal clear blue water all year round. You can also eat fresh local fish in one of the restaurants. It is also an ideal place for sports such as surfing, windsurfing, volleyball, etc.# Create a record1. Click the “Upload” button.

Update a record in the database:

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

# Open the edit page1. Click on the image “Da Nang, Vietnam”#Remove description1. Select Description from the Description text box2.Press “Del” key#Enter description in the “Description” text boxSon Tra Peninsula is 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. Not only can you relax on the beach and swim, but you can also go to the jungle, visit pagodas, ride a scooter around the peninsula and snorkel.# update record1. Click the “Save” button.

Deleting 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 them with other SQL statements. In this section, we parse the record ID from the query parameter in the URL, remove the associated image, and remove the data with the record ID from our database.

# Open the edit page1. Click on the image “Da Nang, Vietnam”# Delete record1. Click on the “Clear” button.# Measure performance with GTmetrix1. Open gtmetrix2. Enter your domain name in the text field “Enter URL for analysis”3. Click the Test Your Website button4. Scroll to the “Page Details” section5. Check the “Full load time” metric

Check the database process:

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