如何用PHP偏移HTML表以从不同列的第二行单元格值中减去第一行的单元格值 [英] How To offset an HTML table with PHP to Subtract cell value of 1st row from 2nd row cell value of different columns
问题描述
好的，所以我试图从上周开始解决这个问题，但是还没有找到解决方案，但是我问了很多遍问题，但是无法使代码正常工作，所以我将所有细节都放在了这个问题中问题.
OK, so I'm trying to solve this problem from last week and haven't found a solution yet I ask the Question so many times but not able to get the Code working so I'm putting all details in this one Question.
我正在使用PHP和Mysql查询生成HTML表(表如下所示:表)
I'm Generating an HTML table with PHP and Mysql query (Table look like this Table)
DB ID EmpID Date Username Computername State Minutesatstate Statestarttime Stateendtime Timestamp
704634 303836 02-06-2019 user PC-818 Idle 2 13:44 13:46 2019-02-06 13:46:46
704599 303836 02-06-2019 user PC-818 Active 16 13:28 13:44 2019-02-06 13:44:46
704340 303836 02-06-2019 user NIPL-1220 Active 2 13:27 13:28 2019-02-06 13:28:48
704313 303836 02-06-2019 user PC-818 Active 13 13:15 13:27 2019-02-06 13:27:31
我想用PHP或Jquery或Javascript添加另一列，以将第一行的Statestarttime列与第二行的Stateendtime列相减，以找出时差.
I want to add another column with PHP or Jquery or Javascript to subtract Statestarttime column of 1st row with Stateendtime column of 2nd row to find out the time difference.
我只希望页面上显示最小差异列.
I just want min difference column to be displayed on the page.
预期结果示例
StateStarTtime StateEndTime Min Difference
03:57 03:58 00:03
03:53 03:54 00:04
03:46 03:49 null
我能够找到很多示例，并帮助从第一行的StateEndTime中减去StateStarTtime，但我想从StateEndTime的第二行单元值中减去StateStarTtime的第一行单元值来找到分钟差.
I'm able to find so many example and help for subtracting StateStarTtime from StateEndTime in the first row but i want to subtract 1st row cell value of StateStarTtime from 2nd row cell value of StateEndTime to find the Minute Difference.
我是HTML和PHP的初学者，但是对JS/JQ来说是全新的，因此，我对如何做到这一点的任何方向都表示赞赏.以下是我正在使用的PHP和HTML代码段.让我知道您是否需要完整的代码.
I'm beginner in HTML and PHP but completely new to JS/JQ so any direction on how i can do this is much appreciated. Below are the PHP and HTML codes snippet i'm using. Let me know if you want complete code.
从Mysql获取数据
<?php
if(isset($_POST['search']))
{
$valueToSearch = $_POST['valueToSearch'];
$query = "SELECT * FROM time WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= Timestamp AND EmpID='".$valueToSearch."' ORDER BY `time`.`Timestamp` DESC";
$search_result = filterTable($query);
}
else {
$query = "SELECT * FROM time WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= Timestamp ORDER BY `time`.`Timestamp` DESC";
$search_result = filterTable($query);
}
function filterTable($query)
{
$connect = mysqli_connect("localhost", "root", "", "timetracker");
$filter_Result = mysqli_query($connect, $query);
return $filter_Result;
}
?>
表PHP代码
<?php while($row = mysqli_fetch_array($search_result)):?>
<tr>
<td align="Center"><?php echo $row['id'];?></td>
<td align="Center"><?php echo $row['EmpID'];?></td>
<td align="Center"><?php echo $row['Date'];?></td>
<td align="Center"><?php echo $row['Username'];?></td>
<td align="Center"><?php echo $row['Computername'];?></td>
<td align="Center"><?php echo $row['State'];?></td>
<td align="Center"><?php echo $row['MinutesatState'];?></td>
<td align="Center"><?php echo $row['StateStarttime'];?></td>
<td align="Center"><?php echo $row['StateEndtime'];?></td>
<td align="Center"><?php echo $row['Timestamp'];?></td>
</tr>
<?php endwhile;?>
尝试的解决方案-1
<?php while($row = mysqli_fetch_array($search_result)):?>
<?php $startTime[] = $row['StateStarttime'];?>
<?php $endTime[] = $row['StateEndtime'];?>
<?php endwhile;?>
<?php
$i = 0;
while($row = mysqli_fetch_array($search_result)):?>
<tr>
<td align="Center"><?php echo $row['id'];?></td>
<td align="Center"><?php echo $row['EmpID'];?></td>
<td align="Center"><?php echo $row['Date'];?></td>
<td align="Center"><?php echo $row['Username'];?></td>
<td align="Center"><?php echo $row['Computername'];?></td>
<td align="Center"><?php echo $row['State'];?></td>
<td align="Center"><?php echo $row['MinutesatState'];?></td>
<td align="Center"><?php echo $row['StateStarttime'];?></td>
<td align="Center"><?php echo $row['StateEndtime'];?></td>
<td align="Center"><?php echo $row['Timestamp'];?></td>
//your calculation goes here like diff
<td align="Center">
<?php
$mycalc = ($startTime[$i] - $endTime[$i+1]);
echo $mycalc;
?>
</td>
</tr>
$i++;
<?php endwhile;?>
尝试的解决方案-2
<?php for($i = 0; $i < count($results); $i++): ?>
<?php
$datetime1 = DateTime::createFromFormat('i:s', $results[$i]['StateStarttime']);
$datetime2 = DateTime::createFromFormat('i:s', $results[$i+1]['StateEndtime']);
if($datetime2)
$interval = $datetime1->diff($datetime2)->format('%I:%S');
else
$interval = 'unknown';
?>
<tr>
<td align="Center"><?php echo $results[$i]['id'];?></td>
<td align="Center"><?php echo $results[$i]['EmpID'];?></td>
<td align="Center"><?php echo $results[$i]['Date'];?></td>
<td align="Center"><?php echo $results[$i]['Username'];?></td>
<td align="Center"><?php echo $results[$i]['Computername'];?></td>
<td align="Center"><?php echo $results[$i]['State'];?></td>
<td align="Center"><?php echo $results[$i]['MinutesatState'];?></td>
<td align="Center"><?php echo $results[$i]['StateStarttime']; ?></td>
<td align="Center"><?php echo $results[$i]['StateEndtime']; ?></td>
<td align="Center"><?php echo $results[$i]['Timestamp'];?></td>
<td align="Center"><?php echo $interval; ?></td>
</tr>
<?php endfor; ?>
在有人建议我之前
<td align="Center"><?php echo($row['StateEndtime'] - $row['StateStarTtime ']) ?></td>
这将给我同一行中的StateStarTtime和StateEndtime的分钟差，我想从StateEndTime的第二行单元格值中减去StateStarTtime的第一行单元格值，依此类推以找到分钟差异.
This will give me the Minute Difference of StateStarTtime and StateEndtime in the same row i want to subtract 1st row cell value of StateStarTtime from 2nd row cell value of StateEndTime and so on to find the Minute Difference.
推荐答案
尝试一些类似的方法.只有一个准则，真正的实现取决于您.另外，我建议您使用{}而不是endwhile.
Try something along the lines of this. Only a guideline, the real implementation is up to you. Also I'd recommend you use {} rather than endwhile.
<?php
$last_row = $mysqli_fetch_array($search_result));
while($row = mysqli_fetch_array($search_result)){
//this is pseudocode, you will need to convert to date objects
$timediff = $last_row['StateStarttime'] - $row['StateEndtime'];
?>
<tr>
<td align="Center"><?php echo $row['id'];?></td>
<td align="Center"><?php echo $row['EmpID'];?></td>
<td align="Center"><?php echo $row['Date'];?></td>
<td align="Center"><?php echo $row['Username'];?></td>
<td align="Center"><?php echo $row['Computername'];?></td>
<td align="Center"><?php echo $row['State'];?></td>
<td align="Center"><?php echo $row['MinutesatState'];?></td>
<td align="Center"><?php echo $row['StateStarttime'];?></td>
<td align="Center"><?php echo $row['StateEndtime'];?></td>
<td align="Center"><?php echo $row['Timestamp'];?></td>
// echo $timediff
</tr>
<?php
$last_row = $row;
}?>
这篇关于如何用PHP偏移HTML表以从不同列的第二行单元格值中减去第一行的单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
