0

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);
?>

Edited by Riu 2009

2
Contributors
9
Replies
10
Views
4 Years
Discussion Span
Last Post by Riu 2009
0

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 
0

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

0

@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 :)

0

@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);
?>

Edited by Riu 2009

0

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']
);
0

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

1

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']);
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.