Php自表加入时间戳

Question

I have a single SQL table running in my db where all my device in the field report too.

Some event written in it are time base, other are async, that why some entry have the same time stamp ( ts )

This is the table ( name is: data )

id     node_id  type    origin  value   ts  
54475   11      COV     P1      2969    2017-04-30  21:54:59    
54476   11      COV     P1      2967    2017-04-30  21:56:16    
54477   11      INT     P1      2967    2017-04-30  22:00:00    
54478   11      INT     P4      2297    2017-04-30  22:00:00    
54479   11      COV     P1      2965    2017-04-30  22:08:00    
54480   11      INT     P1      2966    2017-04-30  22:15:01    
54481   11      INT     P4      2301    2017-04-30  22:15:01    
54482   11      INT     P1      2963    2017-04-30  22:30:03    
54483   11      INT     P4      2299    2017-04-30  22:30:03    
54484   11      COV     P1      2961    2017-04-30  22:35:00    
54485   11      COV     P1      2963    2017-04-30  22:43:40    

I'd like to have my sql request giving me an output like this

type    P1     P2     P3    P4    device_ts
COV    2969                       2017-04-30  21:54:59
COV    2967                       2017-04-30  21:56:16
INT    2967                 2297  2017-04-30  22:00:00
COV    2965                       2017-04-30  22:08:00
INT    2966                 2301  2017-04-30  22:15:01
INT    2963                 2299  2017-04-30  22:30:03
COV    2961                       2017-04-30  22:35:00
COV    2963                       2017-04-30  22:43:40

so I'll be able to do something like this in my html code

while($data = $P1->fetch(PDO::FETCH_ASSOC))
{
    if($data['type']=="INT")
    {
      echo "<tr>";
      echo "<td>P1 value is : ".$data['P1']."</td>";
      echo "<td>P2 value is : ".$data['P2']."</td>";
      echo "<td>P3 value is : ".$data['P3']."</td>";
      echo "<td>P4 value is : ".$data['P4']."</td>";
      echo "<td>Time stamp. : ".$data['device_ts']."</td>";
      echo "</tr>";
    }
}

Is that something possible? What would be the sql request format?

Answer 1

You can handle the heavy lifting for this query directly in MySQL using the following pivot query:

SELECT
    type,
    MAX(CASE WHEN origin = 'P1' THEN value END) AS P1,
    MAX(CASE WHEN origin = 'P2' THEN value END) AS P2,
    MAX(CASE WHEN origin = 'P3' THEN value END) AS P3,
    MAX(CASE WHEN origin = 'P4' THEN value END) AS P4,
    device_ts
FROM data
GROUP BY type, device_ts
ORDER BY device_ts, type

Output:

Demo here:

Rextester