How to retrieve data from multiple tables in MySQL and PHP? [closed]

TLDR: Please point me to the SIMPLEST tutorial for my problem. I have read many tutorials but all they did was confuse me more.

I have three tables.

1. students (has a row of id and name)
2. courses (has a row of id and name)
3. courses_students

The third table to link the two was created like this:

CREATE TABLE courses_students (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    student_id INT(6) REFERENCES students(id),
    course_id INT(6) REFERENCES courses(id)
);

Then I add a simple data like this:

INSERT INTO courses_students (course_id, student_id) VALUES (2, 3);

The 2 and 3 are ids in the courses and students tables, respectively. How do I retrieve the information for these ids (2 from the courses table, and 3 from the students tables) using mysqli+PHP statements?

Answer

It is pretty straight forward, there are many ways, here is the simplest one (assuming you’ve “established” a connection as described here https://www.php.net/pdo.connections):

$stmt = $pdo->query("
            SELECT s.id AS studentId, s.name AS studentName, c.id AS courseId, c.name AS courseName
            FROM courses_students AS cs
            LEFT JOIN students AS s ON s.id = cs.student_id
            LEFT JOIN courses AS c ON c.id = cs.course_id
        ");

$records = $stmt->fetchAll();

That’s getting a bit more complex when you need to query with parameters – then you should read a bit more about bindings, but the general idea is like the following code:

// Preparing the statement.
$stmt = $pdo->prepare("
            SELECT s.id AS studentId, s.name AS studentName, c.id AS courseId, c.name AS courseName
            FROM courses_students AS cs
            LEFT JOIN students AS s ON s.id = cs.student_id
            LEFT JOIN courses AS c ON c.id = cs.course_id
            WHERE cs.student_id = :studentId
            AND cs.course_id = :courseId
        ");

// Binding and executing the statement.
$stmt->execute([
    'studentId' => 3,
    'courseId' => 2
]);

$records = $stmt->fetchAll();