Cant fix the JOIN query of MYSQL using PHP

[Riu 2009]

hi everyone..hope every1 is going great..well i'm a beginner in mysql and php and i cant fix the query as i desire..iam joining 3 tables here and displaying thre data..

1st i have displayed course names from the courses table
2nd i have displayed the subject names from wich the courses belong.
3rd i want to display the status (if any) of each course from request records table,
when the student wants an approval he just applies the course and the status (applied) is saved in request records with 2 foreign keys (coursesid & studentid) here when i display the 3rd table's status it shows only the rows of those courses for whom i've applied..but not the list of all courses.. please help.. i want it to display list of all courses + status if any...
this is offered-courses.php

<?php
   $con=mysql_connect($dbserver,$dbusername,$dbpassword);
if (!$con)
    {
   die('Could not connect: ' . mysql_error());
    }
mysql_select_db($dbname, $con);
$result = mysql_query("SELECT courses.id AS cid, courses.title, courses.subjectsid, subjects.id AS sid, subjects.subjectname, requestrecord.status FROM courses, subjects, requestrecord WHERE (courses.subjectsid=subjects.id AND courses.id=requestrecord.coursesid)");
echo "<table border='1' style='width:500px;'> <br />
<tr>
<th>Course Name</th>
<th>Subject </th>
<th>Action</th>
<th>Status</th>

</tr>";
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
    echo "<td>" . $row['title'] . "</td>";
    echo "<td>" . $row['subjectname'] . "</td>";
    echo "<td><a href='request-for-approval.php?id=".$row['cid']."& sid=".$row['sid']."'>Apply!</a></td>"; 
    echo "<td>" . $row['status'] . "</td>";
  echo "</tr>";
    }
echo "</table>";
mysql_close($con);
?>

[pritaeas]

You mean like this:

SELECT courses.id AS cid, courses.title, courses.subjectsid, subjects.id AS sid, subjects.subjectname, requestrecord.status 
FROM courses, subjects 
LEFT JOIN requestrecord 
ON courses.id = requestrecord.coursesid
WHERE courses.subjectsid = subjects.id 

[Riu 2009]

i have to write this query in php and this is not the syntax in php..im a beginner..sorry for bothering u @pritaeas

[Riu 2009]

@pritaeas the problem is fixed thanx :)

i want to show the applied status of only the student who is logged in... it shows multiple rows of applied status with same course names because different students have applied...how would i filter tht??? thanx in advance :)

[pritaeas]

Add it to the WHERE clause. Without any sample data I cannot be more specific.

[Riu 2009]

@pritaeas
in courses table i have following columns(id, title, description, subjectsid, accountsid)
in accounts table i have following columns(id, fname, lname, email, pwd,)
in subjects table i have following columns(id, subjectname)
in requestrecord table i have following columns(id, status, accountsid, coursesid)

(i want to display record status (if any) of only the student who is logged in and list of all courses with course title, subjectname, action (to apply) and status (if any)... what iam getting is record of all students who have applied... heres my query

$result = mysql_query("SELECT courses.id AS cid, courses.title, courses.subjectsid, subjects.id AS sid, subjects.subjectname,  requestrecord.status, accounts.id
FROM courses JOIN subjects ON courses.subjectsid = subjects.id 
LEFT JOIN requestrecord 
ON courses.id = requestrecord.coursesid 
INNER JOIN accounts
WHERE accounts.id=".$_SESSION['id']);
echo "<table border='1' style='width:500px;'> <br />
<tr>
<th>Course Name</th>
<th>Subject </th>
<th>Action</th>
<th>Status</th>

</tr>";
while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
    echo "<td>" . $row['title'] . "</td>";
    echo "<td>" . $row['subjectname'] . "</td>";
    echo "<td><a href='request-for-approval.php?id=".$row['cid']."& sid=".$row['sid']."'>Apply!</a></td>"; 
    echo "<td>" . $row['status'] . "</td>";
  echo "</tr>";
    }
echo "</table>";
mysql_close($con);
?>

[pritaeas]

Am not sure what status is, but you can replace 'X' with what it should be. The query should look like this:

$result = mysql_query("
    SELECT courses.id AS cid, courses.title, courses.subjectsid, 
           subjects.id AS sid, subjects.subjectname, 
           requestrecord.status, 
           accounts.id
    FROM courses 
    JOIN subjects 
        ON courses.subjectsid = subjects.id 
    LEFT JOIN requestrecord 
        ON courses.id = requestrecord.coursesid 
        AND requestrecord.status = 'X'
    INNER JOIN accounts
        ON accounts.id = " . $_SESSION['id']
);

[Riu 2009]

@pritaeas still its showing the record of another students who has applied in the same subject...i would be thankful for ur response

[pritaeas]

The only way for me to reproduce would be an sql dump of the tables (structure and some data).

[Riu 2009]

i've tried, this is how it is solved :)

$result = mysql_query("SELECT courses.id AS cid, courses.title, courses.subjectsid, subjects.id AS sid, subjects.subjectname,  requestrecord.status
FROM courses JOIN subjects ON courses.subjectsid = subjects.id 
LEFT JOIN requestrecord 
ON courses.id = requestrecord.coursesid AND requestrecord.accountsid=".$_SESSION['id']);