Many to Many Problem

[saidbakr]

Hi
Suppose this situiation,
The relationship between stuents and subjects they are studing in a school is descriped as M:M
(Many to Many).
So, To handle it, it must be handled in three tables, let them as follows,
student table which will handle student entity
subject table which will handle subject entity
student_subject which will handle the studied subjects entity by a student
That's True?
Ok...
Let we go further,
For the purpose of my post I will make things as simple as possible, Now I will set a simple
design for each table as follows:

Student Table

ID | PK |
Student_Name | |

Birth_Date | |

Subject Table

ID | PK |
Subject_Name | |

Top_Degree | |

Student_Subject Table

stu_id | PK |

Sub_id | PK |

Now Let we populate them with DATA, they may appear as follows:

Student Table:

ID | Student_Name | Birth_Date |

1 John Doe 1980-01-26
2 Michel Jackson 1977-05-01
3 Said Bakr 1977-11-19
4 Nancy Chan 1981-02-23

5 Ghandi Amitab 1978-10-01

Subject Table

ID | Subject_Name | Top_degree |

1 PHP 100
2 DHTML 80
3 MySQL 100

4 HTML 50

Student_Subject Table

stu_id | sub_id |

1 2
1 4

3 2

Ok...
To retrive information from the data above, the following SQL query may be done:

SELECT student.Student_Name, subject.Subject_Name FROM
student, subject, student_subject WHERE
student.ID = student_subject.stu_id AND
student_subject.sub_id = subject.ID;

This will produce result as shown,

Student_Name | Subject_Name

John Doe | DHTML
John Doe | HTML

Said Bakr | DHTML

My Question is:
What's your sugestions to code a PHP code inwhich these information may be displayed as follows,

Student John Doe studied DHTML, HTML
Student Said Bakr studied DHTML,

You should notice the real world application will deal with more data and the performance issue
must be regarded.
The critical point here, is the record set will contain the same name twice (John Doe),
So on using a function such as mysql_fetch_array(), the returned array will includes two elements
with the same key value, so on looping throgh it (John Doe) will appears twice!?
Is there any way to make the result produced from the SQL query to be as follows:

---

Student_Name | Subject_Name

John Doe | DHTML
| HTML

Said Bakr | DHTML

So the repeated fields will have only one value.

[sfullman]

THere's no way in SQL to return from this list

1 5
1 6
1 7
1 8

"John (1) studied HTML(5), DHTML(6) ... etc."

i.e. no way to return a cell stating "HTML, DHTML, .. "

just order by student, then by class, filter your students and classes in the WHERE clause, adn build a php array. Arrays are very fast. Your "join" table is should be a double primary key, so it will be fast, index names in the two parent tables if you want more speed.

It's best to get this into an array with all key elements FIRST, and then present it. I'm the master of getting break points but I'm moving into getting the array FIRST, then looping through the array.

Sam

[broc7]

I answered a similar question here. (Actually it's really the same question.)

You don't need to build up an array, although it's certainly a solution. All you want to do is suppress the <br /> and student's name and print a comma instead if the current student name has already been printed during the last record loop.

[sfullman]

Yes, I refer to brok's thing as buffering, which I've done many times before.

The problem I'm finding with inline buffering (and it's a great skill if you have it, and you can buffer on multiple levels and feel you've accomplished great things and all) is that sometimes you want to do things with the <TR> and <td> styles, or even affect rows before, that you couldn't otherwise affect.

Getting things into an array first is helpful to have you think through the data you'll need beforehand. And like me, if you start getting heavy into CSS AFTER and want to go back and change things some times it's a bit easier.

Bottom line, since this is an SQL forum, is there's no way to get a cell with a comma delimited list of "skill1, skill2, .. skillN" directly as a field from the sql query (that doesn't look very ugly and cumbersome).

Sam

[broc7]

Sam:

I once wrote a program in perl that does what you call inline buffering to 5 levels. (Yes, I did feel that I accomplished great things.)

I did find a way to affect previous lines. It was a bit of a hack. It looked like this:

