Запретить повторяющееся имя или повторный ввод имени при отображении

РЕДАКТИРОВАТЬ: можно ли запросить это? У меня 2 таблицы.

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_payment (таблица)

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 = Месяц (январь-декабрь) Я использовал оператор 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  |

1

Решение

Нечто подобное должно работать, вы должны что-то делать со столбцами, которые вы не используете в выражении группы. В вашем случае имеет смысл взять СУММУ, потому что кто-то может заплатить что-то дважды за один месяц, а также за другой.

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
0

Другие решения

Других решений пока нет …

По вопросам рекламы [email protected]