} else { $sql = "select analysisdate,{$cols} from msganalysis {$where} order by analysisdate asc"; } $firstRow = array(); $tarr = array(); $carr = array(); $db = new MyDB(); $res = $db->query($sql); //添加第一行 $firstRow = array('analysisdate'); foreach ($colArr as $value) { $firstRow[] = $value; } $tarr[] = $firstRow; //对结果集进行处理,将结果封装在数组中 while ($result = $db->fetch_assoc($res)) { //将pv和uv转换成百分比 if (strstr($cols, "detailpv") && $result["detailpv"] != 0) { $result["detailpv"] = round($result["msgcount"] / $result["detailpv"] * 100, 4); } if (strstr($cols, "detailuv") && $result["detailuv"] != 0) { $result["detailuv"] = round($result["usercount"] / $result["detailuv"] * 100, 4); } $trow = array(); $crow = array(); array_push($trow, $result["analysisdate"]); if ($flag == TRUE) { $crow["analysisdate"] = $result["analysisdate"]; foreach ($colArr as $value) { array_push($trow, $result[$value]); $crow[$value] = $result[$value];
function insert_db($category, $majorCategory, $date) { $dateArr = explode("-", $date); if (checkdate($dateArr[1], $dateArr[2], $dateArr[0]) == false) { return false; } $thisdate = mktime(0, 0, 0, $dateArr[1], $dateArr[2], $dateArr[0]); $nextdate = $thisdate + 3600 * 24; $sql = "select talkid,count(*) as msgcount from allmsg where "; if ($category == 0 || $category == 1001 || $category == 1002) { $sql .= " channel = {$category}"; } else { $sql .= " channel = 0 and postcategoryid={$category} "; } if ($majorCategory !== NULL && $majorCategory != 0) { $sql .= " and postmajorcategoryid={$majorCategory} "; } $sql .= " and updatetime >= {$thisdate} and updatetime < {$nextdate} group by talkid order by msgcount desc"; //echo $sql . '<br/>'; $db = new MyDB(); $res = $db->query($sql); //初始化关联数组 $mc["c20"] = $mc["c10"] = $mc["c6"] = $mc["c5"] = $mc["c4"] = $mc["c3"] = $mc["c2"] = $mc["c1"] = 0; //分段统计消息数对应的会话数 while ($result = $db->fetch_assoc($res)) { $msgcount = $result["msgcount"]; if ($msgcount > 20) { $mc["c20"]++; } else { if ($msgcount > 10 && $msgcount <= 20) { $mc["c10"]++; } else { if ($msgcount >= 6 && $msgcount <= 10) { $mc["c6"]++; } else { if ($msgcount == 5) { $mc["c5"]++; } else { if ($msgcount == 4) { $mc["c4"]++; } else { if ($msgcount == 3) { $mc["c3"]++; } else { if ($msgcount == 2) { $mc["c2"]++; } else { if ($msgcount == 1) { $mc["c1"]++; } } } } } } } } } //echo json_encode($mc); //insert table 'talkcount' $sql_insert = "insert into talkcount(analysisdate,postcategoryid,postmajorcategoryid,c20,c10,c6,c5,c4,c3,c2,c1) values('"; $sql_insert .= $date . "',"; $sql_insert .= $category . ","; $sql_insert .= $majorCategory . ","; $sql_insert .= $mc["c20"] . ","; $sql_insert .= $mc["c10"] . ","; $sql_insert .= $mc["c6"] . ","; $sql_insert .= $mc["c5"] . ","; $sql_insert .= $mc["c4"] . ","; $sql_insert .= $mc["c3"] . ","; $sql_insert .= $mc["c2"] . ","; $sql_insert .= $mc["c1"] . ")"; //echo $sql_insert . "\n"; $db->query($sql_insert); $db->close(); }