Start loop
if not first record, output stored previous ending depending on current record
Start record output
Store record ending in variables for next loop
End Loop
Output last stored ending

So depending on what was in the next record, I could change how the previous record ended.

One advantage to building an array not mentioned is that it wouldn't matter in what order the data was received. With buffering, it has to be in a precise order.

[sfullman]

Yes exactly, sorting slows down the query..

[mtimdog]

using your sql code from the first post

<?php
$first='true';
$person='';
while ($row=mysql_fetch_array($query_result)){
if ($person!= $row['Student_Name']){
     if ($first == 'true') {
          $first='false';
     }else{//close the last row
          echo '</td></tr>';     

     }
   $person=$row['Student_Name'];
   echo '<tr><td>';
   echo 'Student ' . $person . ' studied ' . $row['Subject_Name'];
}else{//only need to echo out the subject name
    echo ', ' . $row['Subject_Name'];

}//end else
}//end while
echo '</td></tr>';//close out the last row

[edit]I put in a space so it outputted cleaner, btw this is not tested.[/edit]

[saidbakr]

Ok,
So the solution can be found, only, on the PHP code side, not SQL.
That's great!
Building on your valuable advices, I'm currently working on class. It may be usefull to be a solution. When I finish it, I would show you it to share ideas about.
Thank You All Very Much. 🙂

[saidbakr]

This is the class:
You should note that,
there are two major limitations in this class
1- it only works on query results contains only two fileds.
2- The repeated field's value must be firstly selected in the sql_str.

<?
class Fox_filter {
    //Declare Variables
    var $host;
    var $user;
    var $pass;
    var $db;
    var $sql_str;
    var $unique_field_name;//The field name which has repeated records
    function connect(){
        $con = @mysql_connect($this->host,$this->user,$this->pass) or die("could not connect the server");
        mysql_select_db($this->db,$con) or die("could not select a db");
        $query_res = mysql_query($this->sql_str, $con) or die(mysql_error());
        $last_value = '';
        /*It'll handle the last key's value of $row array,
        To filter the repeated kies values and make it unique*/

    while ($row = mysql_fetch_assoc($query_res)){

        foreach ($row as $key => $value){

            if ($key == $this->unique_field_name){

                if ($last_value != $value){
                    $last_value = $value;
                }
            }
            else{
                $res_arr[$last_value][] = $value;
                //Two Dimentional array That hadles filtered values
            }
        }
    }
    return $res_arr;
}

}
 ?>

and this the application may use it

<?
require_once "uniqe.class.php";
$fox = new Fox_filter;
$fox->host = "localhost";
$fox->user = "MeAdmin";
$fox->pass = "Vb7LLpe4";
$fox->db   = "school";
$fox->unique_field_name = "Student_Name";
$fox->sql_str = 'SELECT student.Student_Name, subject.Subject_Name FROM
student, subject, student_subject WHERE
student.ID = student_subject.stu_id AND
student_subject.sub_id = subject.ID';
//You should Note:
//The repeated field must be the first selected field in The SQL query string
//So the class object be able to filterate its repeated values to be Unique
//++ You must define the above data member of the class as they named above
?>
<pre>
<?
$res = $fox->connect();
print_r($res);
?>
</pre>
<hr />
Another way to display
<pre>
<?
foreach($res as $key => $value){
    echo "The student $key has studied ";
    foreach($value as $key => $value){
        echo " $value,";
    }
    echo "\n";
}
?>
</pre>

So what's your comments and suggestions to overcome these limitations?

[broc7]

I'm thinking you loop through each row of a result set as one often does. You go into the loop and, in an inner loop determined by the row length or number of fields, compare the value of each result value to the value of the previous record, stored in a holding array. In each inner record loop, an array value is only printed out when it differs from the value the same field in the previous record had, but then it prints out every remaining field in the record without comparing them to their predecessors. Before the end of the outer loop, you store the current record in the holding array for comparison to the next record.

For extra credit, you can assign css classes to the output based on the field's postion, like class=f1, and then choose to display field values as block, inline, bold, or whatever, depending on content.

I didn't code this, but the idea should work for n records with n fields.