logo标签列表

PHP mysqli fetch_row - 遍历多条记录

phpmysqli
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 |
+------+------+-----+-----+-----+-----+-----+-----+-----+------+-+-----+-+-+-+-+-+-----+

所以，与其插入适当的 <td>，它创建了一整组新的 <td>。这真的很令人沮丧，因为我确定它是简单的问题，但我看不见… :(
代码:
$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";
}
1个回答
2
新的行从这里开始：
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
    ...
}

如果您希望在找不到匹配的数据库行时跳过创建表行，请将行创建语句完全移入内部循环中；这样只有在找到数据库行时才会运行。
最后，请考虑实现视图模板系统；通过将逻辑代码与显示代码分离，您可以轻松修改其中任何一个而不会影响另一个。您的代码在未来变得更加简单易读和易于修改。
非常感谢你，George！我最终重构了整个代码，但正是这个答案让我走上了正确的道路...我很感激！ - TheJester1977
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接