Database and Web Basics

Tasks

Create a simple database
Set up an API server
Create a webpage and deploy to a server

This project will use a LAMP stack for the web application. The goal of this application is to search Netflix titles from an API made from an database.

Technologies Used

Preview of Web App

Creating a Database

MySQL was used for the back-end database for this web application. It was installed using apt install mysql command on linux. By default, the installation process should prompt you to configure the database and accounts that will be using it. From there, mysql -u root -p command can be used to enter queries into the databases.SHOW DATABASES displays all the current databases.

Creating Database and Table

Use CREATE DATABASE [DBNAME] to create an database.

To make a table select a database using USE [DBNAME] and CREATE TABLE [TBNAME] followed by the table parameters.

Inserting records and deleting records

Inserting an entry into table can be done through INSERT INTO query. Deleting an entry is done by DELETE FROM [TBNAME] WHERE [CONDITION].

Updating records

Updating records can be accomplished by UPDATE [TBNAME] SET [VALUE] WHERE [CONDITION].

Dropping table(s) and database

To drop tables and databases, use DROP TABLE or DROP DATABASE.

Importing from CSV

Putting a csv into a table set can be completed by using SQL's LOAD DATA LOCAL INFILE query. First you must create the table on which you are placing the data into. Then specify the file location and enter the proper csv parameters.

For example, in this project, a csv file was imported from Kaggle with Netflix titles data. This file can be found here in the repository.

CREATE TABLE titles (
        ID VARCHAR(700) NOT NULL,
        Form VARCHAR(700) NOT NULL,
        Title VARCHAR(700) NOT NULL,
        Director VARCHAR(700) NOT NULL,
        Cast VARCHAR(700) NOT NULL,
        Country VARCHAR(700) NOT NULL,
        Added VARCHAR(700) NOT NULL,
        Released VARCHAR(700) NOT NULL,
        Rating VARCHAR(700) NOT NULL,
        Duration VARCHAR(700) NOT NULL,
        Category VARCHAR(700) NOT NULL,
        Descripition VARCHAR(1000) NOT NULL
);

LOAD DATA LOCAL INFILE '/var/www/html/Database-Web-Basics/netflix_titles.csv'
INTO TABLE summer
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROW

Link to SQL script can be found here in the repository.

Creating an API Server

An Rest API Server was created fron the Netflix title table in the database. The server was created using PHP to convert the MySQL table data into JSON format for an user to access.

<?php
       // API Headers
    header("Content-type:application/json");
    header("Access-Control-Allow-Origin: *");
    // Connect to Database
    $con = mysqli_connect("localhost", "root", "12345", "testdb");
    
    // If no connection, display error.
    if(!$con){
    die('Could not connect: '.mysqli_error());
    }
    
    //Select Table
    $result = mysqli_query($con, "SELECT * FROM titles");
    
    while($row = mysqli_fetch_assoc($result)){
        $output[]=$row;
    }
    
    //Output in JSON Format.
    print(json_encode($output, JSON_PRETTY_PRINT));
    
    mysqli_close($con);
?>

Link to the php file can be found here in the repository.

The API contains 12 attributes of titles on netflix.

ID - Neflix ID
Form - Movie or TV Show
Title - Name
Director - Director(s) of the film
Cast - Cast of the film
Country - Country in which it was filmed
Added - Date it was added to Netflix
Released - Year film was released
Rating - Suitability for audiences
Duration - Duration of the film
Category - Genre Tag(s)
Descripition - Short synopsis

Webpage and Deploying to a Server

To interact with the data within an API on a webpage, we must fetch it's content with a javascript function.


const loadTitles = async () => {
    try {
        const res = await fetch('http://34.121.233.142/Database-Web-Basics/public/read.php');
        titleData = await res.json();
        console.log(titleData);
    
    } catch (err) {
        console.error(err);
    }
};

We can use this data to create an event listener function where it will display the results based on the input from the user. Link the script to the html and style with css.

For deployment of the web app, Google Cloud Platform was used as a hosting service. The platform allows virtual machine instances to be run on their network. This particular web application is hosted on a linux(ubuntu) VM instance running an apache server.

All project files were created in the server directory on the linux machine. A html file lies in the parent folder for when a client enters the server it redirects to the public folder where the web app's main contents reside.

The apache server address is http://34.121.233.142

jon-michael-c/Database-Web-Basics