Create CRUD Application using Python and MySQL connector

Show following options

1. List All Records
2. Delete
3. Insert
4. Update
5. Search
0. Exit

Write separate function for each functionality

Program should not get exited unless user select 0 option.

You can use any module to connect to MySQL from python

A user or admin facing problem managing data on excel sheet.
He/She need a system to perform at least following operations

1. Add Record
2. Modify Record
3. Delete Record
4. Show Listing and Search Data to get specific Information.

User need a system which should be accessible from internet so that he can work from any machine (laptop/desktop/mobile).

You need to develop a web application with best of your knowledge

Roles: Admin

With correct credentials admin should be able to login and see the dashboard.

if credentials are wrong he will stay on login page and show a message – wrong credentials.

On successful login admin can see users list perform all CRUDL operations.

NOTE: you need to use vim editor to edit files

Following are the wireframes for reference.

---

---

---

---

Technologies to be used

• composer for package management and autoload
• ini for configuration
• git and gitlab for version control
• HTML5 CSS3 Bootstrap 5 for UI/UX
• jquery 3.6 or javascript for validation and AJAX
• php 7.4 or 8 as backend programming language
• mysql 8 database
• PDO for database operations
• PHPUnit for unit testing
• python and php for automation script (Use cron jobs to automatically run script)
• nginx web server
• use infinityfree / webserver / cloudserver for website hosting
• Jenkins and git-ftp for CI/CD

MVP / Deliverable

1. P0
   1. Users Listing
   2. Delete User Record
   3. Add User Record with Profile Picture
      (User status should be enum in database table: enable, disable, blocked, active, inactive)
   4. Update User Record
   5. Session Management Login / Logout
2. P1
   1. View User Details in Modal Window
   2. Pagination
   3. Sorting
   4. Searching
   5. Filtering
3. P2
   1. Frontend – Backend Validation
   2. Export CSV Users
   3. Bulk Upload CSV
   4. Activity Log
   5. Export Activity Log
4. P3
   1. Login with OTP i.e. 2FA (Use Redis to store OTP)
   2. Login Logout for user account
   3. Inactive User status if not logged in for 3 consecutive days
   4. Change Admin and User Password from their respective account
   5. Secret Questions and Forgot Password / Recover Password using secret questions or through the email link
5. P4
   1. REST API (Web Services) for User CRUDL Operations
   2. Protect REST API using Basic Authentication or JWT token
   3. Login with google API or Facebook API
   4. PHPUnit test for all functionalities
   5. Licensing or limit user registration
6. P5
   1. Dashboard showing following summary (Use highcharts)
      1. Total User
      2. Active Users
      3. License Count / Usage Count
      4. Online Users
      5. Weekly Registration Stats

Schema

Ref SQL Queries

SELECT * FROM users ORDER BY id DESC;

SELECT * FROM users WHERE id = 3;

DELETE FROM users WHERE id = 3;

INSERT INTO users (id, name, age, city, added_at, updated_at) VALUES (NULL, 'sonam gupta', 18, 'gorakhpur', NOW(), NOW());

UPDATE users SET name = 'Sonam Gupta', age = 20, city = 'Gorakhpur', updated_at = NOW() WHERE id = 5;

Reference

PHP MySQL CRUD App

Php CRUD Application – How to Create Website Using Php – YouTube

Linux Commands

SQL Cheat Sheet

HTML and CSS

Php Fundamentals

Php Basics Tutorial

Php Advanced Tutorial

Javascript Tutorial in Hindi

Javascript Tutorial in English

Create a project user management system

Roles: Admin

With correct credentials admin should be able to login and see the dashboard.

if credentials are wrong he will stay on login page and show a message – wrong credentials.

On successful login admin can see users list perform all CRUDL operations.

Following are the wireframes for reference.

---

---

---

---

---

Deploy war on heroku

Signup and select java as primary language https://signup.heroku.com/

Download heroku cli from https://devcenter.heroku.com/articles/heroku-cli

heroku plugins:install java

heroku login

Open another terminal or gitbash and run following command

heroku war:deploy <path_to_war_file> --app <app_name>

---

Reference Code: https://gitlab.com/tcet/advanced-java

Reference YouTube videos

1. Introduction to Servlet
   
2. MySQL DATABASE CRUDL
   
3. Servlet Methods – GET, POST, PUT, DELETE and OPTIONS
   
4. DATABASE CONNECTION JDBC MySQL
   
5. Show MySQL Data in HTML Table
   
6. Session Management – Login / Logout
   
7. DML – Insert Update Delete
   
8. Bootstrap Integration in JSP
   

Creating a personal mini car inventory system from scratch using your best knowledge and skills. The system will have inventory of manufacturer and models (cars) of each manufacturer owned.

System should be created using your own frontend and backend framework and should use caching.

Frontend should be a webapp a.k.a. SPA. (Ie: no refreshing/reloading pages)

Technology to be used:

PHP (OOP)
MySql (Normalized Database Schema)
Javascript (JQuery – AJAX)
HTML, CSS and Bootstrap 5

Classes to be created:

Database – Class to deal with each and every operation of database.
Manufacturer – Class to deal with all operations related to car manufacturer.
Model – Class to deal with all operations related to car model.

Page 1: Add Manufacturer.
The page should contain a input box for manufacturer name and a submit button.

Page 2: Add Model.
This page should have a manufacturer dropdown on the right side and model name textbox on the left side (Both should be in the same line)

Add other details below about the car like “Color, manufacturing year, registration number, note and 2 pictures”. Pictures should be uploaded using any ajax plugin.

And lastly there should be a submit button.

Page 3: View Inventory.
This page should populate a table of all the models and manufacturers from the DB.

It should have the columns as below

Serial Number, Manufacturer Name, Model Name, Count

eg.

1. Maruti WagonR 2
2. Tata Nano 1

On clicking on the row, a popup will appear which will have details of the individual models like color, manufacturing year etc. (Basically all details from page 2) and a Sold clickable link.

On clicking Sold, the row will be deleted and the DB will be updated accordingly.

In addition to this, on Page 3 when users are viewing the inventory and in case a car is sold, the View Inventory table for the column Count will have to be dynamically updated in case a car is sold at that moment.

Similarly in case the users are on any other page or pop up of the system, they should get an alert saying “make model is sold” when a car is sold.

Wireframes

DATABASE Schema

Code for reference
https://github.com/shaileshsonare/mcis