**Install MySQL for Database Functionality**
To add a database to your app, you'll need to install MySQL. MySQL is a popular open-source database system that is very commonly used in web applications.

Install MySQL:
- [ ] Open the Node.js command prompt again and navigate to your application's folder by typing `cd YourAppName` and pressing Enter.
- [ ] Then, type `npm install mysql` and press Enter.

This will download and install the MySQL Node.js module, which provides the functionality you need to interact with a MySQL database from your app.

Once you've installed MySQL, you can start creating and interacting with databases in your application.

You'll need to add code to your app to define the structure of your databases, connect to the databases, and read/write data.

This will involve learning about SQL (Structured Query Language), the language used to interact with many types of databases, including MySQL.

---

**Set up MySQL Connection in app.js**
To interact with a MySQL database from your app, you need to set up a connection.

Load the MySQL module:
- [ ] Open "app.js" in VS Code and add the following line of code below `const app = express();:`

`const mysql = require('mysql');`

This line of code loads the MySQL module, which you installed in the previous step.

Create a MySQL connection:
Next, you'll create a connection to your MySQL database. Add the following lines of code below `app.use(express.static('public'));:`

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'earthcare',
  password: 'password',
  database: 'gpt_chatbot'
});

This code creates a connection to a MySQL database running on your computer. Make sure the host, user, password, and database fields match the settings of your MySQL database.

In this case, we're connecting to a database named "gpt_chatbot" as a user named "earthcare".

- [ ] Save your "app.js" file.

Your app is now set up to connect to a MySQL database! You'll use this connection to interact with your database.

This involves sending SQL (Structured Query Language) commands over this connection to create, read, update, and delete data in your database.

As you continue building your app, you'll add more code to "app.js" to carry out these database operations.

---

**Setup MySQL on Your Computer and Install VS Code Extension**
To interact with a MySQL database from your computer, you'll need to set up MySQL locally and install a MySQL extension in VS Code.

Install MySQL locally:
- [ ] Click the Start button on your desktop and search for `MySQL command line`. If it doesn't appear, it means you need to install MySQL. You can download and install MySQL from its official website. Follow the instructions provided on the website to install MySQL.

Visual Studio Code has an extension that provides support for MySQL. Here's how to install it:
- [ ] Open VS Code.
- [ ] Click on the Extensions icon in the Activity Bar on the side of the window. It looks like a square inside a square.
- [ ] In the Extensions view, enter `MySQL` in the search box and press Enter. In the search results, find the MySQL extension. It should be listed as "MySQL" and the publisher should be "Jun Han".
- [ ] Click the Install button next to the MySQL extension.

With MySQL installed locally and the MySQL extension installed in VS Code, you're set up to interact with MySQL databases directly from your code editor.

You can use the VS Code MySQL extension to connect to your databases, browse and edit data, run SQL queries, and more.

---

**Create the MySQL Database and User**
Now that you've set up MySQL on your computer and in your app, it's time to create the MySQL database and user that your app will use.

Open MySQL command line:
- [ ] Click the Start button on your desktop, type "MySQL command line" in the search bar, and click on the MySQL command line app to open it.

Create the MySQL user:
In the MySQL command line, type the following line of code and press Enter:
`CREATE USER 'earthcare'@'localhost' IDENTIFIED BY 'password';`

This command creates a new MySQL user named "earthcare" with the password "password". Replace 'earthcare' and password' with one of your choice.

Create the MySQL database:
Next, type the following line of code and press Enter:
`CREATE DATABASE gpt_chatbot;`

This command creates a new MySQL database named "gpt_chatbot". Your app will store its data in this database.

Grant privileges to the user:
Now, you'll give your new user full access to your new database. Type the following line of code and press Enter:
`GRANT ALL PRIVILEGES ON gpt_chatbot.* TO 'earthcare'@'localhost';`

This command grants all privileges on the "gpt_chatbot" database to the "earthcare" user. This means the user can perform any action on the database, like creating tables, inserting data, and deleting data.

Flush privileges:
Finally, type the following line of code and press Enter:
`FLUSH PRIVILEGES;`

This command tells MySQL to reload the user privileges. This ensures that the changes you've made take effect.

