PHP / MySql report using distinct and group?

Question

I am trying to write a PHP report for a MySql table however I am a bit stuck on the correct commands I should be looking in to...

The table structure is as follows:

|  Int  |  employeeID  |  lessonID  |  date     

|  1    |  15110       |  50        |  2010-10-18
|  2    |  15110       |  51        |  2010-10-18
|  3    |  15110       |  52        |  2010-10-18
|  4    |  15110       |  50        |  2010-10-18
|  5    |  13211       |  51        |  2010-10-18
|  6    |  13211       |  52        |  2010-10-18
|  7    |  11542       |  60        |  2010-10-18

So everytime a lesson is opened a new row is added with the employeeID and lessonID.

The report I am trying to create would show the people who have accessed lessonID 50, 51 and 52.

For example the report would look something like:

|  employeeID  |  lessonID 50  |  lessonID 51  |  lessonID 52  |

     15110           Y                 Y               Y
     13211           N                 Y               Y

What I have so far for my SQL query is:

SELECT DISTINCT employeeID, lessonID

FROM elearning.el_lessonmetrics

WHERE lessonID = 50 OR lessonID = 51 OR lessonID = 52;

Which gives:

|  employeeID  |  lessonID  |
    15110          50
    15110       51
    15110       52
    13211       50
    13211       51

Can anyone help me with how to shape those results into the report structure above?

Thanks

Answer 1

What you're looking for is known as a pivot table.

There are several tutorials available on the internet for MySQL:

• WikiBooks
• Mysql Tech-Resources
• Well House
• EHow

Now, as far as your specific query, it depends on if the number of lessons are known before hand. If so, you could just join on itself for each lesson:

SELECT a.employeeID, 
    CASE WHEN j1.lessonID IS NULL THEN 'N' ELSE 'Y' END AS `lessionID50`,
    CASE WHEN j2.lessonID IS NULL THEN 'N' ELSE 'Y' END AS `lessionID51`,
    CASE WHEN j3.lessonID IS NULL THEN 'N' ELSE 'Y' END AS `lessionID52`
FROM elearning.el_lessonmetrics AS a
    LEFT JOIN elearning.el_lessonmetrics AS j1 ON a.employeeID = j1.employeeID
    LEFT JOIN elearning.el_lessonmetrics AS j2 ON a.employeeID = j2.employeeID
    LEFT JOIN elearning.el_lessonmetrics AS j3 ON a.employeeID = j3.employeeID
WHERE (j1.lessonID = 50 OR j1.lessonID IS NULL)
    AND (j2.lessonID = 51 OR j2.lessonID IS NULL)
    AND (j3.lessonID = 52 OR j3.lessonID IS NULL)
GROUP BY a.employeeID

If you don't know the number of lessons, or there are a large number of them then this method won't work too well (and you're likely better off post-processing the results in PHP to build your table)...

For example:

SELECT DISTINCT employeeID, lessonID
FROM elearning.el_lessonmetrics
WHERE lessonID IN (50, 51, 52);

Then, in PHP

$employees = array();
$lessons = array();
while ($row = getRowFromDatabase()) {
    $lessons[$row['lessonID']] = true;
    $employees[$row['employeeID']][$row['lessonID']] = true;
}
foreach ($employees as &$employee) {
    foreach ($lessons as $lessonId => $status) {
        if (!isset($employee[$lessonId])) {
            $employee[$lessonId] = false;
        }
    }
}

That will result in an array like:

array (
    15110 => array(
        50 => true,
        51 => true,
        52 => true,
    ),
    13211 => array(
        50 => true,
        51 => true,
        52 => false,
    ),
)