WEBVTT

00:00.000 --> 00:06.000
Working with databases in web applications.

00:06.000 --> 00:12.000
In this video, we will understand how to connect a MySQL database with a web application.

00:12.000 --> 00:17.000
Learn to create and manage database tables for user and product data,

00:17.000 --> 00:24.000
perform CRUD operations, create, read, update, and delete using MySQL.

00:24.000 --> 00:29.000
Demonstrate data persistence and front-end interaction.

00:29.000 --> 00:33.000
Now we'll learn how to connect a MySQL database with our existing website

00:33.000 --> 00:40.000
and perform full CRUD operations, create, read, update, and delete.

00:40.000 --> 00:46.000
We begin by opening Visual Studio Code and revisiting the website project we built earlier.

00:46.000 --> 00:53.000
You'll notice a new file named Khanfig.pi, which contains our MySQL connection settings.

00:53.000 --> 00:57.000
This file helps our website communicate with the MySQL server,

00:57.000 --> 01:04.000
allowing data to be stored permanently instead of temporarily in memory.

01:04.000 --> 01:08.000
Now, let's run the project and open the website in the browser.

01:08.000 --> 01:11.000
On the screen, we see the user registration page.

01:11.000 --> 01:15.000
We fill in the registration form and click the register button.

01:15.000 --> 01:23.000
The data we just entered is now being sent from the front end to the database through our back end server.

01:23.000 --> 01:27.000
Next, we open MySQL Workbench to check our database.

01:27.000 --> 01:34.000
In the SQL console, we first type the command drop database app, underscore DB, semi-colon,

01:34.000 --> 01:39.000
to reset any old data, then we run a select command on the user's table.

01:39.000 --> 01:45.000
The table displays user information, exactly the details we entered in the registration form.

01:45.000 --> 01:52.000
This confirms that our website is now successfully connected to the MySQL database.

01:52.000 --> 01:58.000
Now, we'll create a new database specifically for our crud operations.

01:58.000 --> 02:02.000
Inside MySQL Workbench, we define several tables.

02:02.000 --> 02:06.000
We create a user's table to store user details,

02:06.000 --> 02:10.000
a product's table to hold product information,

02:10.000 --> 02:13.000
an order's table to record order data,

02:13.000 --> 02:16.000
order items to list ordered products,

02:16.000 --> 02:21.000
and a cart items table to track products added to the cart.

02:21.000 --> 02:29.000
When we give the command show tables, MySQL displays all the structured tables we've created.

02:29.000 --> 02:35.000
Next, we switch back to our website and click the admin button to add a new product.

02:35.000 --> 02:40.000
We enter the product name drawing book, set the price to 45, stock quantity to 10,

02:40.000 --> 02:44.000
and choose the category under Notebook section.

02:44.000 --> 02:50.000
After saving the product, we return to MySQL and type select star from products.

02:50.000 --> 02:55.000
The structured data appears showing our new product record.

02:55.000 --> 03:00.000
This completes the create operation.

03:00.000 --> 03:12.000
When we view the table, we can see all product details neatly arranged by ID, name, description, price, category, image, stock, and date created.

03:12.000 --> 03:19.000
This step demonstrates the read operation, retrieving data from the database in a structured format.

03:20.000 --> 03:23.000
Now, let's update the product information.

03:23.000 --> 03:30.000
In our website, we open the product settings and change the price of the drawing book to 50 and the stock to 20.

03:30.000 --> 03:36.000
After saving the changes, we return to MySQL and run select from products again.

03:36.000 --> 03:46.000
The table now shows the updated price and stock values, confirming our update operation worked successfully.

03:46.000 --> 03:49.000
Finally, let's perform the delete operation.

03:49.000 --> 03:52.000
In the website, we delete the drawing book product.

03:52.000 --> 03:58.000
Back in MySQL, we run select from products and see that the table is now empty.

03:58.000 --> 04:03.000
The record has been removed completely, completing our cred cycle.