Congratulations! You've set up a MySQL database and user for your app. Your app can now connect to this database and use it to store and retrieve data.

As you continue building your app, you'll add code to interact with this database, like inserting data, retrieving data, and deleting data.

---

**Connect the MySQL Database to VS Code**
With your MySQL database set up, you can now connect it to VS Code. This will allow you to interact with your database directly from your code editor.

Add a MySQL connection in VS Code:
- [ ] Open VS Code and press `Ctrl+Shift+P` to open the command palette.
- [ ] Type `MySQL Add Connection` and press Enter. A form will open where you can input your MySQL server's details one by one:

Host: localhost
User: earthcare
Password: password
Database: gpt_chatbot

Once you've entered these details, click "OK" or press Enter. You should now see your MySQL connection in the MySQL section at the bottom of the Explorer in VS Code.

Troubleshooting connection errors:
If you encounter an error saying that `you're not authorized`, it might be because of the authentication method used by your MySQL user.

You can change the authentication method to "mysql_native_password", which is supported by the MySQL extension in VS Code.

- [ ] Open the MySQL command line and type the following lines:
`ALTER USER 'earthcare'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';`
- [ ] Finally, type the following line of code and press Enter:
`FLUSH PRIVILEGES;`

You're now ready to interact with your MySQL database from VS Code! You can use the MySQL extension to run SQL queries, browse and edit data, and more.

As you continue building your app, you'll add code to interact with this database, like inserting data, retrieving data, and updating data.

---

**Create a Table in the MySQL Database**
Now that you have connected to your MySQL database, you can create tables to store your app's data.

Select the database:
- [ ] Open the MySQL command line by clicking the Start button on your desktop, typing `MySQL command line` in the search bar, and clicking on the MySQL command line app to open it.
- [ ] In the MySQL command line, type the following line of code and press Enter to select your database:
`USE gpt_chatbot;`

This command tells MySQL that you want to work with the "gpt_chatbot" database, which is the database you set up for your app.

Create a table:
Next, you will create a table in your database. This table will store your app's data.

- [ ] Type the following lines of code in the MySQL command line, and press enter only after you typed in all of the lines:

CREATE TABLE products (
  id INT AUTO_INCREMENT,
  name VARCHAR(100),
  price DECIMAL(7,2),
  amount DECIMAL(4,2),
  status VARCHAR(100),
  description VARCHAR(250),
  PRIMARY KEY (id)
);

This command creates a new table named "products". Here are the meanings of the SQL data types used in this command:

* INT: An integer data type.
* AUTO_INCREMENT: A keyword that automatically increments the value in the column for each new record.
* VARCHAR(100), VARCHAR(250): A variable character string data type. The number in parentheses specifies the maximum length of the strings that you can store in the column.
* DECIMAL(7,2), DECIMAL(4,2): A decimal number data type. The first number in parentheses specifies the total number of digits that can be stored in the column, and the second number specifies how many of these digits can be after the decimal point.
* PRIMARY KEY: A keyword that specifies the column (or columns) that uniquely identify each record in the table.

Feel free to adjust these column definitions to match the data you want to store in your table.

Congratulations, you've created a table in your MySQL database!

As you continue building your app, you'll add code to interact with this table, like inserting data, retrieving data, updating data, and deleting data.

---
**Insert Data into the MySQL Table and View It in VS Code**
Now that you have your MySQL table created, you can start adding data to it and view this data directly in VS Code.

- [ ] Insert data into the table:
- [ ] Open the MySQL command line. To add a new row of data to your table, type the following command, then press Enter:

INSERT INTO products (id, name, price, amount, status, description)
VALUES (1, 'Product 1', 99.99, 10, 'Available', 'This is product 1');

This command adds a new row to the "products" table with the specified values. The `INSERT INTO` command specifies the table and the columns you want to insert data into. The `VALUES` keyword is followed by the data you want to insert, in the same order as the columns you specified.

View the data in the table through VS Code:
To view the data you've just inserted, you can use the MySQL extension in VS Code:

