select date,
(case `time` when '08:30:00' then `time` else null end) as time1,
(case `time` when '12:30:00' then `time` else null end) as time2,
(case `time` when '14:00:00' then `time` else null end) as time3,
(case `time` when '18:00:00' then `time` else null end) as time4,
(case `time` when '19:00:00' then `time` else null end) as time5,
(case `time` when '21:00:00' then `time` else null end) as time6
from t1 group by date
(case `time` when '08:30:00' then `time` else null end) as time1,
(case `time` when '12:30:00' then `time` else null end) as time2,
(case `time` when '14:00:00' then `time` else null end) as time3,
(case `time` when '18:00:00' then `time` else null end) as time4,
(case `time` when '19:00:00' then `time` else null end) as time5,
(case `time` when '21:00:00' then `time` else null end) as time6
from t1 group by date
select distinct datefield from date
取出日期,,然后根据日期再取出时间
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)({
$sql1="select * from table where date=".$row['date'] order by time;
$result1=mysql_query($sql1); $i=0
while($row1=mysql_fetch_array($result1)){
$i++;
if($i=1){ $sql2="insert into table2 (date,time1,time2,time3,time4,time5) values ('".$row['date']."','"$row1['time']."','0','0','0','0')"; //日期型是否加隐号不知道 }else{
$sql2="update table2 set time".$i." =".$row1['time']." where date='".$row['date']."'";
}
mysql_query($sql2); }}
$sql = 'select date,';
$i = 1;
while($value = fetch_array($query)){
$sql .= "((case `time` when '".$value['time']."' then `time` else null end) as time".$i.",)";
$i++;
}$sql .=' from t1 group by date';
delete dt1 from dt1 inner join dt2 on dt1.dd=dt2.dd where dt1.tt=dt2.t1 ;insert into dt2(dd,t2) select dd, max(tt) from dt1 group by dd ;
delete dt1 from dt1 inner join dt2 on dt1.dd=dt2.dd where dt1.tt=dt2.t2 ;insert into dt2(dd,t3) select dd, max(tt) from dt1 group by dd ;
delete dt1 from dt1 inner join dt2 on dt1.dd=dt2.dd where dt1.tt=dt2.t3 ;...
$i=0;
$rs=mysql_query($sql);
$count=mysql_num_rows($rs);
while($row=mysql_fetch_array($rs)){
$sid=$row['id']; //把_staffinfo里的id赋给变量
$logtime=explode(" ",$row['logtime']); //因为时间是2008-11-28 08:30:00格式的,所以要截取: $logtime[0]截取的是2008-11-28就是年月日 ,$logtime[1]截取的是08:30:00就是时分秒
$search="select * from attandance_09_2008 where sid=".$sid." and DATE='".$logtime[0]."' group by '".$logtime[0]."'";//这句就是要插入六个时间的表,先查出符合条件的然后插入 $num=mysql_num_rows(mysql_query($search));
$i++;
if($num==0){//如果查出记录为0的就插入,否则就更新
echo "可以插入";
$insert="insert into attandance_09_2008(sid,DATE,fingertime1,fingertime2,fingertime3,fingertime4,fingertime5,fingertime6) values(".$sid.",'".$logtime[0]."','".$logtime[1]."','0','0','0','0','0')";
mysql_query($insert);
} else{
echo "只能更新";
$update="update attandance_09_2008 set fingertime".$i."='".$logtime[1]."' where sid=".$sid." and DATE='".$logtime[0]."'";
mysql_query($update);
}
} 现在执行的结果是只有第一个时间插入了,后五个时间里只有前几条有插入,有的没有插入,反正后五个时间除了前几条都没插进去,哪里有误,是不是那个i值有问题,还望指出呀!
这一句有错
$search="select * from attandance_09_2008 where sid=".$sid." and DATE='".$logtime[0]."' ";
后面的group by 不要了。
$sql='SELECT * FROM table1 GROUP BY date, timeORDER BY time, date';
$rs=mysql_query($sql);
$array = array();
while($row=mysql_fetch_array($rs){
$array[$row['date']][] = $row['time']
}
//$array 得到的结果就是 array([2008/11/28]=>array(6个时间段),[2008/11/29]=>array(6个时间段),);
然后
foreach($array as $key=>$value){
$sql='';
$sql='insert into table2(date,time1,time2,time3,time4,time5,time6)
value(\''.$value[0].'\',''.$value[1].'\',''.$value[2].'\',''.$value[3].'\',''.$value[4].'\',''.$value[5].'\'
,''.$value[6].'\')';
mysql_query($sql);
}
foreach($array as $key=>$value){
$sql='';
$sql='insert into table2(date,time1,time2,time3,time4,time5,time6)
value(\''.$key.'\',''.$value[1].'\',''.$value[2].'\',''.$value[3].'\',''.$value[4].'\',''.$value[5].'\'
,''.$value[6].'\')';
mysql_query($sql);
}
$rs=mysql_query($sql);
$count=mysql_num_rows($rs); while($row=mysql_fetch_array($rs)){ $sid=$row['id']; //把_staffinfo里的id赋给变量
$logtime=explode(" ",$row['logtime']); //因为时间是2008-11-28 08:30:00格式的,所以要截取: $logtime[0]截取的是2008-11-28就是年月日 ,$logtime[1]截取的是08:30:00就是时分秒 $search="select * from attandance_09_2008 where sid=".$sid." and DATE='".$logtime[0]."'"; //这句就是要插入六个时间的表,先查出符合条件的然后插入 $num=mysql_num_rows(mysql_query($search)); $array[$logtime[0]][]=$logtime[1];if($num==0){//如果查出记录为0的就插入,否则就更新 foreach($array as $key=>$value){
$sql2="";
$sql2="insert into attandance_09_2008(sid,date,fingertime1,fingertime2,fingertime3,fingertime4,fingertime5,fingertime6)
value('$sid','$key','$value[1]','$value[2]','$value[3]','$value[4]','$value[5]','$value[6]')"; //這裏的sql用單引號不行啊,會有錯
mysql_query($sql2);
} }}
现在会重复的插入记录,本来只有58条的记录,现在是插入1650条了,而且后面五个时间有的还是没有插进去,好郁闷呀!
所以 $sql2="insert into attandance_09_2008(sid,date,fingertime1,fingertime2,fingertime3,fingertime4,fingertime5,fingertime6)
value('$sid','$key','$value[1]','$value[2]','$value[3]','$value[4]','$value[5]','$value[6]')";
这要改成:
$sql2="insert into attandance_09_2008(sid,date,fingertime1,fingertime2,fingertime3,fingertime4,fingertime5,fingertime6)
value('$sid','$key','$value[0]','$value[1]','$value[2]','$value[3]','$value[4]','$value[5]')";
首先把结果给查询出来的
你不能 查询出一个结果 然后 foreach() 当然会出现很多了while($row=mysql_fetch_array($rs)){ $sid=$row['id']; //把_staffinfo里的id赋给变量
$logtime=explode(" ",$row['logtime']); //因为时间是2008-11-28 08:30:00格式的,所以要截取: $logtime[0]截取的是2008-11-28就是年月日 ,$logtime[1]截取的是08:30:00就是时分秒 $search="select * from attandance_09_2008 where sid=".$sid." and DATE='".$logtime[0]."'"; //这句就是要插入六个时间的表,先查出符合条件的然后插入 $num=mysql_num_rows(mysql_query($search)); $array[$logtime[0]][]=$logtime[1]; if($num==0){//如果查出记录为0的就插入,否则就更新 foreach($array as $key=>$value){
$sql2="";
$sql2="insert into attandance_09_2008(sid,date,fingertime1,fingertime2,fingertime3,fingertime4,fingertime5,fingertime6)
value('$sid','$key','$value[1]','$value[2]','$value[3]','$value[4]','$value[5]','$value[6]')"; //這裏的sql用單引號不行啊,會有錯
mysql_query($sql2);
} }这边的foreach 要想办法放到 while外面去呢 。
要不然会一直循环的。
最好 先把 保存的数据
print_r();
输出 看看格式是什么样的
mysql
========
$sql = "select `date`,group_concat(`time`) as ctime from table1 where 1 group by `date`";php
=================================================
explode(',',时间串),array_unique一下,构造成一个类似以下的数组$rs[0]['date'] = '2008/11/28 '
$rs[0]['time1'] = '时间1';
$rs[0]['time2'] = '时间2';
$rs[0]['time1'] = '时间1';
$rs[0]['time2'] = '时间2';
...
...
..
$rs[1]['date'] = '2008/11/29 '
$rs[1]['time1'] = '时间1';
$rs[1]['time2'] = '时间2';
...
...
...
例如:
$rs=mysql_query($sql);
$array = array();
while($row=mysql_fetch_array($rs){
$array[$row['date']][$row['sid']] = $row['time'] ;
}
这样得到的结果就是:1,2 8分别是每段时间的 sid
array([2008/11/28]=>array
(
'1' => 'time1',
'2' => 'time2',
..
'8 => ''time8,
),[2008/11/29]=>array('1' => 'time1',
'9 => 'time2',
..
'20=> ''time8,);
这样用foreach 就可以了
$sid=$row['id'];echo $sid; //這裏是sid
$logtime=explode(" ",$row['logtime']);
$array[$logtime[0]][$sid]=$logtime[1]; //sid放到array里
} $search="select * from attandance_09_2008 where sid=".$sid." and DATE='".$logtime[0]."'";
$num=mysql_num_rows(mysql_query($search));
if($num==""){
foreach($array as $key=>$value){ $sql2="insert into attandance_09_2008(sid,date,fingertime1,fingertime2,fingertime3,fingertime4,fingertime5,fingertime6)
value('这里','$key','$value[0]','$value[1]','$value[2]','$value[3]','$value[4]','$value[5]')"; mysql_query($sql2);
} //insert语句values里的sid怎样放呢!如果放$sid也不对呀,不太了解php,还望再指出呀!
}
print_r($array)在foreach前面把数组输出,你就可以看清楚他的结构
是 table1里面的6个值组合成 一个table2里面的一个数据
所以 sid 就不能这样做的。。
可能你一开始构思有的错误了。
while($row=mysql_fetch_array($rs)){ $logtime=explode(" ",$row['logtime']);
$array[$logtime[0]][]=$logtime[1];
$search="select * from attandance_09_2008 where sid=".$row['id']." and DATE='".$logtime[0]."'"; $num=mysql_num_rows(mysql_query($search));
if($num==0){ $insert="insert into attandance_09_2008(sid,DATE,fingertime1,fingertime2,fingertime3,fingertime4,fingertime5,fingertime6) values(".$row['id'].",'".$logtime[0]."','".$logtime[1]."','0','0','0','0','0')";
mysql_query($insert);
}else
{
foreach($array as $key=>$value){
if($value[0]>="00:00"&&$value[0]<"08:00")
{
//因为这里如果跨天(就是凌晨00:00:00以后)会刷到下一天里,会在下一天里第一个显示这个跨天的记录,這裏是判断.现在就是如果第一条记录是跨天的话就让它补到前一天的最后一个时间里,再让本天里的每个时间往前移一下,下面个更新语句只能把,第一个记录是跨天的话,让本天的每个时间往前更新,可是怎么把第一个跨天的记录$value[0]增加到上一天的最后一个时间里呢!
$update="update attandance_09_2008 set fingertime1='$value[1]',fingertime2='$value[2]',fingertime3='$value[3]',fingertime4='$value[4]',fingertime5='$value[5]',fingertime6='$value[6]' where sid=".$row['id']." and DATE='".$logtime[0]."'";//符合条件的,每个时间往前移一下,可是第一个移走的时间怎么补到上一条记录的最后一个时间里呢!
}else
{
$update="update attandance_09_2008 set fingertime1='$value[0]',fingertime2='$value[1]',fingertime3='$value[2]',fingertime4='$value[3]',fingertime5='$value[4]',fingertime6='$value[5]' where sid=".$row['id']." and DATE='".$logtime[0]."'";
}
}
mysql_query($update);
}
}
如有不解其意,可看10楼14楼代码注释
if($value[0]>="00:00"&&$value[0] <"08:00")
{
//首先查出上一天的最大时间
$maxtime = 'select fingertime6 from attandance_09_2008 where time='.($logtime[0]-1);
//$maxtime['fingertime6']就是最大时间 然后在更新下面的 应该就可以了.
$update="update attandance_09_2008 set fingertime1='$value[1]',fingertime2='$value[2]',fingertime3='$value[3]',fingertime4='$value[4]',fingertime5='$value[5]',fingertime6='$value[6]' where sid=".$row['id']." and DATE='".$logtime[0]."'";//
}
看了上面的代码 太复杂了 while里面 嵌套 着查询 插入 更新 还在来个 foreach里面的更新.
建议重新思考个做法. 更优的达到效果