Display Certain MySQL Tables

Question

Here is my PHP database structure: http://pastebin.com/x89AdzfS

I've been trying for hours to get a JOIN SQL query using PDO to work but have failed, so I need some help here.

I need a PDO query that displays all the "assignments" (title and description) which are assigned to a class id that the student is also enrolled in.

Please tell me if you need me to be more specific. Thanks!

EDIT

Here's a query I did which displays the classes a person is in

$sql = $db->prepare("SELECT enrollments.*, courses.*, students.* FROM enrollments
LEFT JOIN courses ON enrollments.course_id = courses.id
LEFT JOIN students ON students.id = enrollments.student_id
WHERE enrollments.student_id = ?
");
$sql->execute(array($login_id));

while($row = $sql->fetch(PDO::FETCH_ASSOC))
     {
    echo "<li><a href='class.php?id=".$row['course_id']."'><i class='icon-book'></i>".$row['name']."</a></li>";
}

Hey, Alex - I'm not that familiar with PDO so I won't be able to help you anyway, but it's generally bad form to ask for help without providing examples of what you've tried. Not only is there a good chance that what you wrote is close to working already, it also proves that you've spent time on the issue. — Winfield Trail
– Winfield Trail, Commented Aug 15, 2012 at 1:41
I've updated it with a query I've done that shows the classes a user is enrolled in, but can't get it to show the homework for every class. — Alex Castro
– Alex Castro, Commented Aug 15, 2012 at 1:49
YAY! Someone who doesn't use mysql_ and has a low reputation! — Cole Tobin
– Cole Tobin, Commented Aug 15, 2012 at 4:04

Chris Trahey · Accepted Answer · 2012-08-15 04:01:16Z

2

Here is a relatively simple join to accomplish what I think you are after.

$sql = $db->prepare("SELECT 
  title, 
  description, 
  courses.name
FROM assignments
JOIN courses ON courses.id = assignments.course_id
JOIN enrollments USING(course_id)
WHERE enrollments.student_id = ?");

$sql->execute(array($login_id));

My thought process with queries goes something like this:

Decide which is the "main" table. You said "displays all assignments", so it's "assignments"
That table is the first table after FROM
Knowing that I have a certain student, find the shortest path to "student_id" which makes sense for the domain logic
I see that "enrollments" has a student_id, and that my assignment and enrollments has a course_id; so that's my path to student_id
JOIN enrollments on the factors involved in it's relationship to assignments (which SQL makes easy when the columns are the same name with USING()

The difference from your query (as I see it), is to use your absolute destination entity as the original FROM table, and then join from there. If you want assignments, the query should start with FROM assignments, then use joins to pull in the data you need to use to limit the results.

edited Aug 15, 2012 at 4:01

answered Aug 15, 2012 at 1:49

Chris Trahey

18.3k1 gold badge44 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex Castro Over a year ago

This works a charm. Thanks for the thought process too; it has definitely cleared up some things for me.

Alex Castro Over a year ago

Would you actually know how to see which course it comes from? I want to show Class: Assign. Title

Chris Trahey Over a year ago

Sure thing... just add a join to courses. It's a decent example of the alternative syntax for JOINs (ON instead of USING)

Collectives™ on Stack Overflow

Display Certain MySQL Tables

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related