Selecting in many to many relationship setup

Question

Good day. I have many to many relationship setup. I want to know if how can I group rows with same ids and reflect it as concatenated with each links on entries.

// from this                          // to this but with each link
----------Classes----------           ----------Classes----------
| Student Id | Lecture ID |           | Student Id | Lecture ID |
|     1      |      1     |           |     1      |  1; 2; 3   |
|     1      |      2     |           |     1      |    4; 2    |
|     1      |      3     |           ---------------------------
|     2      |      4     |           
|     2      |      5     |
---------------------------

Here is the query (without group concat):

$classes = "SELECT * FROM students, lectures, classes
WHERE students.student_id=classes.student_id
AND lectures.lecture_id=classes.lecture_id"

$class_result = mysqli_query($con, $classes);

while($class = mysqli_fetch_array($class_result){
  echo '<table><tr>';
  echo '<td><a href="student.php?id='.$class['student_id'].'">'.$class['student'].'</a></td>';
  echo '<td><a href="lecture.php?id='.$class['lecture_id'].'">'.$class['lecture'].'</a></td>';
  echo '</tr></table>';
}

----------Classes----------
| Student Id | Lecture ID |
|     1      |      1     |
|     1      |      2     |
|     1      |      3     |
|     2      |      4     |           
|     2      |      5     |
---------------------------

Here is the query (with group concat):

$classes = "SELECT students.student_id, student, lectures.lecture_id, GROUP_CONCAT(lecture SEPARATOR '; ')
FROM students, lectures, classes
WHERE students.student_id=classes.student_id
AND lectures.lecture_id=classes.lecture_id
GROUP BY student"

$class_result = mysqli_query($con, $classes);

while($class = mysqli_fetch_array($class_result){
  echo '<table><tr>';
  echo '<td><a href="student.php?id='.$class['student_id'].'">'.$class['student'].'</a></td>';
  echo '<td><a href="lecture.php?id='.$class['lecture_id'].'">'.$class["GROUP_CONCAT(lecture SEPARATOR '; ')"].'</a></td>';
  echo '</tr></table>';
}

----------Classes----------
| Student Id | Lecture ID |  // this works but it cannot reflect each link of id of lecture
|     1      |  1; 2; 3   |  // it only reflects link of one id for lecture
|     1      |    4; 2    |
---------------------------

Here is the actual PHP part:

<?php include ('connect.php'); ?>

<?php

$classes = "SELECT * FROM students, lectures, classes WHERE students.student_id=classes.student_id AND lectures.lecture_id=classes.lecture_id";

$class_result = mysqli_query($con, $classes);

?>

The HTML/PHP part:

<html>
<title>Classes</title>
<body>
<table border="1" cellspacing="0" cellpadding="10" width="500">
<tr>
    <td colspan="2" align="right">
    <a href="add_class.php">Add Class</a>
    </td>
</tr>
<tr>
    <th>Student</th>
    <th>Lecture</th>
</tr>
<?php while($class = mysqli_fetch_array($class_result)) { ?>
<tr>
    <td align="center"><a href="student.php?student_id=<?=$class['student_id']?>"><?=$class['student']?></a></td>
    <td align="center"><a href="lecture.php?lecture_id=<?=$class['lecture_id']?>"><?=$class['lecture']?></a></td>
</tr>
<?php } ?>
</table>
</body>
</html>

<?php include('close.php')?>

These are the tables:

----------Classes----------   // wanted to    ----------Classes----------
|  Student   |  Lecture   |       be like     |  Student   |  Lecture   | 
|   John     |    Math    |        this       |            |    Math;   |
|   John     | Literature |        --->       |   John     | Literature;|
|   Paul     |    Math    |      clickable    |            |  Physics;  |
|   Paul     |   Speech   |        each       |            |   Speech   |
|   Paul     | Literature |       lecture     ---------------------------
|   John     |   Physics  |                   |            |    Math;   |
|   Paul     |   Physics  |                   |   Paul     |   Speech;  |
|   John     |   Speech   |                   |            | Literature;|
---------------------------                   |            |  Physics   |
                                              ---------------------------

you could use `explode(';', $class['grouped'])` but you should be aware that `group_concat` has a size limit, I would also get rid of that space in the separator, and alias the field. `GROUP_CONCAT(lecture SEPARATOR '; ') AS grouped` Or you could just Order it by the field you are grouping on... — ArtisticPhoenix, Jan 17 '18 at 06:13
I would use `FETCH_GROUP` if you were using PDO. For mysqli I an not sure. — ArtisticPhoenix, Jan 17 '18 at 06:16

ArtisticPhoenix · Answer 1 · 2018-01-17T10:31:09.773

I would probably do something like this:

$classes = "SELECT * FROM students, lectures, classes
WHERE students.student_id=classes.student_id
AND lectures.lecture_id=classes.lecture_id  ORDER BY students.student_id";

$class_result = mysqli_query($con, $classes);

$student_ids = [];

while($class = mysqli_fetch_array($class_result)){
  //fixed missing ) - while($class = mysqli_fetch_array($class_result){

  if(!isset($student_ids[$class['student_id']])){
       if(count($student_ids) > 1) echo '</tr></table>'; //close previous list
       $student_ids[$class['student_id']] = true;
       echo '<table><tr>';
       echo '<td><a href="student.php?id='.$class['student_id'].'">'.$class['student'].'</a></td>';
  }
  echo '<td><a href="lecture.php?id='.$class['lecture_id'].'">'.$class['lecture'].'</a></td>';
}

if(count($student_ids) > 1) echo '</tr></table>'; //close last list

Note the Order By and the $student_ids array. This is so you will get

Student A link
- Lecture 1 link
- Lecture 2 link
- Lecture 3 link
Student B link
- Lecture 1 link

etc...

I would suggest using a description list:

<dl>
   <dt>Student A</td>
   <dd>Lecture 1</dd>
   <dd>Lecture 2</dd>
</dl>

But you will have to do some CSS on it. For me it seems like a better structure for this because it lets you eliminate a few tags and gives the student link a different tag. But that's up to you.

In any case, the HTML structure and "styling" is a minor detail once you have the data the way you want it. Not to mention there is no way to know how you want it to look given the information provided in the question.

That said, it seems like the lecture links are independent of a student. It seems to me like it should include something from the student or the links are uncorrelated. What I mean is what is the difference between.

Student A, Lecture 1

And

Student B, Lecture 1

Both Lecture links are the same.

This works @ArtisticPhoenix but it is not appearing as one row. Under the name a number of rows appear with the corresponding lectures. — Oween, Jan 17 '18 at 07:12
There I fixed it by adding the `` only when adding the student, and then closing it after the loop if a student was added. That should keep it with only one loop. — ArtisticPhoenix, Jan 17 '18 at 07:37
missing `)` in the Original question, I just copied and pasted it. here `while($class = mysqli_fetch_array($class_result){` — ArtisticPhoenix, Jan 17 '18 at 10:30

Mazhar Hussain · Answer 2 · 2018-01-17T07:29:59.683

0

<?php
$conn=mysqli_connect("localhost","root","","database");

$student_query="SELECT student_id FROM classes";
$student_result=mysqli_query($conn,$student_query);
while($row_student=$student_result->fetch_assoc())
{
   $student_id=$row_student['student_id'];

   $lecturer_query="SELECT lecturer_id FROM classes where student_id='$$student_id'";

   $lecturer_result=mysqli_query($conn,$lecturer_query);
   while($row_lecturer=$lecturer_result->fetch_assoc())
  { 
       $lecturer_id[]=$row_lecturer['lecturer_id'];
  }


  $result[]=array('student_id'=>$student_id,'lecturer_id'=>$lecturer_id);
}

  echo json_encode($result);

edited Jan 17 '18 at 07:29

answered Jan 17 '18 at 06:52

Mazhar Hussain

125
1
11

I would like to try these @Mazhar Hussain. If only it would be explained in detail. Thank you. – Oween Jan 17 '18 at 07:14
are you know php coding – Mazhar Hussain Jan 17 '18 at 07:17
i had edit my answer ok this is perfect code.put and run ok – Mazhar Hussain Jan 17 '18 at 07:30

score 0 · Answer 3 · answered Jan 18 '18 at 03:27

Finally after all the searching and fetching ideas. I have finally managed to make this work. This is how the code goes.

<?php

$classes = "SELECT * FROM students, lectures, classes WHERE students.student_id=classes.student_id AND lectures.lecture_id=classes.lecture_id GROUP BY students.student_id";

$class_result = mysqli_query($con, $classes);

while($class = mysqli_fetch_array($class_result)) {
    $student_id = $class['student_id'];
    $lectures = mysqli_query($con,"SELECT * FROM students, lectures, classes WHERE students.student_id=classes.student_id AND lectures.lecture_id=classes.lecture_id AND students.student_id='$student_id'");
    $counter = 0;
?>
    <tr>
        <td align="center"><a href="student.php?student_id=<?=$class['student_id']?>"><?=$class['student']?></a></td>
        <td align="center">
        <?php while($lecture = mysqli_fetch_array($lectures)) { ?>
            <a href="lecture.php?lecture_id=<?=$lecture['lecture_id']?>"><?=$lecture['lecture'];?></a>
        <?php } ?>
        </td>
    </tr>
<?php } ?>

Now the table looks like this:

-----------------------Classes----------------------------
|    Student    |              Lectures                  |
----------------------------------------------------------
|     John      |    Math Literature Physics Speech      |
----------------------------------------------------------
|     Paul      |    Math Speech Literature Physics      |
----------------------------------------------------------

Now I will be dealing with separators/delimiters for the lectures. (e.g. Math; Literature; Physics; Speech). Thank you for all the help.

Selecting in many to many relationship setup

3 Answers3