MySQL query searching (advanced)

I have a course page, this page is setup to display the details (easy), then who is teaching it first, second, third, and forth period of each semester. The problem with that is, my teachers data is handled in one giant chain.

Bobby开发者_StackOverflow社区: 1-1-1-1-1-1-1-1

Tina: 20-20-20-10-1-1-1-1

Joey: 20-1-1-1-49-432-10-19

What I want to do is find a course: 20, when all of the information is shown a search through teachers would be made to find out who is teaching course 20 and what period?

Ex.

Course id: 20
Period 1: Joey, Tina
Period 2: Tina
Period 3: Tina

I want to get every teacher teaching that course in one search but given the obstacle of the capact data which may be a problem.

Teacher Table: id / name / link / course (1-1-1-1-1-1-1-1)

Course Table: id / name / code / grade / level

Teachers Course contains the id's from course Table

While re-structuring your db might be the best answer, I thought I'd post a straight php solution that works with your current structure and presumptively the rest of your code.

//I set up variables to contain your expected search results so I could test
$search_course = '20';
$search_results = array(
    'Bobby' => '1-1-1-1-1-1-1-1',
    'Tina' => '20-20-20-10-1-1-1-1',
    'Joey' => '20-1-1-1-49-432-10-19'
    );

//explode the course strings into arrays and store with teacher names so 
//you can loop through them later   
foreach($search_results as $teacher=>$string_courses){
    $array_courses = explode('-',$string_courses);
    $search_results[$teacher] = $array_courses;
    }

//Match course you are searching for to the elements in your array
//Create a result array with period and matching teachers
foreach($search_results as $teacher=>$courses){
foreach($courses as $period => $course){
    if($course == $search_course){
            $results[$period][] = $teacher;
            }
        }
    }

//Loop through your result array and show the results
//I expect you'll have different html for this
foreach($results as $period => $teachers){
    echo 'Period: ';
    echo $period+1;
    echo implode(',',$teachers);
    echo '<br>';
}

The printed results match the list you wanted in your OP

Create a new table

Something like this:

CREATE TABLE  `TeacherToPeriod` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`TeacherID` INT NOT NULL ,
`CourseID` INT NOT NULL ,
`Period` INT NOT NULL
) ENGINE = MYISAM ;

Insert the data

Here is some PHP-Code:

foreach($lines as $line){
    $line_data = split($line, ': ');
    $teacher = $line_data[0];
    // SELECT your $teacher_id from the database

    $courses = split($line_data[0], '-');
    $i = 0;
    foreach($courses as $course_id){
       $i++;
       $sql = "INSERT INTO  `TeacherToPeriod` (`TeacherID` ,`CourseID` ,`Period`) ";
       $sql.= "VALUES ($teacher_id,  $course_id,  $i);"
       mysql_query($sql);
    }
}

Select the data you want

SELECT * FROM `TeacherToPeriod` WHERE `CourseID` = 20 ORDER BY `Period` ASC;

You should change the structure of your db, instead of storing a string of all the periods, you should have an additional table with three columns: teacher,course,period and have a separate row in this table for each course that a teacher is teaching. Then determining who is teaching what course would simply be a matter of querying that table by course id and then sorting by period. e.g:

SELECT teacher_id, course_id, period FROM course_info WHERE course_id = 20 
ORDER BY period;