Select Not Populating in Dynamically ID'd Bootstrap 4 Modal

Question

Where I Am Now

I have an HTML table with many rows populated by a PHP foreach loop from a SQL statement. The final column of the table contains a drop-down list of buttons, one of which is "Assign Vehicle". This button triggers a bootstrap-4 modal with a select box. The table is a list of vehicles, the select box within the modal is a list of employees.

What I Want to Happen

The user clicks "Assign Vehicle" in the table. The modal pops up allowing the user to select an employee for assignment. The vehicle_id and employee_id are then transported to another page for processing.

The Code



<table id="example23" class="display nowrap table table-hover table-striped table-bordered" cellspacing="0" width="100%"  data-order='[[ 3, "asc" ]]' data-page-length='25'>
    <thead>
        <tr>
            <th class="text-center">Year</th>
            <th class="text-center">Make</th>
            <th class="text-center">Model</th>
            <th class="text-center">Unit #</th>
            <th class="text-center">Asset #</th>
            <th class="text-center">Assignee</th>
            <th class="text-center">Admin</th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th class="text-center">Year</th>
            <th class="text-center">Make</th>
            <th class="text-center">Model</th>
            <th class="text-center">Unit #</th>
            <th class="text-center">Asset #</th>
            <th class="text-center">Assignee</th>
            <th class="text-center">Admin</th>
        </tr>
    </tfoot>
    <tbody>
        <?php 
            $a = 0;
            $u = 0;

            foreach ($vehicle->fetchAll() as $row){

            $a++;
            $u++;
        ?>
        <tr>
            <td style="text-align: center; vertical-align: middle;"><?php echo $row['Veh_Year']; ?></td>
            <td style="text-align: center; vertical-align: middle;"><?php echo $row['Vehicle_Make_Make']; ?></td>
            <td style="text-align: center; vertical-align: middle;"><?php echo $row['Vehicle_Model_Model']; ?></td>
            <td style="text-align: center; vertical-align: middle;"><?php echo $row['Veh_Unit_No']; ?></td>
            <td style="text-align: center; vertical-align: middle;"><?php echo $row['Veh_Asset_No']; ?></td>
            <!--Creates Unassigned Tag for Vehicle if Database doesn't return a value for Assignee First Name  -->
            <?php if (!empty($row['First_Name'])) { ?>
            <td style="text-align: center; vertical-align: middle;"><a href="#"><?php echo $row['First_Name']; echo ' ' . $row['Last_Name']; ?></a></td>
            <?php } else { ?>
            <td style="text-align: center; vertical-align: middle;"><strong>Unassigned</strong></td>
            <?php } ?>
            <td style="text-align: center;">
                <div class="btn-group">
                    <button type="button" class="btn btn-info dropdown-toggle" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
                        Admin Action
                    </button>
                    <div class="dropdown-menu">
                    <?php if (!empty($row['First_Name'])) { ?>
                    <a class="dropdown-item" href="#" data-toggle="modal" data-target="#Unassign_<?php echo $u; ?>">Unassign Vehicle</a>
                    <?php } else { ?>
                    <a class="dropdown-item" href="#" data-toggle="modal" data-target="#Assign_<?php echo $a; ?>">Assign Vehicle</a>
                    <?php } ?>

                    <a class="dropdown-item" href="https://mailmelater.com/modify-vehicle.php?vehid=<?php echo $row['Veh_ID']; ?>">Modify Vehicle</a>
                    <a class="dropdown-item" href="https://mailmelater.com/retire-vehicle.php?vehid=<?php echo $row['Veh_ID']; ?>">Retire Vehicle</a>
                </div>    
            </td>
        </tr>
        <div class="modal fade" id="Unassign_<?php echo $u; ?>" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
            <div class="modal-dialog modal-dialog-centered" role="document">
                <div class="modal-content">
                    <div class="modal-header">
                        <h5 class="modal-title" id="exampleModalLongTitle">Unassign Confirmation</h5>
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                            <span aria-hidden="true">&times;</span>
                        </button>
                    </div>
                    <div class="modal-body">
                        Are you certain that you would like to unassign the 
                        <span class="font-weight-bold"><?php echo $row['Vehicle_Make_Make']; echo ' ' . $row['Vehicle_Model_Model']; ?></span> 
                        from 
                        <span class="font-weight-bold"><?php echo $row['First_Name']; echo ' ' . $row['Last_Name']; ?></span>?
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary waves-effect waves-light" data-dismiss="modal">Cancel</button>
                        <button type="button" class="btn btn-info waves-effect waves-light">
                            <a class="text-white" href="https://mailmelater.com/includes/functions/fn-unassign-vehicle.php?vehid=<?php echo $row['Veh_ID']; ?>">Confirm</a>
                        </button>
                    </div>
                </div>
            </div>
        </div>
        <div class="modal fade" id="Assign_<?php echo $a; ?>" tabindex="-1" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true">
            <div class="modal-dialog modal-dialog-centered" role="document">
                <div class="modal-content">
                    <div class="modal-header">
                        <h5 class="modal-title" id="exampleModalLongTitle">Assign Vehicle to User</h5>
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                            <span aria-hidden="true">&times;</span>
                        </button>
                    </div>
                    <div class="modal-body">
                        <select name="check" class="select2 form-control custom-select" style="width: 100%;">
                            <?php foreach ($assign->fetchall() as $assignrow) { ?>                                      
                            <option value="<?php echo $assignrow['Emp_ID']; ?>">
                                <?php echo $assignrow['First_Name']; echo ' ' . $assignrow['Last_Name']; ?>
                            </option>
                            <?php } $assign->closeCursor(); ?>
                        </select>
                    </div>
                    <div class="modal-footer">
                        <button type="button" class="btn btn-secondary waves-effect waves-light" data-dismiss="modal">Cancel</button>
                        <button type="button" class="btn btn-info waves-effect waves-light">
                            <a class="text-white" href="https://mailmelater.com/includes/functions/fn-assign-vehicle.php?vehid=<?php echo $row['Veh_ID']; ?>">Confirm</a>
                        </button>
                    </div>
                </div>
            </div>
        </div>
        <?php } $vehicle->closeCursor(); ?>
    </tbody>
