There are 3 classes, each with 10 students. Each class has their own homeroom teacher. When a student submits the form and chooses his homeroom teacher’s identity, I have to make sure that he chooses his own teacher. Otherwise, error message should be displayed.
CREATE TABLE student(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
class_id INTEGER NOT NULL,
pcourse_id INTEGER NOT NULL,
FOREIGN KEY (class_id) REFERENCES class(id),
FOREIGN KEY (pcourse_id) REFERENCES course(id)
);
CREATE TABLE teacher(
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL
);
student.class_id = teacher.id
This is my app.py code for survey page. Help me how and where to place the statement that checks if the student chose his own teacher. Otherwise, display error message.
@app.route("/questionnaire/page", methods = ["GET", "POST"])
def questionnaire():
cursor = get_db().cursor()
if request.method == "POST":
try:
form = request.form
student_id = int(form.get("student_id"))
cursor.execute("BEGIN")
cursor.execute("""
SELECT id
FROM student
WHERE id =?
LIMIT 1
""", [student_id])
if cursor.fetchone():
cursor.execute("COMMIT")
return render_template("questionnaire_already_answered.html")
except (TypeError, ValueError, sqlite3.DatabaseError):
abort(400)
cursor.execute("SELECT * FROM student")
students = cursor.fetchall()
teachers = cursor.execute("SELECT * FROM teacher")
teachers = cursor.fetchall()
courses = cursor.execute("SELECT * FROM course")
courses = cursor.fetchall()
return render_template("questionnaire.html", students = students, teachers = teachers, courses = courses)
2
Answers
since class_id is the same as teacher_id, I didn't have to add such column. Instead, used this code:
cursor.execute(""" SELECT id FROM student WHERE id = ? AND class_id = ? LIMIT 1 """, [student_id, teacher_id]) student_teacher_matching = cursor.fetchone() if not student_teacher_matching: return render_template("mismatched_teacher.html")
teacher_id is retrieved from the submission and if student doesn't choose his own teacher, it redirects the user to a page where it says "Choose your own teacher".
if you want to be sure that a student chooses their own homeroom teacher, you should connect students and teachers directly. Since each class has its own homeroom teacher, you can link students with their teachers based on the class they belong to.
You need to add a column like
class_teacher
to yourclass
table.class_teacher
should store the homeroom teacher’s id for each class. This way, each class will have a designated teacher.Also in your questionnaire page code you should verify that when a student selects a teacher, it corresponds to their assigned homeroom teacher. You can do that by retrieving the homeroom teacher of the student’s class from the database and confirming whether the selected teacher matches.