How to Implement a Quiz Leaderboard in PHP Online Testing

How to Implement a Quiz Leaderboard in an Online Testing System

With the growth of online education, many educational platforms need to evaluate students' learning progress and competitiveness through online quiz systems. Among these features, a leaderboard is an essential tool to assess student performance. This article explains how to implement a leaderboard feature effectively.

Database Design

First, design a database to store student quiz information and leaderboard data. Assume there are two tables: Student and Score. The Student table stores basic information such as student ID, name, and class. The Score table stores quiz scores, including student ID, score, and submission time. Using the scores in the Score table, we can calculate total scores and generate rankings.

Implementing the Leaderboard

Before implementing the leaderboard, you need to fetch students' quiz data and calculate the scores. Example code:

import pymysql

# Connect to the database
db = pymysql.connect(host='localhost', user='root', password='123456', db='test')

# Create a cursor
cursor = db.cursor()

# Query students' quiz scores
sql = "SELECT student_id, SUM(score) AS total_score FROM score GROUP BY student_id"

try:
    # Execute the SQL statement
    cursor.execute(sql)

    # Fetch all student score data
    results = cursor.fetchall()

    # Create leaderboard list
    leaderboard = []

    # Iterate through each student's score
    for row in results:
        student_id = row[0]
        total_score = row[1]

        # Add student ID and total score to the leaderboard
        leaderboard.append((student_id, total_score))

    # Sort the leaderboard in descending order by total score
    leaderboard.sort(key=lambda x: x[1], reverse=True)

    # Output leaderboard data
    for i, item in enumerate(leaderboard):
        print(f'Rank {i+1}: Student ID = {item[0]}, Total Score = {item[1]}')

except Exception as e:
    print(f'Database query error: {e}')

# Close the database connection
db.close()

The code above uses Python's pymysql library to connect to the database and execute SQL statements. It fetches quiz scores from the Score table, stores student ID and total scores in the leaderboard list, sorts it in descending order, and outputs the leaderboard.

Updating the Leaderboard

To keep the leaderboard up-to-date, scores must be updated whenever a student completes a quiz. Example code:

import pymysql

def update_leaderboard(student_id):
    # Connect to the database
    db = pymysql.connect(host='localhost', user='root', password='123456', db='test')

    # Create a cursor
    cursor = db.cursor()

    # Query student's total quiz score
    sql = f"SELECT SUM(score) AS total_score FROM score WHERE student_id = {student_id}"

    try:
        # Execute SQL statement
        cursor.execute(sql)

        # Fetch the student's score
        result = cursor.fetchone()

        if result:
            total_score = result[0]

            # Update leaderboard data
            sql = f"UPDATE leaderboard SET total_score = {total_score} WHERE student_id = {student_id}"
            cursor.execute(sql)

        # Commit transaction
        db.commit()

    except Exception as e:
        print(f'Error updating leaderboard data: {e}')

        # Rollback transaction
        db.rollback()

    # Close the database connection
    db.close()

The code defines the update_leaderboard function to update the leaderboard for a specific student. It retrieves the student's total quiz score from the Score table and updates it in the Leaderboard table to ensure real-time ranking.

Summary

This article covers the complete process of implementing a leaderboard in an online quiz system, including database design, fetching and sorting leaderboard data, and updating it in real-time. Following these steps, developers can easily calculate and rank student quiz scores, enhancing the interactivity and competitiveness of the online quiz platform.