php - 如何使用php解决mysql数据库中的数据重复问题

Question

我还有另一个问题，让我头疼的是使用 php 进行 mysql 数据库的正确迭代。这是测试数据:

0007 2015-1-15 17:17
0007 2015-1-19 07:45
0007 2015-1-19 17:58
0007 2015-1-20 08:08
0007 2015-1-20 17:18
0007 2015-1-21 07:42
0007 2015-1-21 17:17
0007 2015-1-22 07:45

这是我的代码，用于按 ID 和日期提取和计算时间:

$cxn=mysqli_connect($host,$user,$pass,$dbname) or die("Couldn't connect to 
server");
$qry="select ID, ValidDate,ValidTime,Hours from temprec group by  
ID,ValidDate";
$result=mysqli_query($cxn,$qry) or die("Couldn't execute query");

while($row=mysqli_fetch_assoc($result)){
extract($row);
$Time=date("H:i",strtotime($ValidTime));
$Day=date("D",strtotime($ValidDate));

if($Time >= $consTimeOut){
    $TimeOut=$Time;

}
elseif($Time > $consLunchOut AND $Time<=$consLunchIn){
    $LunchIn=$Time;

}
elseif($Time >= $consLunchOut AND $Time<$consLunchIn){
    $LunchOut=$Time;

}
elseif($Time>=$consTimeIn AND $Time <$consLunchOut){
    $TimeIn=$Time;

}
}

当我回显数据库的内容时，它没有重复项，但当我输入计算时，数据变得冗余并且不断重复，大部分从 17:17 开始。希望你能帮助我。

Database Structure ID ValidDate ValidTime Hours

对于整个php脚本，其他代码只是表结构。

这是我在编写上面的代码之前制作的伪代码:

While date= newdate
If time!= nexttime then
vtime=time
if vtime>=6:00 AM AND vtime < 11:00 then
timein=vtime
elseif vtime>=11:00 AND vtime <13:00 then
lunchout=vtime
elseif vtime>= 13:00 AND vtime < 17:00 then
lunchin=vtime
else
timeout=vtime

Complete Code

$user="root";
$pass="admin";
$host="localhost";
$dbname="tempdb";

$cxn=mysqli_connect($host,$user,$pass,$dbname) or die("Couldn't connect to   server");
$qry="select ID, ValidDate,ValidTime,Hours from temprec group by ID,ValidDate";
$result=mysqli_query($cxn,$qry) or die("Couldn't execute query");
$consTimeIn=strtotime("06:00");
$consLunchIn=strtotime("13:00");
$consLunchOut=strtotime("11:00");
$consTimeOut=strtotime("17:00");
$Message="";
$OTMsg="";
$msg="";
$validOT="";
?>
<style type="text/css">
.TFtable{
    width:80%;
    font-family: verdana,aerial,sans-serif;
    font-size: 11px;
    color:#333333;
    border-width: 1px;
    border-color: #a9c9c9;
    border-collapse: collapse;
}
.TFtable th{
    border-width: 1px;
    padding: 8px;
    border-style: solid;
    border-color: #a9c9c9;
    background-color: #dedede;
}
.TFtable td{
    border-width: 1px;
    padding: 7px;
    border: #4e95f4 1px solid;
}
.TFtable tr{
    background: #b8d1f3;
}
.TFtable tr:nth-child(odd){
    background: #b8d1f3;
}
.TFtable tr:nth-child(even){
    background: #dae5f4;
}
</style>
<div><table class="TFtable" cellspacing='5' align='center'>
      <tr><th>ID</th><th>Day</th><th>Date</th><th>TimeIn</th>
      <th>LunchOut</th><th>LunchIn</th><th>TimeOut</th>
      <th>Notice</th></tr>
<?php
$TimeIn="";
$TimeOut;
$LunchOut="";
$LunchIn="";
while($row=mysqli_fetch_array($result)){
extract($row);
$Time=date("H:i",strtotime($ValidTime));
$Day=date("D",strtotime($ValidDate));

if(strtotime($Time)>=$consTimeIn AND strtotime($Time)<$consLunchOut){
    $TimeIn=$Time;
}
elseif(strtotime($Time)>=$consLunchOut AND strtotime($Time)<$consLunchIn){
    $LunchOut=$Time;
}
elseif(strtotime($Time)<=$consLunchIn AND strtotime($Time)>$consLunchOut){
    $LunchIn=$Time;
}
else{
    $TimeOut="";
    $TimeOut=$Time;

}


if($TimeIn==""){
$TimeIn="NULL";
$OTMsg="You're missing some time";  
}
elseif($LunchOut==""){
$LunchOut="NULL";
$OTMsg="You're missing some time";  
}
elseif($LunchIn==""){
$LunchIn="NULL";
$OTMsg="You're missing some time";
}
else{
$validHours=$TimeOut-$TimeIn;
switch($validHours){
    case 8:
    $OTMsg="Completed the 8 hours standard time";
    $validOT="None";
    break;

    case ($validHours<8):
    $OTMsg="You are considered undertime";
    $validOT="None";
    break;

    case ($validHours>8):
    $validHours-=1;
    $validOT=$validHours-8;
    $OTMsg="Completed the 8 hours standard time and gained {$validOT} 
 Hours";
    break;
}
}


$newtimeout=date("g:i",strtotime($TimeOut));
echo "<tr><td>$ID</td>\n
      <td>$Day</td>\n
      <td>$ValidDate</td>\n
      <td>$TimeIn</td>\n
      <td>$LunchOut</td>\n
      <td>$LunchIn</td>\n
      <td>$newtimeout PM</td>\n
      <td>$OTMsg</td>\n";   
}   
echo "</table></div>";

Answer 1

如果我没记错的话，每次在循环中为变量设置值之前，您都需要清除变量 $TimeOut、$LunchIn、$LunchOut、$TimeIn。

从哪里获取 $ValidTime？我在你的代码中没有看到它。

更新

    while ($row = mysqli_fetch_array($result))
    {

    extract($row);
    $valTime = strtotime($ValidTime);
    $valDate = strtotime($ValidDate);

    $TimeIn = "";
    $TimeOut = "";
    $LunchOut = "";
    $LunchIn= "";

    $Day = date("D", strtotime($ValidDate));

    if ($valTime) >= $consTimeIn && $valTime < $consLunchOut)
    {
        $TimeIn = $valTime;
    }
    elseif ($valTime >= $consLunchOut && $valTime < $consLunchIn)
    {
        $LunchOut = $valTime;
    }
    elseif ($valTime <= $consLunchIn && $valTime > $consLunchOut)
    {
        $LunchIn = $valTime;
    }
    else
    {
        $TimeOut = "";
        $TimeOut = $valTime;
    }


    if (empty($TimeIn)) 
    {
        $TimeIn = "NULL";
        $OTMsg = "You're missing some time";  
    }
    elseif(empty($LunchOut))
    {
        $LunchOut="NULL";
        $OTMsg="You're missing some time";  
    }
    elseif(empty($LunchIn))
    {
        $LunchIn="NULL";
        $OTMsg="You're missing some time";
    }
    else
    {
        /*$validHours = $TimeOut - $TimeIn;

        switch($validHours)
        {
            case 8:
                $OTMsg = "Completed the 8 hours standard time";
                $validOT = "None";
            break;
            case ($validHours < 8):
                $OTMsg = "You are considered undertime";
                $validOT = "None";
            break;
            case ($validHours > 8):
                $validHours -= 1;
                $validOT = $validHours - 8;
                $OTMsg = "Completed the 8 hours standard time and gained {$validOT} Hours";
            break;
        }*/
    }

    $newTimeout = date("g:i", strtotime($TimeOut));
echo "<tr><td>$ID</td>\n
      <td>$Day</td>\n
      <td>$ValidDate</td>\n
      <td>$TimeIn</td>\n
      <td>$LunchOut</td>\n
      <td>$LunchIn</td>\n
      <td>$newtimeout PM</td>\n
      <td>$OTMsg</td>\n";   
}