SELECT Many-to-Many loop

[troelss]

I'm developing a 'teacher subject' system in PHP with MySQL as database. But I can't figure out how to make my joins and loops to show multiple teachers when checking off several subjects.

The system I'm trying to make is a lot like this: http://www.brics.dk/jwig-mis/Censor/Censor*Search? (Danish site)

 

I have a table of subjects, teachers and the 'helper-table' which combines them as many-to-many:

TEACHERS:

TID

NAME

1

Mary Smith

2

Larry Anderson

3

Paul Olsen

 

SUBJECTS:

SID

SUBJECT

1

Math

2

Biology

3

Chemistry

4

Computer Science

 

 

TEACHERS_SUBJECTS

TID

SID

1

2

1

3

1

4

2

4

3

2

3

3

 

The list of all subjects in the database is printed out on the screen with a checkbox for each like:

[ ] Math <- checkboxes

[ ] Biology

[ ] Chemistry

[ ] Computer Science

etc.

[search for teachers by subjects button] <- submit button

 

A user should then be able to select multiple subjects and when the seach-button is pressed show the results of the teachers that match all the checked subjects.

 

The code I have so far:

 

INDEX.PHP

<?php include('db_connection.php') ?>
<form action="show_teachers.php" method="post"><input name="search" type="submit" value="Search"><br>
<?php 
  $query1 = "SELECT * FROM subjects ORDER BY name";
  $result1 = mysql_query($query1) or die;
  
  $num1=mysql_numrows($result1);
  $i1 = 0;
  while ($i1 < $num1) {
  $subjects_name = mysql_result($result1,$i1,"name");
  $subjects_subject_id = mysql_result($result1,$i1,"sid");
?>
<label>
<input type="checkbox" name="checkbox[]" value="<?php echo $subjects_subject_id ?>" id="<?php echo $subjects_subject_id ?>">
<?php echo $subjects_name ?></label><br>  
<?php $i1++; } ?>
<br><br>
</form>

 

SHOW_TEACHERS.PHP

<?php
$checkbox=$_POST['checkbox'];
foreach ($checkbox as $checkboxes)
{	
   //I don't know what to do here. I want all the teachers displayed who match the subjects checked. 
  //I think I need a loop to go through all the teachers by first joining the tables and 
//then select all teachers where sid = 'checkbox-value1' AND 'checkbox-value2' AND ... if more than two subjects were selected. 
}
?>

 

Can anybody help me with the loop og how to make the MySQL string "dynamic" by adding the checkboxes in the sid = x AND x AND x; in a SELECT name FROM .... INNER JOIN ... ON .... - string.

 

 

[The Little Guy]

I believe something like this should work:

 

