The trouble with joins...

[DjMikeWatt]

Well the trouble with joins is that I never learned how to do them properly and with my half-assed guesswork sometimes I'm lucky and sometimes I'm not.

 

The scenario:

 

Table 1: "users" -- columns: id, name, phone, etc...

Table 2: "downloads" -- columns: account_id, year, month, link, etc.

 

I am trying to do a query that checks the year and month columns of table 2 against variables (successfully done this), and then also check the "account_id" of any matching records against the "id" in the users table; returning only those results that satisfy all three.

 

I tried this:

mysql_select_db($database_imaging101, $imaging101);
$query_rs_downloads = "SELECT * FROM downloads, users  WHERE `year` = '$year' AND `month` = '$month' AND downloads.account_id = users.id";
$rs_downloads = mysql_query($query_rs_downloads, $imaging101) or die(mysql_error());
$row_rs_downloads = mysql_fetch_assoc($rs_downloads);
$totalRows_rs_downloads = mysql_num_rows($rs_downloads);

And while it seems to get the month and year part right, it also returned an item with "account_id" of "1" when I was logged in with an account that has "users.id" of "2"...

 

Suggestions?

[patrickmvi]

I'm not 100% sure what you want to do here but I'm thinking this is what you want:

 

SELECT * FROM downloads d INNER JOIN users u ON d.account_id=u.id WHERE d.year = '$year' AND d.month = '$month' AND u.id = '2'

[abazoskib]

patrickmvi is right, you need to give a value to a variable for the user_id you are searching for.

[DjMikeWatt]

Can I use this to accomplish assigning a value to u.id?

 

$uid = $_row_rs_user['id'];

SELECT * FROM downloads d INNER JOIN users u ON d.account_id=u.id WHERE d.year = '$year' AND d.month = '$month' AND u.id = '$uid'

 

Would this work as long as the recordset "rs_user" is first on the page (above this new query)?

[abazoskib]

yes.