Jump to content

Archived

This topic is now archived and is closed to further replies.

angel2006

query help

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
[!--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

Share this post


Link to post
Share on other sites
[!--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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.