通过php条件显示给定日期的所有数据

Question

我正在使用php生成从数据库到html表的数据。我想使用php条件输出学生加入日期。

我想运行一个条件，在这个条件下，你取j.date，之前的任何东西都应该有-，之后应该有一个"支付"按钮，它可以保存studentid

+---------+------------+-----+-----+------+------+------+-----+
| Student |   J.date   | Jan | Feb | Mar  | Apr  | May  | ... |
+---------+------------+-----+-----+------+------+------+-----+
| John    | 25-03-2018 |   0 |   0 | 2000 |    0 | 1750 | ... |
| Michael | 10-04-2018 |   0 |   0 |    0 | 5000 |    0 | ... |
+---------+------------+-----+-----+------+------+------+-----+

像这样的

+---------+------------+-----+-----+------+------+------+-----+
| Student |   J.date   | Jan | Feb | Mar  | Apr  | May  | ... |
+---------+------------+-----+-----+------+------+------+-----+
| John    | 25-03-2018 |   - |   - | 2000 | Pay  | 1750 | ... |
| Michael | 10-04-2018 |   - |   - |    - | 5000 | Pay  | ... |
+---------+------------+-----+-----+------+------+------+-----+

已更新

查询

<?php
$sqlFees = "SELECT
s.student_id, s.firstname, s.lastname,
c.subject, c.standard, (t.month * c.fee) AS coursefee,
SUM(f.paid) AS paid, MIN(f.paiddate) AS studentstartdate,
SUM(CASE WHEN MONTH(f.paiddate) = 1 THEN f.paid ELSE 0 END) AS MJan,
SUM(CASE WHEN MONTH(f.paiddate) = 2 THEN f.paid ELSE 0 END) AS MFeb,
SUM(CASE WHEN MONTH(f.paiddate) = 3 THEN f.paid ELSE 0 END) AS MMar,
SUM(CASE WHEN MONTH(f.paiddate) = 4 THEN f.paid ELSE 0 END) AS MApr,
SUM(CASE WHEN MONTH(f.paiddate) = 5 THEN f.paid ELSE 0 END) AS MMay,
SUM(CASE WHEN MONTH(f.paiddate) = 6 THEN f.paid ELSE 0 END) AS MJun,
SUM(CASE WHEN MONTH(f.paiddate) = 7 THEN f.paid ELSE 0 END) AS MJul,
SUM(CASE WHEN MONTH(f.paiddate) = 8 THEN f.paid ELSE 0 END) AS MAug,
SUM(CASE WHEN MONTH(f.paiddate) = 9 THEN f.paid ELSE 0 END) AS MSep,
SUM(CASE WHEN MONTH(f.paiddate) = 10 THEN f.paid ELSE 0 END) AS MOct,
SUM(CASE WHEN MONTH(f.paiddate) = 11 THEN f.paid ELSE 0 END) AS MNov,
SUM(CASE WHEN MONTH(f.paiddate) = 12 THEN f.paid ELSE 0 END) AS MDec
FROM
fees f
JOIN enrollments e ON f.enrollmentid = e.enrollment_id
LEFT JOIN courses c ON e.courseid = c.course_id
LEFT JOIN students s ON f.studentid = s.student_id
LEFT JOIN terms t ON e.termid = t.term_id
WHERE
f.paiddate BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59'
GROUP BY
f.enrollmentid
ORDER BY NOT EXISTS
(SELECT studentid
FROM fees f
WHERE f.enrollmentid = e.enrollment_id
AND MONTH(f.paiddate) = MONTH(CURDATE())
) DESC
";
$resultFees = mysqli_query($con, $sqlFees);
?>
<table border="1" cellpadding="8" style="border-collapse: collapse;">
<thead>
<th>Name</th>
<th>Subject</th>
<th>Jan</th>
<th>Feb</th>
<th>Mar</th>
<th>Apr</th>
<th>May</th>
<th>Jun</th>
<th>Jul</th>
<th>Aug</th>
<th>Sep</th>
<th>Oct</th>
<th>Nov</th>
<th>Dec</th>
<th>Paid so far</th>
<th>Start date</th>
</thead>
<?php while($row = mysqli_fetch_assoc($resultFees)) :
$studentId = $row['student_id'];
$studentFName = $row['firstname'];
$studentLName = $row['lastname'];
$studentFullName = $studentFName.' '.$studentLName;
$subject = $row['subject'];
$standard = $row['standard'];
$paid = $row['paid'];
$courseFee = $row['coursefee'];
$studentStartDate = $row['studentstartdate']; // this is the J.date
$monthJan = $row['MJan'];
$monthFeb = $row['MFeb'];
$monthMar = $row['MMar'];
$monthApr = $row['MApr'];
$monthMay = $row['MMay'];
$monthJun = $row['MJun'];
$monthJul = $row['MJul'];
$monthAug = $row['MAug'];
$monthSep = $row['MSep'];
$monthOct = $row['MOct'];
$monthNov = $row['MNov'];
$monthMDec = $row['MDec'];
?>
<tr>
<td><?php echo $studentId.' '.$studentFullName; ?></td>
<td><?php echo $subject.' '.$standard.'<br>'.$courseFee; ?></td>
<td id="0"><?php echo $monthJan; ?></td>
<td id="1"><?php echo $monthFeb; ?></td>
<td id="2"><?php echo $monthMar; ?></td>
<td id="3"><?php echo $monthApr; ?></td>
<td id="4"><?php echo $monthMay; ?></td>
<td id="5"><?php echo $monthJun; ?></td>
<td id="6"><?php echo $monthJul; ?></td>
<td id="7"><?php echo $monthAug; ?></td>
<td id="8"><?php echo $monthSep; ?></td>
<td id="9"><?php echo $monthOct; ?></td>
<td id="10"><?php echo $monthNov; ?></td>
<td id="11"><?php echo $monthMDec; ?></td>
<td><?php echo $paid.'/- '; ?></td>
<td><?php echo $studentStartDate; ?></td>
</tr>
<?php endwhile; ?>
</table>

