Elements of a PHP table appear repeated and others do not appear

Question

I’m putting together a page to display the courses registered within Moodle. For this, I am doing local tests, where I registered 4 disciplines and 5 users. The intention is to display these four disciplines with the names of the respective teacher(s) (s) below.

However, instead of appearing the 4 disciplines, there are 5 results with some anomalies, namely:

• Subjects with more than 1 teacher (3, in this case) appear repeated - each with the name of a different teacher. The idea is to appear only one div for this discipline and the name of the 3 teachers appear side by side.

• Discipline without a registered teacher does not appear.

• The only normality is that the two (2) disciplines with 1 teacher each appear normally, as expected.

As in Moodle, the roleid for teacher should be 3, and the background level for courses should be 50, I’m using the following code:

            <!-- Conteúdo das abas: graduação -->
            <div id="cursos" class="tabcontent">
                <?php 
                $sq1 = mysqli_query($conexao, "SELECT mdl_course.id AS iddocurso, mdl_course.fullname AS nomedocurso, mdl_context.instanceid, mdl_context.contextlevel, mdl_context.id, mdl_user.id, mdl_user.firstname AS nomedoprofessor, mdl_user.lastname AS sobrenomedoprofessor, mdl_role_assignments.roleid, mdl_role_assignments.contextid, mdl_role_assignments.userid, mdl_role_assignments.roleid

                FROM mdl_course 
                INNER JOIN mdl_context 
                      ON mdl_course.id=mdl_context.instanceid 
                INNER JOIN mdl_role_assignments 
                      ON mdl_context.id=mdl_role_assignments.contextid 
                INNER JOIN mdl_user 
                      ON mdl_user.id=mdl_role_assignments.userid 

                WHERE mdl_role_assignments.roleid = '3' AND mdl_context.contextlevel = '50'") 
                
                or die(
                mysqli_error($conexao)
                );
                
                while($aux = mysqli_fetch_assoc($sq1)) { 
                echo '<a href="meusite.php?id='.$aux["iddocurso"].'" target="_blank">';
                echo '<div class="grid__item large--three-tenths medium--five-tenths">';
                echo '<img src="imagens/imagem.jpg" alt="'.utf8_encode($aux["nomedocurso"]).'" title="'.utf8_encode($aux["nomedocurso"]).'">';
                echo '<p class="h6">'.utf8_encode($aux["nomedocurso"]).'</p>';
                echo '<p class="professor"> Professor: '.utf8_encode($aux["nomedoprofessor"]).' '.utf8_encode($aux["sobrenomedoprofessor"]).'</p>';
                echo '<span>Acessar</span>';
                echo '</div>';
                echo '</a>';                
            }
        
                ?>
            </div>

Detail on the image: Tutoring in Mathematics appears 3 times (because it has 3 teachers); History does not appear because there is no teacher; Psychology and Geometria Analítica are correct.

Each course has an ID. I suspect I have to work with this ID to display the 4 courses with their respective teachers correctly, but I do not know how to proceed in this case. Any thoughts on that, fellas?

Grateful!

Answer 1

Hello... The problem is in the SQL query. You are asking the database only courses with registered teachers (through the INNER JOIN), so that everyone comes (even without teachers), use the LEFT JOIN. On duplication, use the DISTINCT, but remember it looks at the returned fields and removes the rows where all are different, so you may need to remove some column from the query.

SELECT DISTINCT
    mdl_course.id AS iddocurso,
    mdl_course.fullname AS nomedocurso,
    mdl_context.instanceid,
    mdl_context.contextlevel,
    mdl_context.id,
    mdl_user.id,
    mdl_user.firstname AS nomedoprofessor,
    mdl_user.lastname AS sobrenomedoprofessor,
    -- provavelmente as duplicações ocorrem nos campos abaixo
    mdl_role_assignments.roleid, 
    mdl_role_assignments.contextid,
    mdl_role_assignments.userid,
    mdl_role_assignments.roleid
    FROM
        mdl_course 
        INNER JOIN mdl_context ON mdl_course.id=mdl_context.instanceid 
        LEFT JOIN mdl_role_assignments ON mdl_context.id=mdl_role_assignments.contextid 
        LEFT JOIN mdl_user ON mdl_user.id=mdl_role_assignments.userid 
    WHERE
        mdl_role_assignments.roleid = '3' AND mdl_context.contextlevel = '50'"