Determining difference in timestamps for two values in the same MySQL table
- by JayRizzo03
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!
