PHP / MySQL选择查询问题

Question

I was wondering if anyone could give me a hand. I currently have 3 tables as follows:

users(user_id, username, first_name, last_name, password) module(module_id, name, crn) userModule(user_id, module_id)

What would be the best way to access this? From doing basic research it looks like a RIGHT JOIN would be appropriate...

How would I go about looking at the modules table based on the user_id variable?

I gave it ago here but it doesn't seem to work.

$result = mysql_query("SELECT * FROM module JOIN userModule ON (module.module_id = userModule.module_id) JOIN users ON (userModule.user_id = users.user_id) WHERE user_id = 2");

$row = mysql_fetch_row($result);

echo $row[0];
echo $row[1];
echo $row[2];

Answer 1

$result = mysql_query("SELECT * FROM modules LEFT JOIN userModule ON userModule.module_id = modules.module_id WHERE userModule.user_id = 2");

It should work!

Better yet, if you only want to get modules info:

SELECT modules.* FROM modules ....

I used LEFT JOIN but there are several other methods : RIGHT JOIN, INNER JOIN or FULL JOIN. See what's the difference between those methods here: http://www.w3schools.com/sql/sql_join.asp

Also, you should always add " or die(mysql_error());" after your mysql_query(). If the query is wrong (bad formatting, miswritten fields, etc.) it will print a useful error.