PHP学习门户：基于注册实现数据库驱动用户个人主页的技术求助

Let’s break this down step by step—turning your portal into a fully database-driven system that shows personalized modules based on user-selected courses. I’ve worked through similar use cases, so here’s a practical approach:

1. Database Schema Adjustments #

First, you’ll need to expand your database to track user-course associations and course-module relationships. This is the foundation of personalization. Here’s a suggested table structure:

Core Tables #

• users: Stores basic user account details

  CREATE TABLE users (
      id INT PRIMARY KEY AUTO_INCREMENT,
      username VARCHAR(50) UNIQUE NOT NULL,
      email VARCHAR(100) UNIQUE NOT NULL,
      password_hash VARCHAR(255) NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
  

• courses: Stores all available courses users can choose

  CREATE TABLE courses (
      id INT PRIMARY KEY AUTO_INCREMENT,
      course_name VARCHAR(100) NOT NULL,
      description TEXT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
  

• modules: Stores individual learning modules (think lessons, quizzes, etc.)

  CREATE TABLE modules (
      id INT PRIMARY KEY AUTO_INCREMENT,
      module_name VARCHAR(100) NOT NULL,
      content TEXT,
      module_order INT NOT NULL, -- Controls the display sequence of modules
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
  

Association Tables (Critical for Personalization) #

These tables link users to courses, and courses to their modules:

• user_courses: Tracks which courses each user selected during registration

  CREATE TABLE user_courses (
      user_id INT NOT NULL,
      course_id INT NOT NULL,
      PRIMARY KEY (user_id, course_id),
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
      FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
  );
  

• course_modules: Maps courses to their associated modules (supports modules being used in multiple courses if needed)

  CREATE TABLE course_modules (
      course_id INT NOT NULL,
      module_id INT NOT NULL,
      PRIMARY KEY (course_id, module_id),
      FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
      FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE
  );
  

2. Update Registration Flow to Support Course Selection #

Modify your registration process to let users pick courses before completing sign-up:

Frontend Changes #

• Add a course selection section to your registration form (e.g., checkboxes for multiple courses or a dropdown for single selection)
• Fetch the full list of courses from your backend API and populate the selection UI dynamically
• Make sure the selected course IDs are included in the form submission data

Backend Logic #

When processing a new user’s registration:

1. First, insert the user’s account details into the users table and get their new user_id
2. Use that user_id and the submitted course IDs to add entries to the user_courses table
   Example (Python/PostgreSQL):

   # After inserting user and getting user_id
   selected_course_ids = request.form.getlist('selected_courses')  # List of course IDs
   for course_id in selected_course_ids:
       db.execute(
           "INSERT INTO user_courses (user_id, course_id) VALUES (%s, %s)",
           (user_id, course_id)
       )
   db.commit()
   

3. Load Personalized Modules on User Login #

Once a user logs in, fetch only the modules tied to their selected courses instead of a fixed set:

Backend Query to Get User’s Modules #

Use a JOIN query to pull all relevant modules for the current user:

SELECT m.id, m.module_name, m.content, m.module_order
FROM users u
JOIN user_courses uc ON u.id = uc.user_id
JOIN course_modules cm ON uc.course_id = cm.course_id
JOIN modules m ON cm.module_id = m.id
WHERE u.id = %s  -- Replace with current user's ID
ORDER BY m.module_order;

This query will return exactly the modules associated with the courses the user chose during registration.

Frontend Rendering #

Take the module data from the backend and dynamically render them on the user’s dashboard. Example (Vanilla JavaScript):

// Assume modulesData is the array returned from the backend
const dashboardContainer = document.getElementById('dashboard');

modulesData.forEach(module => {
  const moduleCard = document.createElement('div');
  moduleCard.className = 'module-card';
  moduleCard.innerHTML = `
    <h3>${module.module_name}</h3>
    <p>${module.content}</p>
  `;
  dashboardContainer.appendChild(moduleCard);
});

4. Optional Enhancements #

• Post-Registration Course Updates: Add a "Manage Courses" section where users can add/remove courses—just update the user_courses table and refresh their module list
• Progress Tracking: Extend the schema with a user_module_progress table to track which modules a user has completed
• Cache Optimization: Cache the user’s module list temporarily (e.g., in Redis) to reduce repeated database queries on frequent logins

内容的提问来源于stack exchange，提问作者user9445468