- [ ] Open VS Code.
- [ ] Click on the MySQL icon in the Activity Bar on the side. You should see your MySQL connection in the sidebar.
- [ ] Click on your MySQL connection to expand it. You should see a list of your databases.
- [ ] Click on the "gpt_chatbot" database to expand it. You should see a list of your tables.
- [ ] Right-click on the "products" table and select "Select Top 1000". You should see the data in your table displayed in a new tab.

Now you can see the data in your "products" table directly in VS Code. You can refresh this view to see new data as you add it to your table.

You can repeat the INSERT INTO command as many times as you want to add more data to your table. Just remember to change the values each time so you're not inserting the same data over and over.

As you continue building your app, you'll add code to interact with this table, like inserting more data, retrieving data, updating data, and deleting data.

---

**Use the MySQL Table Data in EJS**
Now that you have data in your MySQL table, you can use this data in your EJS templates.

Modify the route handler in "app.js":
- [ ] Open your "app.js" file in VS Code.
- [ ] Look for the `res.render('YourTemplateName.ejs'); `line in the route handler where you want to use the table data.
- [ ] Replace this line with the following code:

connection.query(
  'SELECT * FROM products',
  (error, results) => {
    res.render('YourTemplateName.ejs', {products: results});
  }
);

This code sends a `SELECT * FROM products` SQL query to your MySQL database, which retrieves all rows and columns from the "products" table.

The results parameter of the callback function contains the rows returned by the query, which are passed to the EJS template as an object named "products".

Display the data in the EJS template:
Next, you'll display this data in your EJS template.
- [ ] Open your EJS template file in VS Code (replace 'YourTemplateName' with the name of your file)
- [ ] Add the following code where you want to display the data:

<% products.forEach((product) => { %>
  <li>
    <%= product.id %>
    <%= product.name %>
    <%= product.price %>
    <%= product.amount %>
    <%= product.status %>
    <%= product.description %>
  </li>
<% }) %>

This code uses the forEach method to iterate over each item in the "products" array.

For each item, it creates a list item (`<li>`) that displays the product's id, name, price, amount, status, and description.

The` <% ... %> `syntax is used for JavaScript code that doesn't produce any HTML output, and the `<%= ... %>` syntax is used for JavaScript code that produces HTML output.

The product variable represents the current item in the "products" array for each iteration of the forEach loop.

- [ ] Save your files: After making these changes, save your "app.js" and EJS template files.

Your app is now set up to pass data from your MySQL database to your EJS templates, and display this data in your HTML.

As you continue building your app, you can modify this code to query different tables, pass different data to your templates, and display different data in your HTML.

---

**Adding a Form to Post Data**
You will add a form in your `top.ejs` file, which users can use to submit a question.

- [ ] Open the `top.ejs` file in your VS Code.

Adding a Form to Post a Question:
You will add the following lines to your `top.ejs` file:

<form action="/chatlog" method="post">
  <p>Enter your questions here.</p>
  <input type="text" name="question">
  <input type="submit" value="Ask">
</form>

This form will contain a text input field where users can type in their questions and a submit button. When the user clicks the submit button, the data will be sent as a POST request to the `/chatlog` route.

- [ ] Save your changes and close `top.ejs`.

---

**Adding a New Route to Log Chat Data into MySQL**
You will add functionality in your application to log chat data into your MySQL database. Now, let's add new changes to the `app.js` file:

- [ ] Open the `app.js` file in your VS Code.

Adding a New Route to Receive POST Requests:
You will add a new route below your existing `app.get('/')` route. This route will handle POST requests at `/chatlog`. Add the following lines to your `app.js` file:

app.post('/chatlog',(req,res) => {
    console.log(req.body.question);
    
    connection.query(
      'INSERT INTO faqs(question)VALUES(?)',
      [req.body.question],
      (error, results) => {
        res.redirect('/');
    )
});

This route will receive the question that the user submits through the form in the `top.ejs` file.

It will then use `connection.query` to insert the question into the `faqs` table in your MySQL database.

After that, it will send you to the page routed with "/" to ensure that after sending the form, you won't accidently reload it, and do the `INSERT INTO` function all over again.

- [ ] Save your changes and close `app.js`.

---

When this is all set up, the form on your website will allow users to submit a question. The submitted question will then be stored in your MySQL database through the `/chatlog` route in your Node.js application. This will allow you to accumulate and analyze user questions for future improvement of your services.