</table>

The Problem

You'll notice from the code that the "Assign" modal ID is dynamically assigned. I feel as though this is necessary in order to pass on the respective rows vehicle ID to the modal. Unfortunately, as long as the modals ID is assigned dynamically, this solution only works for the very first record (or when the Veh_ID = 1), for any other row the modal shows up with the select box but there are no results, the select box is empty.

The "Unassign" modal works perfectly fine as-written even with the dynamically assigned ID. This only seems to be a problem with the select box from my perspective, though I'm a novice coder at best.

Example

Example page with both functions active.

On the page linked above, for any row in the table, if you hover "Modify Vehicle" you can see the ID in the address bar. Clicking "Assign Vehicle" on any vehicle that doesn't have vehicle_id = 1 will result in an empty select box in the modal. Feel free to assign or unassign as needed.

Don't Panic · Accepted Answer · 2019-11-14T07:44:58.290

Viewing the source of your example page, I see that your modals etc are all being created correctly, the only problem is the list of employees in the select only appearing the first time. So, look at how that is generated - $assign. That is where the problem is.

Checking the docs for fetchall():

fetchAll() returns an array containing all of the remaining rows in the result set

Note remaining. After the first full iteration in the first table row, you've already retrieved all the results. There's nothing left to fetch. So on the next iteration there are no remaining results to fetch, and you get an empty <select>.

If you want to fetch everything from the same query again, you need to re-execute() the statement.

Your <select> is identical in every row. So rather than repeatedly retrieving your results, it would be much simpler to retrieve them once, outside of your main $vehicle table loop, and set up a $select variable that you can just then echo on every row.

If you want to have the right employee pre-selected on each row, to show who is currently assigned, you could do that with a bit of preg_replace()ing on each row.

One other note - you're also using:

$assign->closeCursor();

So maybe you were intending to make the result set scrollable. The docs describe how to do that, and to make sure that you scroll back to the beginning of the set once you've reached the end. Though AFAIK you'd typically only need this if you intended to retrieve only some of the results, or switch to handling a different query before finishing with the first. You're not doing either of those things so I am not sure using the cursor is required at all.

Note your question has a few elements but each of them more or less has a duplicate here on SO already. It is worth reading through all the answers to the following questions as many of them are helpful and highlight different parts of the problems.

PDO multiple fetching of same query

Resetting array pointer in PDO results

When should I use closeCursor() for PDO statements?

Thank you very much, not only for your answer but the way you answered as well. — Robert Seal, Nov 15 '19 at 10:53

Select Not Populating in Dynamically ID'd Bootstrap 4 Modal

1 Answers1