Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13
  1. #1
    Senior Member Richard S's Avatar
    Join Date
    Jul 2004
    Location
    SW Lonodn
    Posts
    219
    Member #
    6728
    Liked
    3 times
    Hi, I'm currently building a e-learning site.

    The site requires you to log in. There are two levels of privalges: student and manager.

    If you pass the test at the end of the course your details (name, score, date and course name) are entered in to a 'Passed' table.

    In the managers section there is a page that allows you to select a course and querys the 'Passed' table and display all the people who have passed a particualr course. This bit was quite easy.

    The hard part is I want to be able to display all the poeple who *havn't* passed a particular course. But scince there names won't be in the 'Passed' table (as they havn't passed) I can't think of a way of doing it.

    I guses I'd have to compare the table that holds the student details (names ect) with the record set retrived from the query on the 'Passed' table.

    Any ideas?

  2.  

  3. #2
    Senior Member Richard S's Avatar
    Join Date
    Jul 2004
    Location
    SW Lonodn
    Posts
    219
    Member #
    6728
    Liked
    3 times
    Sorted it.

    I spent all day thinking about it, then 5 mins after posting the thread here I figured it out.

    Heres how I've done it.

    PHP Code:
                    <?
                        
                        $n_sql 
    "SELECT * FROM E_index";
                        
    $n_result mysql_query($n_sql);
                        
    $nrows mysql_numrows($n_result);
                        
                        
    $n 0;
                        
                        
                            while (
    $n $nrows)
                                {
                                    
    $pay_nob mysql_result($n_result$n'E_pay_no');
                                    
    $nameb mysql_result($n_result$n'E_name');
                                    
                                    
    $p_sql "SELECT * FROM P_index where F_name = '$pay_nob' AND F_course = '$course_to_get'";
                                            
                                    
    $p_result mysql_query($p_sql);
                                    
                                    
    $pr mysql_numrows($p_result);
                                    
                                        if (
    $pr == 0)
                                            {
                                                if (
    $colour == "0")
                                                    {
                                                        echo 
    "<tr bgcolor=\"#D5D5D5\"><td>$nameb</td></tr>";
                                                        
    $colour "1";
                                                    }
                                                else
                                                    {
                                                        echo 
    "<tr bgcolor=\"#E5E5E5\"><td>$nameb</td></tr>";
                                                        
    $colour "0";
                                                    }
                                            }
                                    
    $n ++;
                                }
                }
            
            
    ?>

  4. #3
    Senior Member
    Join Date
    Dec 2003
    Posts
    1,274
    Member #
    4362
    Hmm... I can't think of any simpler than creating a loop, that checks for every result in the table where ALL the students are present (both passed and non-passed) wether if there exists a equal row in the passed table, and if it doesn't exist in the passed table, create a temporal table with all the non-passed students.
    A better solution would be to have a column called "passed" with possible values "true" and "false"

    EDIT- I was just late, but in any case you could get rid of the "passed" table and simply adding a column to the "students" table, and call that column "passed".

  5. #4
    Senior Member Richard S's Avatar
    Join Date
    Jul 2004
    Location
    SW Lonodn
    Posts
    219
    Member #
    6728
    Liked
    3 times
    Thanks for the reply, I think my solotion is pretty much along the lines of what you were saying. (good to know I'm on the right track).

    you could get rid of the "passed" table and simply adding a column to the "students" table, and call that column "passed".
    I can see what you mean, but it's likely that more than one course will be added in the future (or even to start with). So I guses I'd need to have a coloum for each course, and add more coloums in the future when more courses are added.

  6. #5
    Senior Member
    Join Date
    Dec 2003
    Posts
    1,274
    Member #
    4362
    Yes, I suppose the best would be a single table containing all the students from all the courses, and the specify there in a column to which course they belong, instead of having one table for each course. Like this you will also get a unique student ID, that belongs to one single study so you can do all things with that easily. A simply auto_increment column for the ID.

  7. #6
    Senior Member visualAd's Avatar
    Join Date
    Jan 2003
    Location
    Slough, UK
    Posts
    201
    Member #
    434
    Believe it or not it is a lot simpler than that. SQL provides an outter join facility (also known as left join). In a left join you join the tables together, but you choose one table for which all values must be displayed, regardless of whether or not they have a join pertner.

    Let assume the student table is called students and each student has a numeric identifier called StudentId and the courses table is called courses and each course has a numeric CourseId.

    The passes table simply has the fields "StudentID" and "CourseId" which form a composite primary key.

    Obviously your table structure will be different, but the principle is the same.

    A query to find all students who have not passed a particular course lets say CousrseId 52 would look like this:

    Code:
    SELECT * FROM students,courses LEFT JOIN passes ON
    students.StudentId=passes.StudentId AND
    courses.CourseId=passes.CourseId WHERE
    passes.CourseId IS NULL AND courses.CourseId=56;
    Here is how it works. First of all the students and courses are joined together in one big table, allowwing for every combination of student and course.

    This table is then joined with the passes table, again every possible combination is found based on the the join condition. The key part of the query is WHERE passes.CourseId IS NULL, because, if the join condition is not staisfied for a student,course pair it is still displayed, however the passes value is NULL. Filtering out the results you want is then childs play.

  8. #7
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    In the latest versions of MySQL you can use sub-queries, so you could simply say...

    SELECT * from students s
    WHERE NOT EXISTS
    ( SELECT '1' FROM passes p
    WHERE p.student_id = s.student_id
    )

    ..that sort of thing

    The other suggestion, regarding putting it all in one table, would be fine as long as it is correct relationally... this assumes that no student would ever do more than one course, which sounds a bit limiting in terms of their educational development :-)

    Sean

  9. #8
    Senior Member visualAd's Avatar
    Join Date
    Jan 2003
    Location
    Slough, UK
    Posts
    201
    Member #
    434
    Quote Originally Posted by seanmiller
    The other suggestion, regarding putting it all in one table, would be fine as long as it is correct relationally... this assumes that no student would ever do more than one course, which sounds a bit limiting in terms of their educational development :-)Sean
    That suggestion doesn't assume one student can only do one course. Its does however assume one student can only be on each course, thats why the students and courses table are joined in the left join.

  10. #9
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    Wasn't referring to you, rather Xarst's comment...

    Yes, I suppose the best would be a single table containing all the students from all the courses, and the specify there in a column to which course they belong, instead of having one table for each course. Like this you will also get a unique student ID, that belongs to one single study so you can do all things with that easily. A simply auto_increment column for the ID.
    May have mis-read this... not quite sure what was being suggested, but it doesn't sound relationally correct. Normalisation principles would dictate that the course and the student should both be entities in their own right.. in fact, I think the original design seems pretty sound (!)

    The original requirement, however, was to list students who hadn't passed a single course.. and for that NOT EXISTS is the recommended solution... I do not think many folks are still using MySQL 3, so it should be available... Oracle is my forté (been doing it 16 years+) but all the RDBMS solutions seem to be moving closer together -- aren't standards wonderful?

    Sean

  11. #10
    Senior Member visualAd's Avatar
    Join Date
    Jan 2003
    Location
    Slough, UK
    Posts
    201
    Member #
    434
    I suggested the outter join solution, because I know a lot of web hosts don't use the MySql version which supports subqueries. I seem to remember Oracle can handle sub queries and I know you can do outter joins with a (+) sign. An interesting question though, would my query actually port to Oracle?

    Edit:
    Sorry I mis-understood your comment. I think it would be a bad idea too, it would place unnecessary contraints on the system as well as un-normalising it.


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:03 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com