php - 防止显示名称重复或重新输入名称

Question

编辑:是否可以查询这个？我有 2 张 table 。

tithing table
tithings.MarrID = FK to Marriage table
tithings.Tithing_ID PK

tithingspayment table
tithingspayment.TP_ID = PK
tithingspayment.Tithing_ID = FK to tithings table "Tithing_ID"
Paid_Amount 
Paid_Month_Year = Date

Marr_ID | Tithing_ID | TP_ID | Tithing_ID | Paid_Amount | Paid_Month_Year | TP_ID | Tithing_ID | Paid_Amount | Paid_Month_Year | 
  1     |     2      |   5   |     2      |     10.00   |    Jan-2014     |   3   |      2     |     10.00   |     Feb-2014    |

抱歉代码太长；我想问的是我不想在我的表格中显示相同的名字:

<?php

$con=mysqli_connect("localhost","root","","RMS");

if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }
if (isset($_GET["page"])) { $page  = $_GET["page"]; } else { $page=1; }; 
$start_from = ($page-1) * 30; 

$sql = "SELECT * 
FROM tithings
INNER JOIN marriage ON tithings.Marr_ID = marriage.MarrID
INNER JOIN tithings_payment ON tithings_payment.Tithing_ID = tithings.Tithing_ID WHERE YEAR( STR_TO_DATE( Dateregistered,  '%Y' ) ) <= $year LIMIT $start_from, 30"; 
$result = mysqli_query($con,$sql);






echo "<div class='datagrid'><table>
<thead><tr>
<th>Family Name</th>
<th>Jan</th>
<th>Feb</th>
<th>Mar</th>
<th>Apr</th>
<th>May</th>
<th>June</th>
<th>July</th>
<th>Aug</th>
<th>Sep</th>
<th>Oct</th>
<th>Nov</th>
<th>Dec</th>
<th>Operations</th>
</tr></thead>";

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

$id = $row['Tithing_ID'];
echo "<tbody><tr>";
$MonthRegistered = date("F", strtotime($row['Dateregistered']));
$YearRegistered = date("Y", strtotime($row['Dateregistered']));

$PaidMonthYear = $row['Paid_Month_Year'];
$PaidAmount = $row['Paid_Amount'];


$January = date("Y-m", strtotime($row['Paid_Month_Year']));
if($January == "$year-01")
{$January = $row['Paid_Amount'];}
else{$January = 0;}

$February = date("Y-m", strtotime($row['Paid_Month_Year']));
if($February == "$year-02")
{$February = $row['Paid_Amount'];}
else{$February = 0;}

$March = date("Y-m", strtotime($row['Paid_Month_Year']));
if($March == "$year-03")
{$March = $row['Paid_Amount'];}
else{$March = 0;}

$April = date("Y-m", strtotime($row['Paid_Month_Year']));
if($April == "$year-04")
{$April = $row['Paid_Amount'];}
else{$April = 0;}

$May = date("Y-m", strtotime($row['Paid_Month_Year']));
if($May == "$year-05")
{$May = $row['Paid_Amount'];}
else{$May = 0;}

$June = date("Y-m", strtotime($row['Paid_Month_Year']));
if($June == "$year-06")
{$June = $row['Paid_Amount'];}
else{$June = 0;}

$July = date("Y-m", strtotime($row['Paid_Month_Year']));
if($July == "$year-07")
{$July = $row['Paid_Amount'];}
else{$July = 0;}

$August = date("Y-m", strtotime($row['Paid_Month_Year']));
if($August == "$year-08")
{$August = $row['Paid_Amount'];}
else{$August = 0;}

$September = date("Y-m", strtotime($row['Paid_Month_Year']));
if($September == "$year-09")
{$September = $row['Paid_Amount'];}
else{$September = 0;}

$October = date("Y-m", strtotime($row['Paid_Month_Year']));
if($October == "$year-10")
{$October = $row['Paid_Amount'];}
else{$October = 0;}

$November = date("Y-m", strtotime($row['Paid_Month_Year']));
if($November == "$year-11")
{$November = $row['Paid_Amount'];}
else{$November = 0;}

$December = date("Y-m", strtotime($row['Paid_Month_Year']));
if($December == "$year-12")
{$December = $row['Paid_Amount'];}
else{$December = 0;}

