PHP/MySQL join 2 tables

Question

Hi,

I've got one mysql table with usernames, passwords and property names, that allows a user to log-in to a secure site section. Now what I'd like to be able to do is pull-in pdfs from a second table where it checks the property name from table one against the documentCategory of the pdfs in table two, and then only displays the relevant pdfs depending on the logged-in user. (Hope this makes sense)

Table structure:

Table 1 -  
userid    password   property

Table 2 -  
pdfFile    documentCategory

Where table1.property = table2.documentCategory

Any help on how I can get this working in php would be greatly appreciated as I'm now completely lost.
S

Answer 1

SELECT table2.pdfFile, table2.documentCategory FROM table2, table1 WHERE table1.property = table2.documentCategory AND table1.user_id = ?

Make sure table1.user_id has a PRIMARY index and table2.documentCategory has an index too.

Answer 2

If the user is already logged in (that is, you have the userid) your SQL query would probably look like this:

$query = 'SELECT pdfFile
  FROM table2
  INNER JOIN table1 ON property = documentCategory
  WHERE table1.userid = '.intval($userid);

$result = mysql_query($query);

Adapt this for your preferred database abstraction layer.

Answer 3

select t2.pdfFile
from t1 inner join t2 
on t1.property = t2.documentCategory
where t1.userId = $userId

Update... Not sure this is a php question... More like a SQL question.