Determining difference in timestamps for two values in the same MySQL table

Posted by JayRizzo03 on Stack Overflow See other posts from Stack Overflow or by JayRizzo03
Published on 2012-11-20T04:55:35Z Indexed on 2012/11/20 4:59 UTC
Read the original article Hit count: 120

Filed under:
|

I am relatively new to programming in PHP, so I apologize if this is a rather simple question.

I have a MySQL database table called MachineReports that contains the following values: ReportNum(primary key, auto increment), MachineID and Timestamp

Here is some example data:

|ReportNum | MachineID |     Timestamp      |
|1         | AD3203     | 2012-11-18 06:32:28|
|2         | AD3203     | 2012-11-19 04:00:15|
|3         | BC4300     | 2012-11-19 04:00:15|

What I am attempting to do is find the difference in timestamps in seconds for each machine ID by iterating over each row set. I am getting stuck on the best way to do this, however. Here is the code I've written so far:

<?php
include '../dbconnect/dbconnect.php';
      $machineID=[];

//Get a list of all MachineIDs in the database      
foreach($dbh->query('SELECT DISTINCT(MachineID) FROM MachineReports') as $row) {
        array_push($machineID, $row[0]);
   }

  for($i=0;$i<count($machineID);$i++){
    foreach($dbh->query("SELECT MachineID FROM MachineReports WHERE MachineID='$machineID[$i]' ORDER BY MachineID") as $row) {

    //code to associate each machineID with two time stamps goes here
 }
  }

?> This code just lists out the contents of the table row by row. My ultimate goal is to find the difference in timestamps for a certain MachineID. One of the things I've considered is using a multidimensional array in php - using the $machineID as the key and then storing the timestamp inside the array the key points to.

However, I'm uncertain how to do that since my query parses row by row.

I have quite a few questions.

1) Is this the most efficient way to be doing this? I suspect my database table design may not be the best. 2)What would be the best way to determine the difference in timestamps for a certain machineID?

Even just a pointer to a topic that would prompt me to think about this in a different way would be helpful - I'm not afraid to do research. Thanks!

© Stack Overflow or respective owner

Related posts about php

Related posts about mysql