if ($YearRegistered <= $year)
{$FamilyHead = $row['FName_Groom']. ", " . $row['LName_Groom'];

$operation = "<td class=operations>" . "<a class='detail' href='../record_detail.php?id=$id'>Details</a>&nbsp;&nbsp;|&nbsp;&nbsp<a class='pay' href='payment.php?id=$id'>Pay</a>&nbsp;&nbsp;|&nbsp;&nbsp<a class='edit' href='../record_edit.php?id=$id'>Edit</a></td>";
}else{
$FamilyHead = "<style>
tbody{
display:none;
}
</style>";
$operation = "";
}


echo "<td>" . $FamilyHead ."</td>";
echo "<td>" . $January . "</td>";
echo "<td>" . $February . "</td>";
echo "<td>" . $March ."</td>";
echo "<td>" . $April . "</td>";
echo "<td>" . $May . "</td>";
echo "<td>" . $June . "</td>";
echo "<td>" . $July ."</td>";
echo "<td>" . $August . "</td>";
echo "<td>" . $September . "</td>";
echo "<td>" . $October ."</td>";
echo "<td>" . $November . "</td>";
echo "<td>" . $December . "</td>";
echo $operation;

  }

十一奉献(表)

Marr_ID(FK) | Tithing_ID (PK) | Dateregistered | Pledge | Barangay  | Address | 
      5     |        1        | Sept, 13 2014  | 15.00  | Barangay1 | Address |
      6     |        2        |  Jan, 13 2014  | 15.00  | Barangay1 | Address |

婚姻(表格)

MarrID (PK) | FName_Groom | LName_Groom | MName_Groom | 
 5          |    Name(5)  |   LName(5)  |   MName(5)  |
 6          |    Name(6)  |   LName(6)  |   MName(6)  |

什一奉献(表)

Tithing_P_ID(PK) | Tithing_ID(FK) | Payment_Date_Received | Paid_Amount | Paid_Month_Year |
        1        |        1       |     Sept, 13 2014     |     15.00   |   Sept,01 2014  |
        2        |        1       |     Sept, 13 2014     |     15.00   |    Oct,01 2014  |
        3        |        2       |     Sept, 13 2014     |     15.00   |    Jan,01 2014  |
        4        |        2       |     Sept, 13 2014     |     15.00   |    Feb,01 2014  |

我的查询语句是

$sql = "SELECT * 
FROM tithings
INNER JOIN marriage ON tithings.Marr_ID = marriage.MarrID
INNER JOIN tithings_payment ON tithings_payment.Tithing_ID = tithings.Tithing_ID WHERE YEAR( STR_TO_DATE( Dateregistered,  '%Y' ) ) <= $year LIMIT $start_from, 30"; 

Paid_Month_Year = Month(Jan-Dec) 我使用 PHP if else 语句

结果是这样的。

Family Name |   Jan   |   Feb   | Mar | Apr | May | June | July | Aug |   Sep   |   Oct   | Nov | Dec |
    Name(5) |    0    |    0    |  0  |  0  |  0  |   0  |   0  |  0  |  15.00  |    0    |  0  |  0  |
    Name(5) |    0    |    0    |  0  |  0  |  0  |   0  |   0  |  0  |    0    |  15.00  |  0  |  0  |
    Name(6) |  15.00  |    0    |  0  |  0  |  0  |   0  |   0  |  0  |    0    |    0    |  0  |  0  |
    Name(6) |    0    |  15.00  |  0  |  0  |  0  |   0  |   0  |  0  |    0    |    0    |  0  |  0  |

注意:我试过 Distinct In 查询无效。

我希望我的输出看起来像这样；

Family Name |   Jan   |   Feb   | Mar | Apr | May | June | July | Aug |   Sep   |   Oct   | Nov | Dec |
    Name(5) |    0    |    0    |  0  |  0  |  0  |   0  |   0  |  0  |  15.00  |  15.00  |  0  |  0  |
    Name(6) |  15.00  |  15.00  |  0  |  0  |  0  |   0  |   0  |  0  |    0    |    0    |  0  |  0  |

Answer 1

这样的事情应该可行，您必须对组语句中未使用的列做一些事情。在您的情况下，采用 SUM 是有意义的，因为有人可以在同一个月支付两次，也可以在另一个月支付。

SELECT
 Family_Name,
 SUM(Jan),
 SUM(Feb)
 ....
 SUM(Dec) 
FROM
 tithings
INNER JOIN marriage ON tithings.Marr_ID = marriage.MarrID
INNER JOIN tithings_payment ON tithings_payment.Tithing_ID = tithings.Tithing_ID
WHER
 YEAR( STR_TO_DATE( Dateregistered,  '%Y' ) ) <= $year
GROUP BY
 Family_Name
LIMIT $start_from, 30