Populating Text Areas with DB data

[CaptainSlow]

Hey there! I've got a SQL question that I hope I can get some help on. I'll first start with some back story to help you understand what I'm trying to do and then I'll go to my question.

Here the problem (tables have been simplified). I've got three tables. First, I have an instructor table with fields for an instID, fname, and lname. I then have a table called course with fields for courseID, courseName. Finally, I have a table called instructorCourse with fields instID, courseID. Both these fields are primary and foreign keys. This table lists all the courses an instructor can teach.

As a data example say I have the following in each table:

Instructor
ID    fname    lname
1      George    Costanza

Courses
ID      courseName
1            reading
2            math
3            cooking
4            science

instructorCourse
instID      courseID
1              1
1              4

So essentially George Costanza can teach reading and science. What I want to do is have two text areas on form, side by side. The first text area (left side)will list the courses George CAN teach (in this case, 1 and 4, or reading and science) and the second text area (right side) will list the courses George CANNOT teach (in this case, 2 and 3, or math and cooking). Any idea as to what kind of SQL statement I will need to do this? I think I know how to display the courses he can teach but I have no idea on how to list the courses he cannot teach.

Any help would be greatly appreciated.

P.S. I am using PHP to get the SQL results from the MySQL DB. Thanks again!

[shoz]

[code]
SELECT
c.courseID, c.courseName, IF (ISNULL(ic.courseID), 0, 1) AS canTeach
FROM
Courses AS c
LEFT JOIN
instructorCourse AS ic
ON
c.courseID = ic.courseID AND ic.instID = $instructor_id
[/code]

In PHP you can then create an array of the courses using something similar to the following
[code]
<?php
$canTeach = array();
$notTeach = array();
while ($row = mysql_fetch_assoc($result))
{
    if ($row['canTeach'])
    {
        $canTeach[] = $row['courseID'];
    }
    else
    {
        $notTeach[] = $row['courseID'];
    }
}
?>
[/code]

[CaptainSlow]

What does [code]IF (ISNULL(ic.courseID), 0, 1)[/code]
mean? I wasn't aware that you were able to add IF statements in SQL queries.

Actually I think I may have figured it out. Let me know if this is correct. If the ic.courseID of $instID is null enter a zero otherwise enter a 1.

[CaptainSlow]

I did play around with the code you gave me and it does exactly what I need. Also, thanks for adding the additional PHP code that creates arrays for courses George can teach and cannot teach. I have written code previously that parses data from an array and enters it into the text area. However I do have another issue I was concerned about.

So at this point George can teach courses 1 and 4 which are reading and science respectively. However, using the method I have implemented (javascript) he moved courses 1 and 4 (reading and science) to the left text box (pool of all courses) and to the right text box (courses instructor can teach) he moved the courses 2 and 3 (math and cooking). So after this George can now ONLY teach 2 and 3, math and cooking, and he CANNOT teach courses 1 and 4, reading and science. Now that I have these values how do I enter them into the 'instructorCourse' table while removing the old values relating to the courses he cannot teach (1 and 4, reading and science)? My only thought was before entering the values I would execute this query:

[CODE]
DELETE FROM instructorCourse
WHERE instID = 1;
[/CODE]

This would then remove the rows:

instID  skillID
1            1
1            4

After doing that I would just enter the new courses into the table using an INSERT command. Is this the correct way to approach this or am I doing something wrong?

[pnj]

This approach works, but seems a bit inefficient given that each time a change is made, you are deleting and repopulating the instructor's entire course set, even if only one is being changed.  This would make the biggest difference if you had instructors teaching many courses, which I guess will not be that common.

In any case, it seems a bit more appropriate to do it like this:
[code]DELETE FROM instructorCourse WHERE instID=1 AND (skillId=1 OR skillId=4);[/code]

Cheers
-pnj

[CaptainSlow]

Thanks for the reply pnj, however I'm not quite sure I understand how that is better. Within this example I know which courses and course numbers George is wanting to change however in the real world the only thing I will know is that an array of selected courses will be passed from the form page to the processing page and any combination of courseIDs could be in that array. Therefore I wouldn't know what criteria, other than the instID, to enter into the WHERE clause. In order for me to use the way you suggested I think I would have to get the array, determine which courses are NOT in the array, determine whether the instructorCourse table has entries for the courses that are no longer in the array, if so then delete the rows, otherwise enter the new courses from the array. To me this sounds really complicated though I might be making it more complicated than it seems.

Perhaps offering a different example and getting help on that will help me understand how my approach is wrong or whether the approach proposed by pnj will fit my needs:

George decides he still wants to teach courseID 1 (reading), not longer wants to teach courseID 4 (science), and wants to start teaching courseID 2 and 3 (math and cooking).

In the final implementation of my application (not sure if that's the correct term) there will be 29 different courses and instructor can teach and from what I've seen of the real data some instructors are associated with up to 12 different courses. This may make my code (DELETE FROM instructorCourse WHERE instID = 1;) a poor choice to use due to its inefficiency, however, it's the only approach I really understand at this point.

[shoz]

I don't necessarily think that deleting 12 to 20+ rows on the (what I'd assume) rare occasion that someone is changing their courses would be a problem. However, you can achieve some efficiency by approaching the problem in the following way.

What's important in this system in my estimation is not what courses are removed but only what courses should be listed for the teacher. When the user submits their list you'll ignore the list of the courses that are not being taught and instead only look at the courses that will be taught.

You can then remove only entries from the table where the courseID is not one of the courses being taught and then insert those that are not yet in the table.

eg.
[code]
DELETE FROM table WHERE instID = 1 AND courseID NOT IN(1,2,3,4);
[/code]

You'll then generate the INSERT query using the array that was sumbitted.
[code]
INSERT IGNORE INTO table VALUES(1,1), (1, 2), (1,3), (1,4);
[/code]

The syntax shown for the INSERT above allows multiple rows to be inserted with one query. The IGNORE is used so that if we get a a "DUPLICATE KEY" error MYSQL will still do the INSERTs for the other rows. That will happen when you've tried to insert a courseID already associated with a teacher.

To have MYSQL ensure that you don't insert multiple courseIDs and subsequently issue the error mentioned above you'll have to put a UNIQUE key on (instID,courseID)

[code]
ALTER TABLE ADD UNIQUE(instID, courseID)
[/code]

You may lose some efficiency but I think that it's a good idea to have the logic show the intention of the script, meaning you don't rely on MYSQL to stop duplicate inserts but instead have your script insert only what is needed.

ie
[code]
SELECT courseID FROM table WHERE courseID IN (list of ids)
[/code]

You'd then remove the ids that are already in the table from the array and then only insert the others.

http://dev.mysql.com/doc/refman/4.1/en/insert.html
http://dev.mysql.com/doc/refman/4.1/en/create-index.html
http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

[CaptainSlow]

Thanks for the help and tips shoz! I'll give it a try later today.