时间戳

首页 > 解决方案 > 如何对两个日期之间的过滤数据求和？

php - 如何对两个日期之间的过滤数据求和？

问题描述

我试图将两个日期之间的选定数据相加，然后按部门分组。有人可以帮我:(我会在这里附上其余的代码。请帮忙。我真的需要完成这个。谢谢大家。

SELECT * FROM salary  
WHERE date BETWEEN '".$_POST["from_date"]."' AND '".$_POST["to_date"]."', Sum (total) as total_salary ] from 'salary' group by department";$result = mysqli_query($connect, $query);  
  $output .= '  
       <table> 
            <tr>  
                <th width="10%">Name</th>  
                <th width="2%">Position</th>  
                <th width="2%">Transaction ID</th>  
                <th width="3%">No of Days</th>  
                <th width="4%">Rate Per Day</th>  
                <th width="7%">Undertime</th>
                <th width="7%">Gross Pay</th>
                <th width="7%">Salary</th> 
            </tr>  
  ';  
  if(mysqli_num_rows($result) > 0)  
  {  
       while($row = mysqli_fetch_array($result))  
       {  
            $output .= '  
                 <tr>  
                      <td>'. $row["name"] .'</td>  
                      <td>'. $row["position"] .'</td>  
                      <td>'. $row["trasactionid"] .'</td>  
                      <td>'. $row["days"] .'</td>  
                      <td>'. $row["rate"] .'</td>  
                      <td>'. $row["undertime"] .'</td> 
                      <td>'. $row["gross"] .'</td> 
                      <td>'. $row["total"] .'</td> 
                 </tr>  
            ';  
       }  
  }  
  else  
  {  
       $output .= '  
            <tr>  
                 <td colspan="5">No Order Found</td>  
            </tr>  
       ';  
  }  
  $output .= '</table>';  
 echo $output;   }  
?>

我期待这样的输出。每个部门的工资总额。

Department       Total_Salary
Maintenance      30,000

标签： phpmysql

解决方案

我假设您能够成功连接到数据库。我还使用准备语句来避免 sql 注入。

$stmt = $mysqli->prepare("SELECT department, SUM(total) as total_salary FROM salary 
                          WHERE date_time BETWEEN ? AND ? group by department");
$stmt->bind_param("ss", $_POST["from_date"],$_POST["to_date"]);
$stmt->execute();
$result = $stmt->get_result();  
$output .= '  
   <table> 
        <tr>  
            <th width="10%">Name</th>  
            <th width="2%">Position</th>  
            <th width="2%">Transaction ID</th>  
            <th width="3%">No of Days</th>  
            <th width="4%">Rate Per Day</th>  
            <th width="7%">Undertime</th>
            <th width="7%">Gross Pay</th>
            <th width="7%">Salary</th> 
        </tr>  
  ';  
  if($result->num_rows > 0)  
 {  
   while($row = $result->fetch_assoc())  
   {  
        $output .= '  
             <tr>  
                  <td>'. $row["name"] .'</td>  
                  <td>'. $row["position"] .'</td>  
                  <td>'. $row["trasactionid"] .'</td>  
                  <td>'. $row["days"] .'</td>  
                  <td>'. $row["rate"] .'</td>  
                  <td>'. $row["undertime"] .'</td> 
                  <td>'. $row["gross"] .'</td> 
                  <td>'. $row["total"] .'</td> 
             </tr>  
        ';  
     }  
   }  
 else  
  {  
     $output .= '  
        <tr>  
             <td colspan="5">No Order Found</td>  
        </tr>  
   ';  
}  
  $output .= '</table>';  
  echo $output;   }  
   ?>

推荐阅读