Multi-table Query Join [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

Since I am new to PHP, i am finding this immensely complicated to solve. I have this query that is showing results that a user is not supposed to do.

The problem is the sales agent is able to see complaints from users he is not authorized to see. This pertains to the accounts table, which is for the customers.

$priv = "dire problem" , 
$naone = "not serious" , 
$priv2 = "mild prblem" 
are sorting conditions. 
$aid is the agent viewing this page.  

Complaints is for complaints by the customers. 
Accounts table holds all the customer information. 
Agents table is for all the sales/customer reps. 

Code:

$sql = "SELECT complaints.complaint_id, accounts.full_name,
agents.agent_name, complaints.person_id, complaints.why_com开发者_开发问答plaint, 
complaints.just_date, complaints.type, complaints.date_time_added FROM 
complaints LEFT JOIN accounts ON complaints.person_id = accounts.person_id 
LEFT JOIN agents on complaints.agent_whois = agents.agent_id WHERE 
(complaint_type = '$priv' OR complaint_type = '$naone' OR complaint_type = '$priv2') and  
 (complaints.added_by <> '$aid')"; 
 $result=mysql_query($sql);

 $query = mysql_query($sql) or die ("Error: ".mysql_error());

 if ($result == "")
 {
 echo "";
 }
 echo "";


 $rows = mysql_num_rows($result);

 if($rows == 0)
 {
 print("");

 }
 elseif($rows > 0)
 {
 while($row = mysql_fetch_array($query))
 {

  $complaintid = $row['complaint_id'];
  $agentwho = $row['person_id'];
  $agentname = $row['agent_name'];
 $reason = $row['why_complaint'];
 $datetimeadded = $row['just_date'];
  $docname = $row['full_name'];
  $type = $row['type'];


   print("");
   }

    }

---

This is not really an answer, but I'm posting it anyway since it's the best I can do.

OK, first of all, your indentation is all over the place. Please, for the sake of anyone who needs to read your code, use a consistent indentation style. It doesn't really matter which style you use — just pick one and apply it consistently. It makes you code much easier to read.

That said, let's take a look at your query. Here it is in its original form, just reindented for better readability:

SELECT
  complaints.complaint_id,
  accounts.full_name,
  agents.agent_name,
  complaints.person_id,
  complaints.why_complaint, 
  complaints.just_date,
  complaints.type,
  complaints.date_time_added
FROM
  complaints
  LEFT JOIN accounts ON complaints.person_id = accounts.person_id 
  LEFT JOIN agents ON complaints.agent_whois = agents.agent_id
WHERE
  ( complaint_type = '$priv'
    OR complaint_type = '$naone'
    OR complaint_type = '$priv2' )
  AND (complaints.added_by <> '$aid')

In fact, we can rewrite the WHERE clause a bit more compactly like this:

WHERE
  complaint_type IN ('$priv', '$naone', '$priv2')
  AND complaints.added_by <> '$aid'

But all this says is that complain_type must be one of the three values, and that the complaint must not have been added by the agent '$aid'. You say that

"The problem is the sales agent is able to see complaints from users he is not authorized to see."

but there's absolutely nothing in the query about any kind of authorization at all! Since I can't even guess from the query what kind of authorization data your tables might contain, or what you want to do with it, the only advice I can give you is to figure out some rules to tell the records that should be shown from those that shouldn't be and add them to the query.