PHP mysqli fetch_row - 遍历多条记录

Question

我已经搞砸了 3 天，研究和试验:是时候寻求帮助了。

我有一些代码用于循环遍历数据库表的计划显示页面，每个约会类型一次。它收集一周内该类型的所有约会，并连续返回它们，如下所示:

+------+------+-----+-----+-----+-----+-----+-----+-----+------+
| Name | Year | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Purp |
+------+------+-----+-----+-----+-----+-----+-----+-----+------+
| IST  |  NA  | 9-4 |     |     |     |     |     |     | ABC  |
+------+------+-----+-----+-----+-----+-----+-----+-----+------+
| OT   |  NA  |     | 8-2 | 8-2 | 8-2 | 8-2 |     |     | DEF  |
+------+------+-----+-----+-----+-----+-----+-----+-----+------+

我的代码完全按照我的意愿运行，但有一个致命缺陷。如果“名称”不止一行，我会把它作为我的行:

+------+------+-----+-----+-----+-----+-----+-----+-----+------+
| Name | Year | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Purp |
+------+------+-----+-----+-----+-----+-----+-----+-----+------+
| IST  |  NA  | 9-4 |     |     |     |     |     |     | ABC  |
+------+------+-----+-----+-----+-----+-----+-----+-----+------+
| OT   |  NA  |     | 8-2 | 8-2 | 8-2 | 8-2 |     |     | DEF  |
+------+------+-----+-----+-----+-----+-----+-----+-----+------+-+-----+-+-+-+-+-+-----+
| MTG  |      | 1-2 |     |     |     |     |     |     |      | | 1-2 | | | | | | GHI |
|      |      |     |     |     |     |     |     |     |      | |     | | | | | | JKL |
+------+------+-----+-----+-----+-----+-----+-----+-----+------+-+-----+-+-+-+-+-+-----+

因此，它不是插入到适当的 中，而是创建了一组全新的它们。这真的很令人沮丧，因为我确信它很简单，但我看不到它......:(

代码:

$apptnamestop = array("IST", "OT", "MTG", "TR-CN", "EVENT", "EN", "REC", "TO");
$daysofweek = array("1", "2", "3", "4", "5", "6", "0");
foreach ( $apptnamestop as $name) {
    print str_repeat($tab, 8) . "<tr>\n";
    print str_repeat($tab, 9) . "<td class=\"td1s\">$name</td>\n";
    print str_repeat($tab, 9) . "<td class=\"td1s\">N/A</td>\n";
    $getdataquery = "SELECT appointmentName as name
, appointmentType as type, appointmentStartDateTime as sdt
, appointmentEndDateTime as edt, appointmentPurpose as purp 
from tblAppointments 
where appointmentStaffIDsToAttend like '%{$_SESSION['user_id']}%' 
and appointmentName = '$name' 
and (appointmentStartDateTime >= DATE_ADD(CURDATE(), INTERVAL (9 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY) 
and appointmentEndDateTime < DATE_ADD(CURDATE(), INTERVAL (16 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY))
";
    $getdataqueryresults = $mysqli->query($getdataquery)  
       or trigger_error("<p class=\"error\">We're very sorry, but an error has occurred when interacting with the CHAIRS database.  Please try again and see if the error repeats.  If it does, please get the following information in its entirety to your database adminapptrator so the CHAIRS developer can get the error resolved.<br />Error Message: " . $mysqli->error, E_USER_ERROR);
    $datarowcnt = $getdataqueryresults->num_rows;
    if ($datarowcnt > 0) {
        while ($row = $getdataqueryresults->fetch_row()) {
            $rows[] = $row;
        }

        foreach ($rows as $row) {
            $title = $row[0];
            $type = $row[1];
            $sdt = $row[2];
            $edt = $row[3];
            $purp = $row[4];

            $c=7;

            if ($type == 1) {
                $typew = "Mandatory";
            } else {
                $typew = "Elective";
            }

            $sparts = explode(" ", $sdt);
            $eparts = explode(" ", $edt);
            $tdiff = getTimeDiff($sparts[1], $eparts[1]);

            foreach ($daysofweek as $day) {
                if ($title == $name) {
                    if ($day == date('w', strtotime("$sparts[0]"))) {
                        if ($sparts[0] == $eparts[0]) {
                            print str_repeat($tab, 9) . "<td class=\"td1s\">$sparts[1] - $eparts[1]<br />($tdiff) - $typew</td>\n";
                            $c--;
                        } else {
                            $s = strtotime("$sparts[0]");
                            $e = strtotime("$eparts[0]");

                            for ($i=$s; $i<=$e; $i+=86400) {
                                print str_repeat($tab, 9) . "<td class=\"td1s\">$sparts[1] - $eparts[1]<br />($tdiff) - $typew</td>\n";
                                $c--;
                            }
                        }
                    } 
                    if ( $c > 0) {
                            $c--;
                            print str_repeat($tab, 9) . "<td class=\"td1s\"></td>\n";
                    }
                }
            }
        }
        $rc++;
    } else {
        foreach ($daysofweek as $day) {
            print str_repeat($tab, 9) . "<td class=\"td1s\"></td>\n";
        }
    }
    print str_repeat($tab, 9) . "<td class=\"td1s\">$purp</td>\n";
    $purp = "";
    print "</tr>\n";
}

Answer 1

新行从这里开始:

foreach ( $apptnamestop as $name) {
    print str_repeat($tab, 8) . "<tr>\n";
    print str_repeat($tab, 9) . "<td class=\"td1s\">$name</td>\n";

如您所见，这个新行在 $apptnamestop 中针对每个元素启动一次，而不是数据库中的每一行启动一次。该代码不关心它在表中找到了多少匹配行，它只会为每个名称插入一个新的 tr。

有几种方法可以改善这种情况，具体取决于您的需要。

如果您需要始终为每个名称插入一行，而不管数据库中是否有匹配的行，请保持第 4、5 和 6 行与现在相同。我们需要修改内部循环 (foreach ($rows as $row)) 以输出第一个循环之后的每个循环的新表行:

$rowCounter = 0;
foreach ($rows as $row) {
    if ($rowCounter > 0) {
        print "</tr>\n"; // Close the previous table row
        print str_repeat($tab, 8) . "<tr>\n";
        print str_repeat($tab, 9) . "<td class=\"td1s\">$name</td>\n";
        print str_repeat($tab, 9) . "<td class=\"td1s\">N/A</td>\n";
    }
    $rowCounter++;
    // Remainder of the loop code goes here
    ...
}

如果您希望在找不到匹配的数据库行时跳过表行的创建，请将行创建语句完全移入内部循环；这样它只会在找到数据库行时运行。

最后一点，考虑实现一个 View 模板系统；通过将逻辑代码与显示代码分开，您可以轻松地修改其中一个而不会破坏另一个。您的代码将来会变得更易于阅读和修改。