Hiding duplicate rows in php/myqli?

Question

i hope you dng good,i m working in school project that manage all influence inside it,i got a problem as the image descrip it duplicate each row my purpose is to show just one 'MATIERE' with all 'NOTE_DEVOIR'(devoir in the table),but i got each matiere duplicate as much of 'devoir' we have in this one.

NOTE:a teacher add 'NOTE_DEVOIR' with their 'MODULE_DEVOIR' ,it mean the 'MODULE' are duplicate each time with 'NOTE_DEVOIR' in database

I NEED TO HIDE NOT TO DELETE

PHP :

echo"<br><button class='btn btn-info cursor' name='butAf'>Afficher</button>";



if(isset($_POST['butAf']))
{        

    /*ID_CLASS and ID_ANNEE just to filter the class and the year of study*/
$query5 = "select * from devoir inner join prof_an_cls on  prof_an_cls.ID_PROF_AN_CLS = devoir.ID_PROF_AN_CLS where CIN_ELEVE = '$cinE' and ID_ANNEE=$ID_ANNEE and  ID_CLASS = $ID_CLASS";
$result5 = mysqli_query($con,$query5);
if(mysqli_num_rows($result5)>0)
{
    echo"<table class='table table-bordered table-striped table-hover table-info'><tr>
    <th>MATIERE</th>
    <th>devoir1</th>
    <th>devoir2</th>
    <th>devoir3</th>
    <th>devoir4</th>
    <th>devoir5</th>

    </tr>
    ";
    while($rows = mysqli_fetch_assoc($result5))
    {/*i guess this loop its the reason of duplicating */
       $moduleD = $rows['MODULE_DEVOIR'];

    $queryDEV = "select * from devoir inner join eleve on devoir.CIN_ELEVE = eleve.CIN_ELEVE where eleve.CIN_ELEVE = '$cinE' and MODULE_DEVOIR = '$moduleD' order by MODULE_DEVOIR";

    $resultDEV = mysqli_query($con,$queryDEV);
        echo"<tr><td>$moduleD</td>";     

           while($rows = mysqli_fetch_assoc($resultDEV))/*loop2*/
            {
                $noteDEV = $rows['NOTE_DEVOIR'];

                  echo"<td>$noteDEV</td>";


              }


        echo"</tr>";
    }



    echo"</table>";
}
}

based on the image adding the keyword distinct to the select may do it. but I would like to know why you have duplicate values. The inner join may be causing the duplication perhaps you're missing join criteria? What is the PK/FK relationship between devoir and prof_an_cls? and devoir & eleve? — xQbert, Feb 26 '18 at 16:39
@xQbert i already find the answer but i would explain, the duplicate error is a result of a the teacher when he want to add a 'note',because in each 'note' its obligatory to add the name of 'matiere',it mean with each select he should print that name of matiere,(relationship : eleve pass a devoir,that devoir have relation with the class and the year[to filter data]),i hope you take an idea :) thaanks — medo100000S, Feb 26 '18 at 16:46

Toleo · Answer 1 · 2018-02-26T16:50:28.643

3

You either use

DISTINCT

SELECT DISTINCT column . . . WHERE . . .

GROUP BY

SELECT column . . . WHERE . . . GROUP BY column

You can check the differences in this answer

edited Feb 26 '18 at 16:50

answered Feb 26 '18 at 16:47

Toleo

764
1
5
19

score 0 · Accepted Answer · answered Feb 26 '18 at 16:38

0

On your $queryDEV, simply add GROUP BY devoir.matiere (I assume the column name is 'matiere'), and you should get non-duplicated rows

answered Feb 26 '18 at 16:38

Adlan Arif Zakaria

1,706
1
8
13

Welcome! Please mark this as answer, so others with the same problem may get answers easily. – Adlan Arif Zakaria Feb 26 '18 at 16:45
yeah sure just they told me i should wait another 2min ,dont worry bro i will :) – medo100000S Feb 26 '18 at 16:47
3

Group by without aggregation makes little to no sense in SQL. `Distinct` is meant to eliminate duplicate values. In mySQL the group by is extended which allows this to happen but it can result in unexpected/anticipated results. The current versions of mySQL will fail this as the default setting has been changed to disable the extended group by. While this may work; it may not work on future versions; it may return unexpected results. Caution is warranted in my opinion. It may work and it may meet your needs but you need to understand why it works and when this option could be used. – xQbert Feb 26 '18 at 16:53

Hiding duplicate rows in php/myqli?

2 Answers2