获取选定列的和

Question

在这张表中，我想搜索一个时间段，位置广告在另一个页面中获得搜索结果。

​

​

​

这是我搜索后得到的一页。(search.php)

<table class="table" id="keywords" cellspacing="0" cellpadding="0">
<thead>
    <tr> 
    <th><span>ID</span></th> 
    <th><span>Name</span></th>
    <th><span>Location</span></th>
    <th><span>Date</span></th>
    <th><span>Catagory</span></th>
    <th><span>Labour-Supplier</span></th> 
    <th><span>In-time</span></th>
    <th><span>Out-time</span></th>
    <th><span>Day</span></th>
    <th><span>Day Rate</span></th>
    <th><span>Salary</span></th>
    <th><span>OT-hours</span></th>
    <th><span>OT-rate</span></th>
    <th><span>OT-amount</span></th>
    <th><span>Allowance II</span></th>
    <th><span>TotalSalary</span></th>
    <th><span>Advance</span></th>
    <th><span>Salary-to-hand</span></th> 
</tr>
</thead>

<?php
    if(isset($_POST['submit'])){
    if(isset($_GET['go'])){
    $Location=$_POST['Location'];


    $query = mysql_query("SELECT ID,Name,Location,Date,Category,LabourSupplier,Day,DayRate,Salary,OTHours,OTrate,OTAmount,Allowance2,TotalSalary,Advance,SalaryToHand FROM attendance WHERE Date BETWEEN '".$_POST["FDate"]."' AND '".$_POST["TDate"]."' AND Location LIKE '%" . $Location . "%' ORDER BY location DESC, Date DESC",$connection)
        or die("Failed to query database" . mysql_error());

        while ($row = mysql_fetch_array($query)) {

                print "<tr>"; 
                print "<td >"  . $row['ID'] . "</td>"; 
                print "<td >" . $row['Name'] . "</td>"; 
                print "<td >" . $row['Location'] . "</td>"; 
                print "<th >" . $row['Date'] . "</th>";
                print "<td >" . $row['Category'] . "</td>";
                print "<td >" . $row['LabourSupplier'] . "</td>";
                print "<th >" . $row['InTime'] . "</th>";
                print "<th >" . $row['OutTime'] . "</th>"; 
                print "<th >" . $row['Day'] . "</th>"; 
                print "<th >" . $row['DayRate'] . "</th>"; 
                print "<th >" . $row['Salary'] . "</th>"; 
                print "<th >" . $row['OTHours'] . "</th>"; 
                print "<th >" . $row['OTrate'] . "</th>"; 
                print "<th >" . $row['OTAmount'] . "</th>"; 
                print "<th >" . $row['Allowance2'] . "</th>"; 
                print "<th >" . $row['TotalSalary'] . "</th>"; 
                print "<th >" . $row['Advance'] . "</th>"; 
                print "<th>" .  $row['SalaryToHand'] . "</th>"; 
                print "</tr>"; 
                }
                }

            }
                print "</table>"; 

                ?>

我想得到表格底部的栏日、薪水、OT小时、OT金额、总薪资和工资的总和。是否有可能做到这一点，或者我是否应该在另一个表中得到和。。

我试着把这笔钱放进一个新的桌子里，但这不管用。

<?php

    if(isset($_POST['submit'])){
    if(isset($_GET['go'])){
    $Location=$_POST['Location'];


    $query = mysql_query("SELECT ID,Name,Location,sum(Day),sum(Salary),sum(OTHours),sum(OTAmount),sum(TotalSalary),sum(Advance),sum(SalaryToHand) FROM attendance WHERE Date BETWEEN '".$_POST["FDate"]."' AND '".$_POST["TDate"]."' AND Location LIKE '%" . $Location . "%' ORDER BY location DESC, Date DESC",$connection)
        or die("Failed to query database" . mysql_error());

        while ($row = mysql_fetch_array($query)) {

                print "<tr>"; 
                print "<td >"  . $row['ID'] . "</td>"; 
                print "<td >" . $row['Name'] . "</td>"; 
                print "<td >" . $row['Location'] . "</td>"; 
                print "<th >" . $row['Day'] . "</th>"; 
                print "<th >" . $row['Salary'] . "</th>"; 
                print "<th >" . $row['OTHours'] . "</th>"; 
                print "<th >" . $row['OTAmount'] . "</th>"; 
                print "<th >" . $row['TotalSalary'] . "</th>"; 
                print "<th >" . $row['Advance'] . "</th>"; 
                print "<th>" .  $row['SalaryToHand'] . "</th>"; 
                print "</tr>"; 
                }
                }

            }
                print "</table>"; 

                ?>

Answer 1

尝试以下SQL查询：

// Get parameter
$fDate = $_POST["FDate"];
$tDate = $_POST["TDate"];
// Build SQL query
$sql = <<<SQL
    SELECT '' AS ID,
           '' AS Name,
           '' AS Location,
           SUM(Day) AS Day, 
           SUM(Salary) AS Salary,       
           SUM(OTHours) AS OTHours,
           SUM(OTAmount) AS OTAmount, 
           SUM(TotalSalary) AS TotalSalary,
           SUM(Advance) as Advance,
           SUM(SalaryToHand) as SalaryToHand
    FROM   attendance 
    WHERE  Date BETWEEN '{$fDate}' AND '{$tDate}' 
    AND    Location LIKE '%{$Location}%' 
    ORDER BY location DESC, Date DESC
SQL;
// Excecute it
$query = mysql_query($sql ,$connection) or die("Failed to query database" . mysql_error());
// Handle result
while ($row = mysql_fetch_array($query)) {
  ...
}

请记住，这是不安全的查询(不要将$_POST param直接放入SQL，使用准备语句)。

@草莓是对的，mysqli比mysql更安全。当你有时间的时候，想想迁移吧。