使用PDO选择包含参数的连接表

Question

Can somebody tell me pls where the error is in my code below? I have been trying to join two tables to get exam scores but I always get the error that `Warning: PDOStatement:execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:direcory'

Here is my code:

function getEmptyExamScores($conn, $course_code, $level, $year, $semester) { 
$stmt = $conn->prepare('SELECT * FROM exams INNER JOIN register_course ON exams.course_code = register_course.course_code WHERE exams.course_code = :exams.course_code AND level = :level AND year = :year AND semester = :semester;');
$stmt->execute(['exams.course_code' => $course_code, 'level' => $level, 'year' => $year, 'semester' => $semester]);
return $stmt->fetchAll(); 
}

Answer 1

First advice: readability matters :-)

I'd suggest using SQL alias for your tables, because it makes queries easier to read.

Also you can specify which columns belong to which table. Otherwise, let's say a year from now you have a year column on both tables, and your query is going to fail because the parser won't know which column you are referring to.

I'm assuming level, year and semester are part of the exams table.

<?php

function getEmptyExamScores($conn, $course_code, $level, $year, $semester) {
    $query = 'SELECT *
        FROM exams e
        INNER JOIN register_course rc ON e.course_code = rc.course_code
        WHERE e.course_code = :course_code
            AND e.level = :level
            AND e.year = :year
            AND e.semester = :semester';

    $stmt = $conn->prepare($query);

    $stmt->execute([
        'course_code' => $course_code,
        'level' => $level,
        'year' => $year,
        'semester' => $semester,
    ]);

    return $stmt->fetchAll();
}