PHP mysqli fetch_row - iterate over multiple records

Question

I have been messing with this for 3 days now, researching and experimenting: it's time to ask for some help.

I have a bit of code for a schedule display page that loops over a database table, once per appointment type. It gathers all appointments of that type for the week, and returns them in a row, like this:

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

My code works exactly as I want it to, with one fatal flaw. If there's more than one row for a "Name", I get this as my row:

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

So instead of inserting into the appropriate <td>, it makes a whole new set of them. It's really frustrating because I'm sure it's something simple, but I can't see it... :(

Code:

$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";
}

George Cummins · Accepted Answer · 2015-02-17 13:19:39Z

The new rows are started here:

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

As you can see, this new row is started once per element in $apptnamestop, rather than once per row from the database. The code doesn't care how many matching rows it finds in the table, it is only going to insert one new tr per name.

There are a couple of ways to improve this situation, depending on your needs.

If you need to always insert a row per name, regardless of whether there is a matching row in the database, keep lines 4, 5 and 6 the same as they are now. We will need to modify the inner loop (foreach ($rows as $row)) to output the new table rows for every loop beyond the first one:

$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
    ...
}

If you prefer to skip creation of the table row when no matching database row is found, move the row creation statements into the inner loop entirely; this way it will run only if database rows are found.

As a final note, consider implementing a view templating system; by separating your logic code from your display code, you can easily modify either one without breaking the other. Your code becomes much simpler to read and modify in the future.

Thanks so much George!! I ended up restructuring the entirety of that code, but it was this answer that got me on the right course... I appreciate it!!

Collectives™ on Stack Overflow

PHP mysqli fetch_row - iterate over multiple records

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related