query help

[angel2006]

hi, could someone please help me with this query,

i have these tables for the query:

lecture: lecture_id (PK), module_code(FK), lec_date

module: module_code(PK), info about it...

student_modules: student_id(FK), module_code(FK)

student: student_id(PK), student details...

i need to work out which students are taking which modules, then from that result, which module has which lecture. So in the end i want to create a form which will display these fields automatically on the page

could some 1 help????

[Barand]

Something like this?

[code]$sql = "SELECT s.student_name, m.module_code,
                     m.module_name, l.lecture_id, l.lec_date
        FROM student s
        INNER JOIN student_models sm
              ON s.student_id = sm_student_id
        INNER JOIN module m
              ON sm.module_code = m.module_code
        INNER JOIN lecture l
              ON l.module_code = m.module_code
        WHERE s.student_id = '$sid'
        ORDER BY m.module_name, l.lec_date";

$res = mysql_query($sql) or die (mysql_error());

list ($sname, $modcode, $modname, $lecid, $lecdate) = mysql_fetch_row($res);

echo "<h3>$sname</h3>";

$prevmod = '';

do {
    if ($prevmod != $modcode) {
        echo "<h4>$modcode - $modname</h4>";
        $prevmod = $modcode;
    }
    echo "$lecid $lecdate<br>";
} while (list ($sname, $modcode, $modname, $lecid, $lecdate) = mysql_fetch_row($res));[/code]

[angel2006]

[!--quoteo(post=366979:date=Apr 20 2006, 08:44 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Apr 20 2006, 08:44 PM) [snapback]366979[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Something like this?

[code]$sql = "SELECT s.student_name, m.module_code,
                     m.module_name, l.lecture_id, l.lec_date
        FROM student s
        INNER JOIN student_models sm
              ON s.student_id = sm_student_id
        INNER JOIN module m
              ON sm.module_code = m.module_code
        INNER JOIN lecture l
              ON l.module_code = m.module_code
        WHERE s.student_id = '$sid'
        ORDER BY m.module_name, l.lec_date";

$res = mysql_query($sql) or die (mysql_error());

list ($sname, $modcode, $modname, $lecid, $lecdate) = mysql_fetch_row($res);

echo "<h3>$sname</h3>";

$prevmod = '';

do {
    if ($prevmod != $modcode) {
        echo "<h4>$modcode - $modname</h4>";
        $prevmod = $modcode;
    }
    echo "$lecid $lecdate<br>";
} while (list ($sname, $modcode, $modname, $lecid, $lecdate) = mysql_fetch_row($res));[/code]
[/quote]






thanks that seems 2 work, there arent any errors but im not sure how 2 test it and if i have an input what it should be

[Barand]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]WHERE s.student_id = '$sid'[/quote]

You need to input the student's id (as [b]$sid[/b]) whose lectures you want to list