$checkbox=implode(",", $_POST['checkbox']);
$sql = mysql_query("select t.TID, t.NAME from SUBJECTS s
left join TEACHERS_SUBJECTS ts using(SID)
left join TEACHERS t using(TID)
where s.SID in($checkbox)");
while($row = mysql_fetch_assoc($sql)){
    echo "<p>#{$row['TID']} ... {$row['NAME']}</p>";
}

 

In my where clause, I use "in" which is the comma separated list of subject ID's

[troelss]

I believe something like this should work:

 

$checkbox=implode(",", $_POST['checkbox']);
$sql = mysql_query("select t.TID, t.NAME from SUBJECTS s
left join TEACHERS_SUBJECTS ts using(SID)
left join TEACHERS t using(TID)
where s.SID in($checkbox)");
while($row = mysql_fetch_assoc($sql)){
    echo "<p>#{$row['TID']} ... {$row['NAME']}</p>";
}

 

In my where clause, I use "in" which is the comma separated list of subject ID's

 

It's almost what I need, but the problem is the "in", because I only need the distinct teachers who match all subjects checked. This scripts gives me all the names of several teachers, I need those who match.

 

Say we have two teachers, both teachers, teach the subjects MATH and COMPUTER SCIENCE. But then I add a third teacher to the database who teaches in MATH and BIOLOGY. If I check off the MATH and BIOLOGY I only get one (the third teachers name), not all the teachers who teach in MATH, but only the ones teaching in both those distinct checked off subjects.

 

I'm really grateful for the help! I hope someone can help me with this last hurdle.

[ebmigue]

Try this:

$sql = mysql_query("select DISTINCT t.TID, t.NAME from SUBJECTS s
inner join TEACHERS_SUBJECTS ts using(SID)
inner join TEACHERS t using(TID)
where s.SID in($checkbox)");

 

In general, avoid using LEFT JOINS, as they generate the NULL "value." NULL ought not to be supported in a truly Relational System.

 

 

Hope it helps.

[troelss]

Try this:

$sql = mysql_query("select DISTINCT t.TID, t.NAME from SUBJECTS s
inner join TEACHERS_SUBJECTS ts using(SID)
inner join TEACHERS t using(TID)
where s.SID in($checkbox)");

 

In general, avoid using LEFT JOINS, as they generate the NULL "value." NULL ought not to be supported in a truly Relational System.

 

 

Hope it helps.

 

I still get a list of all the ones matching both checked off criteria, not the ones matching only all the criteria checked. The "in" makes it select for example: MATH or BIOLOGY subjects, not the MATH and  BIOLOGY subjects - I need the AND, but can't get my string to do this. And all the values where both have to match a Teacher, before the teachers name is shown.

 

My suggestion is something like this: (What am I doing wrong?)

$checkbox=implode(" AND ", $_POST['checkbox']);
$sql = mysql_query("SELECT t.tid, t.name FROM subjects s INNER JOIN teachers_subjects ts USING(sid) INNER JOIN teachers t USING(tid) WHERE s.sid = ($checkbox)");

 

The code above should in my eyes give me all the results which match criteria: "x AND x AND x AND x" etc. But when I execute the string, it only shows the results from the first value x.

 

Do I need two SQL-strings for this operation and/or some PHP?

 

Thank you in advance!

 

[ebmigue]

I think I didn't correctly grasped what you wanted.

 

Anyway, is this what you want?

List all teachers t that teaches the selected subjects s?

 

If so, the query ought to be like this:

$checkbox=implode(",", $_POST['checkbox']);
$sql = mysql_query("
SELECT DISTINCT t.TID, t.NAME, s.SUBJECT, FROM teacher_subjects ts NATURAL JOIN teachers t NATURAL JOIN subjects s
WHERE s.SID IN ($subjects);
");

 

Hope it helps.

[troelss]

I think I didn't correctly grasped what you wanted.

 

Anyway, is this what you want?

List all teachers t that teaches the selected subjects s?

 

If so, the query ought to be like this:

$checkbox=implode(",", $_POST['checkbox']);
$sql = mysql_query("
SELECT DISTINCT t.TID, t.NAME, s.SUBJECT, FROM teacher_subjects ts NATURAL JOIN teachers t NATURAL JOIN subjects s
WHERE s.SID IN ($subjects);
");

 

Hope it helps.

 

I'm not sure if we understand each other correctly. It's again almost what I want. But I still get all the teachers who teach in one of the subjects, and not (if two selected) both subjects.

 

My example:

I select:

Subjects:

 

[x] Math (sid = 60)

[x] Biology (sid = 72)

 

With the sql-string above I get:

 

Teacher 1 (Teaches Math)

Teacher 2 (Teaches Math)

Teacher 3 (Teaches Math)

Teacher 4 (Teaches Math) <- correct

Teacher 4 (Teaches Biology) <- correct

Teacher 5 (Teaches Biology)

Teacher 6 (Teaches Biology)

 

The result I'm looking for is:

 

Teacher 4 (Teaches Math)

Teacher 4 (Teaches Biology)

 

Then I can make a "Distinct" on name and I will get: Teacher 4s name. (Math & Biology) not one or the other.

 

------

Is this example possible with the string above, because then I have made an error. Or is it understandable, what I'm trying to accomplish? :-)

 

 

[Muddy_Funster]

what about:

<?php
$checkbox=$_POST['checkbox'];
$counter = 1;
$append_sring = '';
foreach ($checkbox as $checkboxes)
{	
  if($counter == 1){
$append_string .= " WHERE sid='$checkboxes'";
$counter = 2;
}
else{
$append_string .= " AND sid='$checkboxes'";
}
}
$append_string .= "GROUP BY teachers.name";
$query_string = "SELECT name FROM teachers RIGHT JOIN subjects ON teachers.sid = subjects.sid $append_string";
$result_set = mysql_query($query_string) or die ('Fatal Error:<br>'.mysql_error());
while ($each_row = mysql_fetch_assoc($result_set){
echo "{$each_row['name']} - teaches all the chosen subjects"
}
?>

 

Not tested and probably full of holes, but it should give you something closer to what you are looking for. - Obviously replace field/table names as appropriate.

[iblood]

$sqlcond = '';
foreach ($checkbox as $checkboxes) {
    $sqlcond .= 'SID=' . $checkbox . ' OR '; 
}
if ($sqlcond !== '') {
    // Get all teachers with SIDs
    $sql = "SELECT `TID` FROM  `TEACHERS_SUBJECTS` WHERE " . rtrim($sqlcond, ' OR ');
    $res = mysql_query($sql);
    
    // Create a buffer of teachers
    $teachers = array();
    while ($row = mysql_fetch_assoc($res)){
        $teachers[$row['TID']][] = 1; 
    }
    
    $tnameSqlExt = '';
    foreach ($teachers as $tid => $t) {
        // check if teacher has all the SID from checkbox
        if (count($t) == count($checkbox)){
            // at this point we are sure that the teacher has all the SID requested
            $tnameSqlExt .= 'TID=' . $tid . ' OR ';
        }
    }
    
    // display teachers names
    if ($tnameSqlExt !== '') {
        $tSql = "SELECT `NAME` FROM `TEACHERS` WHERE " . $tnameSqlExt;
        $res = mysql_query($tSql);
        while ($row = mysql_fetch_assoc($res)){
            echo $row['NAME'] . '<br />';
        }
    }
    
}

 

Hope this workd 

[mikosiko]

@troelss

 

Try this:

SELECT a.tid,a.name
FROM teacher_subjects t
   JOIN teachers As a ON a.tid = t.tid
WHERE t.sid IN (60,72)
GROUP BY a.tid
HAVING count(a.tid) > 1;   // here you must use a variable instead of 1, with value = #subjects searched - 1

[ebmigue]

I see now.

 

Expressed as a command:

 

Get all teachers who teaches all the subjects selected.

 

Is that it?

 

If so, I think the query ought to be similar to this:

$list_of_subjects_selected =implode(",", $_POST['checkbox']);

$sql = "
SELECT DISTINCT t.TID, t.NAME, s.SUBJECT FROM (
  SELECT COUNT(SID) AS 'subj_count' FROM subjects WHERE SID IN($list_of_subjects_selected)
)s INNER JOIN (
    SELECT DISTINCT t.TID, t.NAME, COUNT(_s.SID) AS 'subj_count' FROM teacher_subjects ts NATURAL JOIN subjects _s NATURAL JOIN   
    teachers t WHERE _s.SID IN ($list_of_subjects_selected) GROUP BY t.TID, t.NAME
)t ON s.subj_count = t.subj_count
NATURAL JOIN teacher_subjects ts NATURAL JOIN subjects s
";


$sql = mysql_query($sql);
while($row = mysql_fetch_assoc($sql)){
    echo "<p>#{$row['TID']} ... {$row['NAME']}...{$row['SUBJECT']}</p>";
}

 

Most probably there are "variations of the same theme" for this query. Try this first.

 

 

Hope it helps.

 

P.S. If this still won't work, we'd appreciate if you include an SQL script file in your next post with the table definitions, sample data, and desired results. THis is so we could test it. Thank you.