complex mysql query to create html table

Question

I made this mock-up for a project, and now I have to actually do it (in php) but I don't know how to get the data from the db so that I can create the html table.
Here is the definition of the tables involved:

grade:

grade_id | title        | sort_order 
-------------------------------
   1     | Preschool    |    10
   2     | PreK         |    20
   3     | Kindergarten |    30
   4     | Grade 1      |    40
   5     | Grade 2      |    50
   6     | Grade 3      |    60

competency_set:

competency_set_id | title                   | sort_order 
--------------------------------------------------------
   1              | Grouping and sequencing |    10
   2              | Conecting: orientation  |    20
   3              | Conecting: reference    |    30

competency:

competency_id | competency_set_id | grade_id | sort_order | title                                  
--------------------------------------------------------------------------------------------------
   1          |          1        |     1    |     10     | Procedure / Recount / Narrative Retell
   2          |          1        |     2    |     10     | Procedure preK
   3          |          1        |     3    |     10     | Procedure kindergarten
   4          |          1        |     4    |     10     | Procedure Grade 1
   5          |          1        |     5    |     10     | Procedure Grade 2
   6          |          1        |     6    |     10     | Procedure Grade 3
   7          |          1        |     2    |     20     | Recount / Narrative Retell PreK
   8          |          1        |     3    |     20     | Recount / Narrative Retell Kindergarten
   9          |          1        |     4    |     20     | Recount / Narrative Retell Grade 1
   10         |          1        |     5    |     20     | Recount Grade 2
   11         |          1        |     6    |     20     | Recount Grade 3

competency_set_id is a foreign key to competency_set.competency_set_id (PK).
grade_id is a foreign key to grade.grade_id (PK).

Ideally I'd like to have a result that allows me to loop in a very simple way, i.e.:

$rows = $this->getTableRows(); // the method that will return the mysql query result
foreach ($rows as $row) {
    echo '<tr>';
    echo '<td>' . $row->competencySetTitle . '</td>';
    echo '<td>' . $row->preschool . '</td>';
    echo '<td>' . $row->prek . '</td>';
    echo '<td>' . $row->kindergarten . '</td>';
    echo '<td>' . $row->grade1 . '</td>';
    echo '<td>' . $row->grade2 . '</td>';
    echo '<td>' . $row->grade3 . '</td>';
    echo '</tr>';
}

Here is a screenshot of the mock-up with the different parts (competencies, competency sets and grades) explained:

So what is your problem exactly? The MySQL query or the loop in PHP? — rlanvin
– rlanvin, Commented Aug 23, 2015 at 15:26
Ok, just to you know, it's going to be very inefficient to do this in pure MySQL because you need to pivot the grade table (feel free to search for "pivot table join"). I would recommend a double loop in PHP instead. — rlanvin
– rlanvin, Commented Aug 23, 2015 at 15:42

Carsten Massmann · Accepted Answer · 2015-08-23 16:23:12Z

Maybe this is not quite the solution you were looking for, but perhaps a starting point:

select competency_set_id, 
 group_concat(CASE grade_id WHEN 1 THEN title END ORDER BY competency_id SEPARATOR '<hr>') presch,
 group_concat(CASE grade_id WHEN 2 THEN title END ORDER BY competency_id SEPARATOR '<hr>') preK,
 group_concat(CASE grade_id WHEN 3 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Kinderg,
 group_concat(CASE grade_id WHEN 4 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade1,
 group_concat(CASE grade_id WHEN 5 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade2,
 group_concat(CASE grade_id WHEN 6 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade3
 FROM comp GROUP BY competency_set_id

Check out the little live Demo here.

I concentrated on the last table since this contains all the important data. The core problem is that you have potentially several competencies for each competency set and grade. In your suggested solution you have a "regular" table-layout with multiple lines per compentency set. This I cannot achieve with my simple query. Instead I separate the competencies inside each competency/grade-cell by <hr>s (just for demonstration purposes).

Just as food for thought: Are the individual lines inside each competency group in your solution table really properly connected in terms that they refer to something they have in common? If not, then maybe my simpler approach makes sense after all?

Complete solution in MySQL:

If, on the other hand, each of your table lines refers to some type of competency that spans over all grades then maybe you should supply this information also in your competency table as a new int column competency_type. This column should then point to (yet another) table of competency types you could set up in a separate table. But most of all this additional column will make it easy for you to achieve your desired result table layout with a simple sql query, see here for an updated demo

select competency_set_id ci,competency_type ct, 
 group_concat(CASE grade_id WHEN 1 THEN title END ORDER BY competency_id SEPARATOR '<hr>') presch,
 group_concat(CASE grade_id WHEN 2 THEN title END ORDER BY competency_id SEPARATOR '<hr>') preK,
 group_concat(CASE grade_id WHEN 3 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Kinderg,
 group_concat(CASE grade_id WHEN 4 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade1,
 group_concat(CASE grade_id WHEN 5 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade2,
 group_concat(CASE grade_id WHEN 6 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade3
 FROM comp GROUP BY competency_set_id,competency_type
 ORDER BY competency_set_id,competency_type

I have kept the possibility of having several competencies per cell in the query but once you have assigned all of your competencies to unique competency_types per grade you can safely replace the group_concats by simpler max() functions.

As rlanvin already pointed out: If you want to get your posted table layout as shown then it makes sense to do some collecting and tabulating in PHP instead of trying to do everything in an SQL query.
from what the client told me, it's not really competency types, but some kind of order in which to display them. I have tested your code replacing the competency_type by sort_order, and it really looks good. I have to go now, I will test more in depth tomorrow and will keep you posted.

Joshua Nightingale · Accepted Answer · 2015-08-23 15:48:07Z

As an example you can do something like this to get and display your results in a table. I have not tested this because I do not have your database so you will have to play around with it and give it a try.

$hostname = "localhost";
$user = 'USERNAME';
$pass = 'PASSWORD';
$dbname = 'DATABASE_NAME';
$conn = "mysql:host=$hostname;dbname=$dbname";
$err = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
$pdo = new PDO($conn, $user, $pass, $err);
$data = $pdo->prepare("SELECT `grade_id`, `title`, `sort_order` FROM `grade` ORDER BY `grade_id` ASC");
$data->execute();
$data->setFetchMode(PDO::FETCH_ASSOC);
echo '<table border="1"><tr><th>Grade ID</th><th>Title</th><th>Sort Order</th></tr>';
foreach ($data as $rows){
    echo '<tr align="center"><td>'.$rows['grade_id'].'</td><td>'.$rows['title'].'</td><td>'.$row['sort_order'].'</td></tr>';
}
echo '</table>';
$pdo = null;

Now all you have to do is use this example and design your table to be like your mock up.

Collectives™ on Stack Overflow

complex mysql query to create html table

grade:

competency_set:

competency:

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

grade:

competency_set:

competency:

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related