HTML table from MySQL with most recent and unique values

Question

Not sure what to search for, so this may be a duplicate question. I have a mysql database with a single table called software. The software table is comprised of the following fields:

id (auto_increment)
computer_name
software_name
software_version
install_date

Every time a piece of software is installed, upgraded or reverted to a previous version a new entry is made into the table.

I'm trying to build a table with php that would look like so:

.---------------------------------------------------.
|            | software x | software y | software z |
|---------------------------------------------------|
| computer a | ver 1.0    |            | ver 1.2    |
|---------------------------------------------------|
| computer b |            | ver 2.1    |            |
|---------------------------------------------------|
| computer c | ver 1.3    | ver 1.1    |            |
'---------------------------------------------------'

From data like so:

computer_name  |  software_name  |  software_version  |  install_date
----------------------------------------------------------------------
computer a     |  software x     |  ver 1.1           |  [10 days ago]
computer a     |  software x     |  ver 1.0           |  [2 days ago]
computer a     |  software z     |  ver 1.1           |  [20 days ago]
computer a     |  software z     |  ver 1.2           |  [5 days ago]

The install_date would be an actual date, threw in [X days ago] for convenience.

The table would show the latest version of each software installed on each computer, with only one row for each unique computer entry and one column for each unique software entry.

Is this possible to do with a single MySQL table? What would be the best way to accomplish this? I'm using PHP and MySQL with codeigniter.

Update:

From looking at the possible duplicate question I was able to prepare this query:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(software_name = ''',
      software_name,
      ''', software_version, NULL)) AS ',
      replace(software_name, ' ', '_')
    )
  ) INTO @sql 
FROM 
  software;

SET @sql = CONCAT('SELECT computer_name, ', @sql, ' FROM software GROUP BY computer_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Which generates results in the format I'm looking for, however it is not getting the version by the latest install_date but rather by the biggest version number found. How can I modify the above to get the version with the newest associated install_date?

Is there any sort of primary key field (an autoincrement perhaps) in this table? — Mike Brant, Mar 27 '13 at 19:49
Yeah, there is an id field which auto-increments. Updating the question... — codybuell, Mar 27 '13 at 19:52
if install date was a unix timestamp you wouldn't need an autoincrementing primary key (even though every bone in my body tells me you should have one) and you could order by install_date descending. However, set a primary key and a simple select distinct should work... — , Mar 27 '13 at 20:02
possible duplicate of [Is it possible to use Crosstab/Pivot Query in MySQL?](http://stackoverflow.com/questions/8920626/is-it-possible-to-use-crosstab-pivot-query-in-mysql) — symcbean, Mar 27 '13 at 22:06

score 1 · Answer 1 · answered Mar 27 '13 at 22:59

A hard work but I got a query that will achieve the result!

What you need on the database side is something like a CROSS JOIN to be able to list all distinct computers against all distinct softwares and then filter the desired software_version.

I did it with this query:

SELECT 
  b.computer_name, 
  a.software_name, 
  MAX(IF(b.software_name = a.software_name AND b.id = a.id, a.software_version, '')) AS software_version 
FROM 
  software AS a, 
  software AS b 
GROUP BY 
  b.computer_name, 
  a.software_name 
ORDER BY 
  b.computer_name ASC, 
  a.software_name ASC, 
  software_version DESC;

My table data had these values:

 ------ --------------- --------------- ------------------ --------------------- 
| id   | computer_name | software_name | software_version | install_date        |
 --------------- ------ --------------- ------------------ --------------------- 
|    1 | computer a    | software x    | ver 1.1          | 2013-03-27 15:58:17 |
|    2 | computer a    | software x    | ver 1.0          | 2013-03-27 15:58:20 |
|    3 | computer a    | software z    | ver 1.3          | 2013-03-27 15:58:24 |
|    4 | computer b    | software z    | ver 1.4          | 2013-03-27 15:58:30 |
|    5 | computer b    | software z    | ver 1.2          | 2013-03-27 15:58:45 |
|    6 | computer c    | software x    | ver 1.5          | 2013-03-27 15:58:51 |
|    7 | computer c    | software y    | ver 1.7          | 2013-03-27 15:58:58 |
 --------------- ------ --------------- ------------------ ---------------------

And this was my output:

 ------------ ------------ ------------------ 
| computer   | software   | software_version |
 ------------ ------------ ------------------ 
| computer a | software x | ver 1.1          |
| computer a | software y |                  |
| computer a | software z | ver 1.3          |
| computer b | software x |                  |
| computer b | software y |                  |
| computer b | software z | ver 1.4          |
| computer c | software x | ver 1.5          |
| computer c | software y | ver 1.7          |
| computer c | software z |                  |
 ------------ ------------ ------------------

In your PHP code you should do something like this (as I know of pure PHP - I did not tested the PHP code below and it's not the best way of code it... but I think it will work and it's based on my query output):

<?php
  $sql = 'SELECT ...';  // put the query here
  $result = mysql_query($sql);

  if( $result and mysql_num_rows($result))
  {
    $num_rows = mysql_num_rows($result);

    $versions = array();
    while ($row = mysql_fetch_assoc($result))
      $versions[] = $row;

    // Here we'll count how much softwares we have 
    $software_count = -1;
    $first_computer = $versions[0]['computer_name'];

    while( $versions[++$software_count]['computer_name'] == $first_computer );

    echo "<table>". 
           "<thead>". 
             "<tr>". 
               "<th></th>";

    // Here we'll print the softwares names as our table headers
    for($i = 0; $i < $software_count; $i++)
      echo "<th>". $versions[$i]['software_name'] . "</th>";

    echo     "</tr>". 
           "</thead>". 
           "<tbody>";

    // Here we'll print the data
    while($num_rows)
    {
      echo   "<tr>". 
               "<td style='font-weight: bold;'>". 
                 $versions[$i]["computer_name"] .
               "</td>";

      for($i = 0; $i < $software_count; $i++)
        echo   "<td>". $versions[$i]["software_version"] . "</td>";

      echo   "</tr>";

      $num_rows -= $software_count;
    }

    echo   "</tbody>".  
         "</table>";

  mysql_free_result($result);

?>

A bit of work but I hope it helps you and I hope my PHP code is working fine! Good luck!

Looks good, but `computer b` should have `ver 1.2` as the installed version for `software z` as it was installed after `ver 1.4`. Any way to correct that? — codybuell, Mar 28 '13 at 20:33
@codybuell I thought you wanted the highest software_version, instead of the last installed version... I'll try to modify... — Felypp Oliveira, Mar 28 '13 at 23:02

AbsoluteƵERØ · Answer 2 · 2013-03-27T20:14:10.957

Something like this should work (a lot faster than using distinct).

select computer_name,software_name,software_version,install_date from table group by software_name order by computer_name,install_date DESC

This way you can still show all the software on each computer.

If you wanted to limit the entry to the most recent software installed per computer you could use:

select computer_name,software_name,software_version,install_date from table group by computer_name,software_name order by computer_name,install_date DESC

HTML table from MySQL with most recent and unique values

Update:

2 Answers2