CODEWITHSUNDEEP
phpmysqldatabase
user1839601
user1839601

Reputation: 119

PHP/MySQL select query issues

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];

Upvotes: 0

Views: 129

Answers (1)

mombul
mombul

Reputation: 327

$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.

Upvotes: 1

Related Questions