Query Multiple Tables II

ripcurlksm

I have three tables set up: user, reports & permissions. I need some help in executing a SQL query and I am having a tough time getting the right results.

Summary:
I have a login setup so that a user can login and view reports that they have permission to see. I need the SQL statement to get the user's id, then select all of the rows in the "permissions" table where there id is present AND get the "report id" associated inside that table, THEN select all of the rows in the "report" table that have the "report id" from the previous query.

Here is my database schema:

user
==================
id   |   username 
==================
1    |   bob
2    |   john


reports
===============================
id   |   title   |   company 
===============================
1    |   Green   |   Crayola
2    |   Red     |   Bic
3    |   Blue    |   Papermate



permissions
===========================
user_id   |   report_id 
===========================
1         |   1
1         |   2
2         |   1
2         |   3


**So Bob would get "Green" and "Red" and John would get "Red" and "Blue"**

Here is my incomplete SQL query. I still need to reference the permissions table and I am really lost on this one.

SELECT reports.* FROM user, report WHERE user.id = reports.id;

Can anyone suggest a correct query?

malariajoe

Your WHERE-clause is not what you want ;-)

This one could work:

SELECT r.title FROM user u JOIN permissions p ON u.id = p.user_id JOIN reports ON p.report_id = r.id

bpat1434

To expand further (mala is correct though). You answered your own question when you asked it.

I need the SQL statement to get the user's id, then select all of the rows in the "permissions" table where there id is present AND get the "report id" associated inside that table, THEN select all of the rows in the "report" table that have the "report id" from the previous query.

So if you list them out in steps:
1.) Get permissions from permissions table pertaining to user ID
2.) Get report ID associated with permissions for user
3.) Get all reports available based upon report IDs already grabbed

So in two queries it's this:

<?php
$userID = 1; // Will need to be changed.  '1' used as example

/*  Your DB connection stuff here: 
 *
 *  $conn = mysql_connect('host', 'user', 'pass);
 *  mysql_select_db('database', $conn);
 *
 */

// ::::: GENERATE PERMISSIONS ::::: //
$p_query = "SELECT report_id
FROM `permissions`
WHERE user_id = '$userID'";
$permissions = mysql_query($p_query);

while($row = mysql_fetch_array($permissions))
  $perms[] = $row['report_id'];

// ::::: GRAB REPORT INFORMATION ::::: //
$r_query = "SELECT title, company
FROM `reports`
WHERE report_id IN(" . implode(', ', $perms) . ")";
$rslt = mysql_query($r_query);

while($row = mysql_fetch_array($rslt))
  echo 'Report: <strong>' . $row['title'] . '</strong> <em>' . $row['company'] . '</em><br />';

?>

Now, that can be combined with a (few) JOIN statement(s). Simply, in one query it'd be:

<?php
/* Once again, $userID is arbitrary for example purposes.
 * It should be set by you dynamically based upon the current
 * user.  Typically, it would be read from the SESSION.  It should be
 * the numerical ID associated with the name (1=Bob, 2=John).
 */
$userID = 1;

$query = "SELECT r.report_id, r.title, r.company
FROM `permissions` as p
INNER JOIN `reports` as r
  ON p.report_id = r.report_id
WHERE p.user_id = '$userID'";
$result = mysql_query($query);

while($row = mysql_fetch_array($result))
  echo 'Report: <strong>' . $row['title'] . '</strong> <em>' . $row['company'] . '</em><br />';
?>

$userID again would be populated at login or whatever. But somehow you'd have to get that to its numerical value (1 for Bob, 2 for John).