Answer 1

function formatPay($value, $date, $date2) {
if($value == 0) {
if(strtotime($date) < strtotime($date2) {
$output = '-';
} else {
$output = 'Pay';
}
} else {
$output = $value;
}
return $output;
}
$studentStartDate = $row['studentstartdate']; // this is the J.date
$monthJan = formatPay($row['MJan'],$studentStartDate,'01-01-2018');
$monthFeb = formatPay($row['MFeb'],$studentStartDate,'01-02-2018');
$monthMar = formatPay($row['MMar'],$studentStartDate,'01-03-2018');
$monthApr = formatPay($row['MApr'],$studentStartDate,'01-04-2018');
$monthMay = formatPay($row['MMay'],$studentStartDate,'01-05-2018');
$monthJun = formatPay($row['MJun'],$studentStartDate,'01-06-2018');
$monthJul = formatPay($row['MJul'],$studentStartDate,'01-07-2018');
$monthAug = formatPay($row['MAug'],$studentStartDate,'01-08-2018');
$monthSep = formatPay($row['MSep'],$studentStartDate,'01-09-2018');
$monthOct = formatPay($row['MOct'],$studentStartDate,'01-10-2018');
$monthNov = formatPay($row['MNov'],$studentStartDate,'01-11-2018');
$monthMDec = formatPay($row['MDec'],$studentStartDate,'01-12-2018');

除非我在某个地方打错了字，否则这应该有效。由于您的代码有点乱，所以最简单的方法就是创建一个处理这种转换的函数。

Answer 2

我想你想要的是这样的东西。您可以在SQL中执行此操作，但由于您要求使用php解决方案，因此它就在这里。

我去掉了查询并重新格式化了一些html以获得一些垂直空间。我还使用了一个按钮模板，你可以随时调整。我在几个月里使用了一个数组，并对其进行循环，然后在html表cols中再次循环。数组不是必需的，但我更喜欢在视图/模板之外进行计算。这也使它更容易理解/阅读。

<?php
$sqlFees = <<<SQL
...
SQL;
$resultFees = mysqli_query($con, $sqlFees);
$buttonTemplate = '<button data-studentid="%s">Pay</button>';
?>
<table border="1" cellpadding="8" style="border-collapse: collapse;">
<thead>
<th>Name</th><th>Subject</th>
<th>Jan</th><th>Feb</th><th>Mar</th><th>Apr</th><th>May</th>
<th>Jun</th><th>Jul</th><th>Aug</th><th>Sep</th><th>Oct</th><th>Nov</th><th>Dec</th>
<th>Paid so far</th>
<th>Start date</th>
</thead>
<tbody>
<?php while ($row = mysqli_fetch_assoc($resultFees)) :
$button = sprintf($buttonTemplate, $row['student_id']);
$joinDate = DateTime::createFromFormat('Y-m-d H:i:s', $row['studentstartdate']);
$joinMonth = $joinDate->format('m');
$courseFee = $row['coursefee'];
$payments = [ $row['MJan'], $row['MFeb'], $row['MMar'], $row['MApr'], $row['MMay'],
$row['MJun'], $row['MJul'], $row['MAug'], $row['MSep'], $row['MOct'], $row['MNov'], $row['MDec']];
$buttons = [];
foreach ($payments as $month => $paid) {
if($joinMonth > ($month + 1)) {
$buttons[$month] = '-';
continue;
}
if(0 === (int)$paid) {
$buttons[$month] = $button;
continue;
}
$buttons[$month] = $paid;
}

?>
<tr>
<td><?= $row['firstname'] . ' ' . $row['lastname']; ?></td>
<td><?= $row['subject'] . ' ' . $row['standard'] . '<br>' . $row['paid']; ?></td>
<?php foreach($buttons as $mon => $but): ?>
<td id="<?= $mon; ?>"><?= $but; ?></td>
<?php endforeach; ?>
<td><?= $row['paid']; ?></td>
<td><?= $row['studentstartdate']; ?></td>
</tr>
<?php endwhile; ?>
</tbody>
</table>

重要的一点是：

foreach ($payments as $month => $paid) {
if($joinMonth > ($month + 1)) {
$buttons[$month] = '-';
continue;
}
if(0 === (int)$paid) {
$buttons[$month] = $button;
continue;
}
$buttons[$month] = $paid;
}

在第一个if中，它检查学生是否真的加入了，(由于我使用了数组索引，我需要在月上加一个(
在第二个if中，它检查是否没有付款，并添加按钮
默认情况下，它显示已支付的价值。

需要注意的一点是，你可以在if-elseif-else结构中这样做，但我想展示一些不同的解决方法，这种方法也稍微快一点(微优化(

Answer 3

您的问题不清楚，但我会试一试。

你可以这样做：

$date = date_create_from_format('d-m-Y', $j.date);

然后在你的桌子上：

<?= $date->format('m') < $yourMonth ? '-' : $date->format('m') == $yourMonth ? $yourNumber : $date->format('m') +1 == $yourMonth ? 'Pay' : $yourNumber ?>

希望这对有帮助