Correct way of making pivot table in php/mysql?

Question

I want to make a table for groupleaders where they can see the progression of their students.

The hierarchy of the content is: chapters, lessons, topics or tests

I made it work, but I'm wondering if this is the right way to do it. I use MySQL.

What I do is that I select the lessons of a chapter. In another query I select the students of a group. In another query I select the topics of a lesson. In another query I select the status of a topic.

The reason why I do it in different queries, is that I want to dynamicly pivot the information.

Is this the correct way?

This is the code:

    if($chapter_id != 'notchosen' && $group != 'notchosen') {
    
    // select lessons of current chapter
    $lessons = $wpdb->get_results( "SELECT
    a.post_title AS lessontitle,
    a.menu_order as menu_order,
    a.post_type AS post_type,
    a.ID as lesson_id,
    b.meta_value AS course_id
    FROM wp_posts AS a
    INNER JOIN wp_postmeta AS b
    ON b.post_id = a.ID
    WHERE a.post_type = 'sfwd-lessons'
    AND b.meta_key = 'course_id'
    AND b.meta_value = $chapter_id"
    );

    echo '<div class="resultaten" id="tabs">';
    echo '<ul>';
    foreach ($lessons as $lesson) {
      echo '<li>';      
      echo '<a href=#tabs-';
      echo $lesson->lesson_id;
      echo ' class=menubutton>';
      echo $lesson->lessontitle;
      echo '</a>';                      
      echo '</li>';
    };
    echo '</ul>';

    foreach ($lessons as $lesson) {
      
      // select students of group
      $leerlingen = $wpdb->get_results( $wpdb->prepare( "SELECT
      a.post_title AS groep,          
      c.display_name AS naam,
      MAX(d.activity_status) AS status
      FROM wp_posts AS a
      INNER JOIN wp_usermeta AS b
      ON b.meta_value = a.ID
      INNER JOIN wp_users AS c
      ON c.ID = b.user_id
      LEFT JOIN wp_learndash_user_activity AS d
      ON d.user_id = c.ID
      AND d.post_id = %s
      WHERE a.post_title = %s
      AND b.meta_key LIKE '%learndash_group_users%'
      GROUP BY naam
      ORDER BY naam ASC", $lesson->lesson_id, $groep ) 
      );


      // select topics of lesson
      $stappen = $wpdb->get_results( $wpdb->prepare( "SELECT
      a.post_title AS staptitel,
      a.menu_order as menu_order,
      a.post_type AS post_type,
      a.ID as stap_id,
      b.meta_value AS lesson_id
      FROM wp_posts AS a
      INNER JOIN wp_postmeta AS b
      ON b.post_id = a.ID
      WHERE (a.post_type = 'sfwd-topic' OR a.post_type = 'sfwd-quiz')
      AND b.meta_key = 'lesson_id'
      AND b.meta_value = %s
      ORDER BY post_type DESC, menu_order ASC", $lesson->lesson_id ) 
      );

      echo '<div id=tabs-';
      echo $lesson->lesson_id;
      echo ' class="schema">';

      echo '<table cellspacing="0" cellpadding="0"><tr>';
      
      echo '<td>';
      echo '<table class="resultaten-leerlingen" cellspacing="0" cellpadding="0">';
      foreach ($leerlingen as $leerling) {
        echo '<tr><td class="naam">';
        echo $leerling->naam;
        echo '</td><td class="indicator">';
        echo '<div class="resultaten-indicator" ';
        if ($leerling->status == '1') { echo 'style=background:green>'; } else if ($leerling->status == '0') { echo 'style=background:#32373c>'; } else { echo 'style=background:#32373c>'; };              
        echo '</div>';
        echo '</td></tr>';
      }
      echo '</table>';
      echo '</td>';
      
      foreach ($stappen as $stap) {

        // select status of topic
        $statussen = $wpdb->get_results( $wpdb->prepare( "SELECT
        a.post_title AS groep,
        b.user_id AS userid,
        c.meta_value AS voornaam,
        MAX(d.activity_status) AS status,
        e.post_title AS titel
        FROM wp_posts AS a
        INNER JOIN wp_usermeta AS b
        ON b.meta_value = a.ID
        INNER JOIN wp_usermeta AS c
        ON c.user_id = b.user_id
        LEFT JOIN wp_learndash_user_activity AS d
        ON d.user_id = c.user_id
        AND d.post_id = %s
        LEFT JOIN wp_posts AS e
        ON e.ID = d.post_id
        WHERE a.post_title = %s
        AND b.meta_key LIKE '%learndash_group_users%'
        AND c.meta_key = 'first_name'
        GROUP BY userid
        ORDER BY voornaam ASC", $stap->stap_id, $groep ) 
      );

Correct way of making pivot table in php/mysql?

0 Answers0