Jump to content


Photo

query help


  • Please log in to reply
3 replies to this topic

#1 angel2006

angel2006
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 20 April 2006 - 06:21 PM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 April 2006 - 07:44 PM

Something like this?

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 angel2006

angel2006
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 20 April 2006 - 09:38 PM

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

$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));
[/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


#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,017 posts

Posted 20 April 2006 - 10:33 PM

[!--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 $sid) whose lectures you want to list
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users