Support the ongoing development of Laravel.io →
posted 1 month ago
Laravel

carlx1101 liked this thread

1

To implement the learning process feature where progress is tracked and displayed, you will indeed need to store this information in a database. Here's a guide on how to design this feature using MySQL:

Database Design

  1. Tables:
    • users: Stores user information.
    • courses: Stores course information.
    • sections: Stores sections information within courses.
    • lessons: Stores lesson information within sections.
    • user_course_progress: Tracks the progress of users in each course.

Table Structure

users

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);

courses

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT
);

sections

CREATE TABLE sections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

lessons

CREATE TABLE lessons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    FOREIGN KEY (section_id) REFERENCES sections(id)
);

user_course_progress

CREATE TABLE user_course_progress (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    lesson_id INT NOT NULL,
    completed BOOLEAN NOT NULL DEFAULT FALSE,
    completion_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (lesson_id) REFERENCES lessons(id)
);

Implementation Steps

  1. Track Lesson Completion:
    • When a user completes a lesson, insert or update a record in the user_course_progress table to mark the lesson as completed.
INSERT INTO user_course_progress (user_id, course_id, lesson_id, completed)
VALUES (user_id_value, course_id_value, lesson_id_value, TRUE)
ON DUPLICATE KEY UPDATE completed = TRUE, completion_date = CURRENT_TIMESTAMP;
  1. Calculate Course Progress:
    • To calculate the progress of a user in a course, you can use a query to count the completed lessons and divide by the total number of lessons in the course.
SELECT 
    COUNT(*) as completed_lessons
FROM 
    user_course_progress 
WHERE 
    user_id = user_id_value 
    AND course_id = course_id_value 
    AND completed = TRUE;
SELECT 
    COUNT(*) as total_lessons
FROM 
    lessons 
WHERE 
    section_id IN (SELECT id FROM sections WHERE course_id = course_id_value);
  • Calculate the percentage completion in your application logic.
  1. Display Progress:
    • Fetch the progress data for the user and display it on the learning panel.
SELECT 
    l.id as lesson_id,
    l.title as lesson_title,
    ucp.completed as lesson_completed
FROM 
    lessons l
LEFT JOIN 
    user_course_progress ucp 
    ON l.id = ucp.lesson_id 
    AND ucp.user_id = user_id_value
WHERE 
    l.section_id IN (SELECT id FROM sections WHERE course_id = course_id_value);

Example Application Logic (Pseudo-Code)

def get_course_progress(user_id, course_id):
    completed_lessons = query_database(f"""
        SELECT COUNT(*) as completed_lessons
        FROM user_course_progress 
        WHERE user_id = {user_id} 
        AND course_id = {course_id} 
        AND completed = TRUE;
    """)
    
    total_lessons = query_database(f"""
        SELECT COUNT(*) as total_lessons
        FROM lessons 
        WHERE section_id IN (SELECT id FROM sections WHERE course_id = {course_id});
    """)
    
    progress_percentage = (completed_lessons / total_lessons) * 100
    return progress_percentage

def display_lesson_status(user_id, course_id):
    lessons_status = query_database(f"""
        SELECT l.id as lesson_id, l.title as lesson_title, ucp.completed as lesson_completed
        FROM lessons l
        LEFT JOIN user_course_progress ucp 
        ON l.id = ucp.lesson_id 
        AND ucp.user_id = {user_id}
        WHERE l.section_id IN (SELECT id FROM sections WHERE course_id = {course_id});
    """)
    
    return lessons_status

This design and approach will ensure that you can effectively track and display user progress through each course.

0

To implement the learning process feature where progress is tracked and displayed, you will indeed need to store this information in a database. Here's a guide on how to design this feature using MySQL:

Database Design

  1. Tables:
    • users: Stores user information.
    • courses: Stores course information.
    • sections: Stores sections information within courses.
    • lessons: Stores lesson information within sections.
    • user_course_progress: Tracks the progress of users in each course.

Table Structure

users

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
);

courses

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT
);

sections

CREATE TABLE sections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

lessons

CREATE TABLE lessons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    section_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    FOREIGN KEY (section_id) REFERENCES sections(id)
);

user_course_progress

CREATE TABLE user_course_progress (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    lesson_id INT NOT NULL,
    completed BOOLEAN NOT NULL DEFAULT FALSE,
    completion_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (course_id) REFERENCES courses(id),
    FOREIGN KEY (lesson_id) REFERENCES lessons(id)
);

Implementation Steps

  1. Track Lesson Completion:
    • When a user completes a lesson, insert or update a record in the user_course_progress table to mark the lesson as completed.
INSERT INTO user_course_progress (user_id, course_id, lesson_id, completed)
VALUES (user_id_value, course_id_value, lesson_id_value, TRUE)
ON DUPLICATE KEY UPDATE completed = TRUE, completion_date = CURRENT_TIMESTAMP;
  1. Calculate Course Progress:
    • To calculate the progress of a user in a course, you can use a query to count the completed lessons and divide by the total number of lessons in the course.
SELECT 
    COUNT(*) as completed_lessons
FROM 
    user_course_progress 
WHERE 
    user_id = user_id_value 
    AND course_id = course_id_value 
    AND completed = TRUE;
SELECT 
    COUNT(*) as total_lessons
FROM 
    lessons 
WHERE 
    section_id IN (SELECT id FROM sections WHERE course_id = course_id_value);
  • Calculate the percentage completion in your application logic.
  1. Display Progress:
    • Fetch the progress data for the user and display it on the learning panel.
SELECT 
    l.id as lesson_id,
    l.title as lesson_title,
    ucp.completed as lesson_completed
FROM 
    lessons l
LEFT JOIN 
    user_course_progress ucp 
    ON l.id = ucp.lesson_id 
    AND ucp.user_id = user_id_value
WHERE 
    l.section_id IN (SELECT id FROM sections WHERE course_id = course_id_value);

Example Application Logic (Pseudo-Code)

def get_course_progress(user_id, course_id):
    completed_lessons = query_database(f"""
        SELECT COUNT(*) as completed_lessons
        FROM user_course_progress 
        WHERE user_id = {user_id} 
        AND course_id = {course_id} 
        AND completed = TRUE;
    """)
    
    total_lessons = query_database(f"""
        SELECT COUNT(*) as total_lessons
        FROM lessons 
        WHERE section_id IN (SELECT id FROM sections WHERE course_id = {course_id});
    """)
    
    progress_percentage = (completed_lessons / total_lessons) * 100
    return progress_percentage

def display_lesson_status(user_id, course_id):
    lessons_status = query_database(f"""
        SELECT l.id as lesson_id, l.title as lesson_title, ucp.completed as lesson_completed
        FROM lessons l
        LEFT JOIN user_course_progress ucp 
        ON l.id = ucp.lesson_id 
        AND ucp.user_id = {user_id}
        WHERE l.section_id IN (SELECT id FROM sections WHERE course_id = {course_id});
    """)
    
    return lessons_status

This design and approach will ensure that you can effectively track and display user progress through each course.

0

Sign in to participate in this thread!

Eventy

Your banner here too?

YIP KAR FAI carlx1101 Joined 19 Jul 2022

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.