Get data from buttons populated in while loop

Question

I have a while loop that loops through my drivers table and populates the buttons with the drivers names

function driverMenu()
{
    global $conn;
    $query = mysqli_query($conn, "SELECT * FROM driver");

    while ($row = mysqli_fetch_assoc($query)) {
        $id = $row['DriverID'];
        $name = $row['driverName'];

        echo "<div class='col text-center'>
                <input type='submit' name='driverNameBtn' class='btn btn-primary rounded-pill text-light' value='{$name}'></input>
                </div>";
    }
}

I then call that function in another page that shows jobs associated to the drivers.

What I need to figure out now is how to change the data that is shown when they click on the different driver buttons.

This is my code that shows each job currently in the database but does not filter out the jobs by driver yet. But it does sort each job by days of the week.

<?php ini_set('error_reporting', E_ALL); ?>
<?php ini_set('display_errors', 1); ?>
<?php ini_set('display_startup_errors', 1); ?>

<?php include '../header.php' ?>

<!-- Page Title -->
    <div class="container-sm text-dark px-3 p-4 truckList">
        <div class="row m-auto align-items-center">
            <?php include '../includes/functions.php'; driverMenu();?>
        </div>
    </div>

    <?php 
        global $conn;
        $monday = mysqli_query($conn, "SELECT *
                                        FROM openjobs
                                        INNER JOIN driver ON openjobs.driverName_fk = driver.driverName
                                        WHERE weekday(jobDate) = 0");

        $tuesday = mysqli_query($conn, "SELECT *
                                            FROM openjobs
                                            INNER JOIN driver ON openjobs.driver_fk = driver.DriverID
                                            WHERE weekday(jobDate) = 1");

        $wednesday = mysqli_query($conn, "SELECT *
                                            FROM openjobs
                                            INNER JOIN driver ON openjobs.driver_fk = driver.DriverID
                                            WHERE weekday(jobDate) = 2");

        $thursday = mysqli_query($conn, "SELECT *
                                            FROM openjobs
                                            INNER JOIN driver ON openjobs.driver_fk = driver.DriverID
                                            WHERE weekday(jobDate) = 3");

        $friday = mysqli_query($conn, "SELECT *
                                        FROM openjobs
                                        INNER JOIN driver ON openjobs.driver_fk = driver.DriverID
                                        WHERE weekday(jobDate) = 4");
    ?>

    <!-- Truck's Weekly Job List -->
    <div class="container-fluid bg-secondary darkContainer">
        <div class="container py-5 px-4 p-3 webWeeklyPlanTruckCard">
            <div class="row gy-2"> 
                <div class="col-12">               

                    <!-- Monday -->
                    <div class="card mondayJobCard my-1">
                        <div class="card-body">
                            <div class="row justify-content-between">
                                <div class="col-11">
                                    <h5 class="card-title">Monday ...</h5>
                                </div>
                                <div class="col-1">                            
                                    <a href="/pages/webAddJob.html" class="btn btn-primary btn-sm text-light rounded-pill">Add Job</a>
                                </div>
                            </div>
                            <div class="row">
                                <div class="col pt-3">
                                    <table class="table table-bordered table-responsive">
                                        <thead>
                                            <tr class="table-light">
                                                <th scope="col" class="col-2">Job</th>
                                                <th scope="col">Driver</th>
                                                <th scope="col">Type</th>
                                                <th scope="col" class="col-2">Order #</th>
                                                <th scope="col" class="col-2">Reference</th>
                                                <th scope="col">Pallets</th>
                                                <th scope="col">Weight (kg)</th>
                                                <th scope="col" class="col-2">Status</th>
                                            </tr>
                                        </thead>

                                        <?php 
                                            while ($row = mysqli_fetch_assoc($monday)) {
                                                //$id = $row['DriverID'];
                                                $driverName_fk = $row['driverName_fk'];
                                                $jobName = $row['jobName'];
                                                $jobType = $row['jobType'];
                                                $orderNumber = $row['orderNumber'];
                                                $referenceNumber = $row['referenceNumber'];
                                                $pallets = $row['pallets'];
                                                $jobWeight = $row['jobWeight'];
                                                $jobStatus = $row['jobStatus'];
                                        
                                                echo "<tbody>
                                                        <tr>
                                                            <th>{$jobName}</th>
                                                            <th>{$driverName_fk}</td>
                                                            <td>{$jobType}</td>
                                                            <td>{$orderNumber}</td>
                                                            <td>{$referenceNumber}</td>
                                                            <td>{$pallets}</td>
                                                            <td>{$jobWeight}</td>
                                                            <td>{$jobStatus}</td>
                                                        </tr> 
                                                    </tbody>";
                                            }
                                        ?>

                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                            
                    <!-- Tuesday -->
                    <div class="card tuesdayJobCard my-2">
                        <div class="card-body">
                            <div class="row justify-content-between">
                                <div class="col-11">
                                    <h5 class="card-title">Tuesday ...</h5>
                                </div>
                                <div class="col-1">                            
                                    <a href="/pages/webAddJob.html" class="btn btn-primary btn-sm text-light rounded-pill">Add Job</a>
                                </div>
                            </div>
                            <div class="row">
                                <div class="col pt-3">
                                    <table class="table table-bordered table-responsive">
                                        <thead>
                                            <tr class="table-light">
                                                <th scope="col" class="col-2">Job</th>
                                                <th scope="col">Driver</th>
                                                <th scope="col">Type</th>
                                                <th scope="col" class="col-2">Order #</th>
                                                <th scope="col" class="col-2">Reference</th>
                                                <th scope="col">Pallets</th>
                                                <th scope="col">Weight (kg)</th>
                                                <th scope="col" class="col-2">Status</th>
                                            </tr>
                                        </thead>

                                        <?php
                                            while ($row = mysqli_fetch_assoc($tuesday)) {
                                                //$id = $row['DriverID'];
                                                $driverName_fk = $row['driverName_fk'];
                                                $jobName = $row['jobName'];
                                                $jobType = $row['jobType'];
                                                $orderNumber = $row['orderNumber'];
                                                $referenceNumber = $row['referenceNumber'];
                                                $pallets = $row['pallets'];
                                                $jobWeight = $row['jobWeight'];
                                                $jobStatus = $row['jobStatus'];
                                        
                                                echo "<tbody>
                                                        <tr>
                                                            <th>{$jobName}</th>
                                                            <th>{$driverName_fk}</td>
                                                            <td>{$jobType}</td>
                                                            <td>{$orderNumber}</td>
                                                            <td>{$referenceNumber}</td>
                                                            <td>{$pallets}</td>
                                                            <td>{$jobWeight}</td>
                                                            <td>{$jobStatus}</td>
                                                        </tr> 
                                                    </tbody>";
                                            }
                                        ?>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Wednesday -->
                    <div class="card wednesdayJobCard my-2">
                        <div class="card-body">
                            <div class="row justify-content-between">
                                <div class="col-11">
                                    <h5 class="card-title">Wednesday ...</h5>
                                </div>
                                <div class="col-1">                            
                                    <a href="/pages/webAddJob.html" class="btn btn-primary btn-sm text-light rounded-pill">Add Job</a>
                                </div>
                            </div>
                            <div class="row">
                                <div class="col pt-3">
                                    <table class="table table-bordered table-responsive ">
                                        <thead>
                                            <tr class="table-light">
                                                <th scope="col" class="col-2">Job</th>
                                                <th scope="col">Driver</th>
                                                <th scope="col">Type</th>
                                                <th scope="col" class="col-2">Order #</th>
                                                <th scope="col" class="col-2">Reference</th>
                                                <th scope="col">Pallets</th>
                                                <th scope="col">Weight (kg)</th>
                                                <th scope="col" class="col-2">Status</th>
                                            </tr>
                                        </thead>
                                        <?php 
                                          while ($row = mysqli_fetch_assoc($wednesday)) {
                                            //$id = $row['DriverID'];
                                            $driverName_fk = $row['driverName_fk'];
                                            $jobName = $row['jobName'];
                                            $jobType = $row['jobType'];
                                            $orderNumber = $row['orderNumber'];
                                            $referenceNumber = $row['referenceNumber'];
                                            $pallets = $row['pallets'];
                                            $jobWeight = $row['jobWeight'];
                                            $jobStatus = $row['jobStatus'];
                                    
                                            echo "<tbody>
                                                    <tr>
                                                        <th>{$jobName}</th>
                                                        <th>{$driverName_fk}</td>
                                                        <td>{$jobType}</td>
                                                        <td>{$orderNumber}</td>
                                                        <td>{$referenceNumber}</td>
                                                        <td>{$pallets}</td>
                                                        <td>{$jobWeight}</td>
                                                        <td>{$jobStatus}</td>
                                                    </tr> 
                                                </tbody>";  
                                          }
                                        ?>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>

                    <!-- Thursday -->
                    <div class="card thursdayJobCard my-2">
                        <div class="card-body">
                            <div class="row justify-content-between">
                                <div class="col-11">
                                    <h5 class="card-title">Thursday ...</h5>
                                </div>
                                <div class="col-1">                            
                                    <a href="/pages/webAddJob.html" class="btn btn-primary btn-sm text-light rounded-pill">Add Job</a>
                                </div>
                            </div>
                            <div class="row">
                                <div class="col pt-3">
                                    <table class="table table-bordered table-responsive">
                                        <thead>
                                            <tr class="table-light">
                                                <th scope="col" class="col-2">Job</th>
                                                <th scope="col">Driver</th>
                                                <th scope="col">Type</th>
                                                <th scope="col" class="col-2">Order #</th>
                                                <th scope="col" class="col-2">Reference</th>
                                                <th scope="col">Pallets</th>
                                                <th scope="col">Weight (kg)</th>
                                                <th scope="col" class="col-2">Status</th>
                                            </tr>
                                        </thead>
                                        <?php
                                            while ($row = mysqli_fetch_assoc($thursday)) {
                                                //$id = $row['DriverID'];
                                                $driverName_fk = $row['driverName_fk'];
                                                $jobName = $row['jobName'];
                                                $jobType = $row['jobType'];
                                                $orderNumber = $row['orderNumber'];
                                                $referenceNumber = $row['referenceNumber'];
                                                $pallets = $row['pallets'];
                                                $jobWeight = $row['jobWeight'];
                                                $jobStatus = $row['jobStatus'];
                                        
                                                echo "<tbody>
                                                        <tr>
                                                            <th>{$jobName}</th>
                                                            <th>{$driverName_fk}</td>
                                                            <td>{$jobType}</td>
                                                            <td>{$orderNumber}</td>
                                                            <td>{$referenceNumber}</td>
                                                            <td>{$pallets}</td>
                                                            <td>{$jobWeight}</td>
                                                            <td>{$jobStatus}</td>
                                                        </tr>
                                                    </tbody>";
                                            }
                                        ?>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                    
                    <!-- Friday -->
                    <div class="card fridayJobCard my-2">
                        <div class="card-body">
                            <div class="row justify-content-between">
                                <div class="col-11">
                                    <h5 class="card-title">Friday ...</h5>
                                </div>
                                <div class="col-1">                            
                                    <a href="/pages/webAddJob.html" class="btn btn-primary btn-sm text-light rounded-pill">Add Job</a>
                                </div>
                            </div>
                            <div class="row">
                                <div class="col pt-3">
                                    <table class="table table-bordered table-responsive">
                                        <thead>
                                            <tr class="table-light">
                                                <th scope="col" class="col-2">Job</th>
                                                <th scope="col">Driver</th>
                                                <th scope="col">Type</th>
                                                <th scope="col" class="col-2">Order #</th>
                                                <th scope="col" class="col-2">Reference</th>
                                                <th scope="col">Pallets</th>
                                                <th scope="col">Weight (kg)</th>
                                                <th scope="col" class="col-2">Status</th>
                                            </tr>
                                        </thead>
                                        <?php
                                            while ($row = mysqli_fetch_assoc($friday)) {
                                                //$id = $row['DriverID'];
                                                $driverName_fk = $row['driverName_fk'];
                                                $jobName = $row['jobName'];
                                                $jobType = $row['jobType'];
                                                $orderNumber = $row['orderNumber'];
                                                $referenceNumber = $row['referenceNumber'];
                                                $pallets = $row['pallets'];
                                                $jobWeight = $row['jobWeight'];
                                                $jobStatus = $row['jobStatus'];
                                        
                                                echo "<tbody>
                                                        <tr>
                                                            <th>{$jobName}</th>
                                                            <th>{$driverName_fk}</td>
                                                            <td>{$jobType}</td>
                                                            <td>{$orderNumber}</td>
                                                            <td>{$referenceNumber}</td>
                                                            <td>{$pallets}</td>
                                                            <td>{$jobWeight}</td>
                                                            <td>{$jobStatus}</td>
                                                        </tr>
                                                    </tbody>";
                                            }
                                        ?>
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <!-- Bootstrap Bundle with Popper -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.bundle.min.js" integrity="sha384-U1DAWAznBHeqEIlVSCgzq+c9gqGAJn5c/t99JyeKa9xxaYpSvHU5awsuZVVFIhvj" crossorigin="anonymous"></script>
    <script src="../JS/app.js"></script>
    <script src="../JS/ui.js"></script>

    <?php include '../footer.php' ?>

I'm assuming everything will need to be in an if (isset) etc. but can't figure out what the condition needs to be. Nor how to get the data from the original functions.

just FYI - an `input` element is self-closing so there is no need to have `` and indeed it is incorrect to do so. — Professor Abronsius, Oct 05 '21 at 06:24
Where are these buttons that you mention? Also why so much code duplication for each day of the week? — Professor Abronsius, Oct 05 '21 at 06:26
That depends on the kind of behavior you want to achieve. If you want the page to reload each time they click a button, you're on the right track. Wrap your form with the 'form' tag, define a target script (the script itself), a method (POST) and then check for `isset($_POST['nameOfButton'])`. — Refugnic Eternium, Oct 05 '21 at 06:26
If you're looking for a more dynamic behavior however, you may want to look into JavaScript for filtering. JQuery is a pretty nice JS library to do a lot of 'interactive behavior'. To actually save anything using JS, you may want to read up on 'AJAX'. — Refugnic Eternium, Oct 05 '21 at 06:28
@ProfessorAbronsius, you raise a good point. I assume, Anya is referring to the hyperlink elements as 'buttons'. And yes, I would also assume that 'using a loop to create the form' would be a lot more efficient and maintainable. — Refugnic Eternium, Oct 05 '21 at 06:29
You need to do something like this: `$stmt = mysqli_prepare($conn, "SELECT * FROM openjobs INNER JOIN driver ON openjobs.driverName_fk = driver.driverName WHERE weekday(jobDate) = 0 AND driver.DriverID = ?"); mysqli_stmt_bind_param($conn, 'd', $_REQUEST['driverNameBtn']); $monday = mysql_stmt_execute($stmt);` For details see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — kmoser, Oct 05 '21 at 06:46
Thanks @ProfessorAbronsius I had it set as an "a" and changed it to an input and didn't think to remove the closing tag. I had the code sitting in functions and called them separately but was having issues with "redeclaring functions" ? Wasn't sure how to fix it quickly so lumped it all together and repeated for each day until I have time to focus on improving it. I'm really new to php. — Anya Webb, Oct 05 '21 at 06:51
Sorry by buttons I mean the input "buttons" that display on the page. They were originally buttons but I changed them to inputs when playing around with other code I have on other pages. — Anya Webb, Oct 05 '21 at 06:52
How are you hoping to affect the display when the user clicks on a driver's name from the manu created within `driverMenu` function? Is it to show ONLY those jobs for the selected user? — Professor Abronsius, Oct 05 '21 at 06:55
When you grab the open jobs from the DB there's scope to do that with just one query and then build a multidimensional array with the returned data — SpacePhoenix, Oct 05 '21 at 07:05

Professor Abronsius · Answer 1 · 2021-10-06T06:21:56.100

The following is wholly untested and may have errors - so apologies but I think the initial page can be simplified by using a single sql select statement with no where clause and then use logic in PHP to determine which records are displayed based upon weekday number. In PHP you can use the date function to find the weekday number so the same block of code is repeated for each weekday number - and each table row now has the driver's name and ID to aid identification in Javascript. PHP & mySQL use a different numbering system for weekday - PHP uses 0 for Sunday whereas mySQL uses 0 for Monday!

<?php ini_set('error_reporting', E_ALL); ?>
<?php ini_set('display_errors', 1); ?>
<?php ini_set('display_startup_errors', 1); ?>
<?php include '../header.php' ?>

<!-- Page Title -->
    <div class="container-sm text-dark px-3 p-4 truckList">
        <div class="row m-auto align-items-center">
            <?php 
                include '../includes/functions.php';
                driverMenu();
            ?>
        </div>
    </div>

    <?php
    
        $sql='SELECT *
            FROM openjobs
            INNER JOIN driver ON openjobs.driverName_fk = driver.driverName';
        $results = $conn->query( $sql );
        
        $days=array(
            1   =>  'Monday',
            2   =>  'Tuesday',
            3   =>  'Wednesday',
            4   =>  'Thursday',
            5   =>  'Friday'
        );

        for( $i=1; $i<=5; $i++ ){
                printf('
                    <div class="container-fluid bg-secondary darkContainer">
                        <div class="container py-5 px-4 p-3 webWeeklyPlanTruckCard">
                            <div class="row gy-2"> 
                                <div class="col-12">               

                                    <!-- %1$s -->
                                    <div class="card %2$sJobCard my-1">
                                        <div class="card-body">
                                            <div class="row justify-content-between">
                                                <div class="col-11">
                                                    <h5 class="card-title">%1$s ...</h5>
                                                </div>
                                                <div class="col-1">                            
                                                    <a href="/pages/webAddJob.html" class="btn btn-primary btn-sm text-light rounded-pill">Add Job</a>
                                                </div>
                                            </div>
                                            <div class="row">
                                                <div class="col pt-3">
                                                    <table class="table table-bordered table-responsive">
                                                        <thead>
                                                            <tr class="table-light">
                                                                <th scope="col" class="col-2">Job</th>
                                                                <th scope="col">Driver</th>
                                                                <th scope="col">Type</th>
                                                                <th scope="col" class="col-2">Order #</th>
                                                                <th scope="col" class="col-2">Reference</th>
                                                                <th scope="col">Pallets</th>
                                                                <th scope="col">Weight (kg)</th>
                                                                <th scope="col" class="col-2">Status</th>
                                                            </tr>
                                                        </thead>
                    ',
                    $days[ $i ],
                    strtolower( $days[ $i ] )
                );//close printf()
                
                
            while( $row = $results->fetch_object() ) {
                if( (int)date( 'w', strtotime( $row->jobDate ) )==$i ){
                    printf('<tbody>
                                <tr data-did="%9$s" data-driver="%1$s">
                                    <th>%2$s</th>
                                    <th>%1$s</td>
                                    <td>%3$s</td>
                                    <td>%4$s</td>
                                    <td>%5$s</td>
                                    <td>%6$s</td>
                                    <td>%7$s</td>
                                    <td>%8$s</td>
                                </tr> 
                            </tbody>',
                            $row->driverName_fk,
                            $row->jobName,
                            $row->jobType,
                            $row->orderNumber,
                            $row->referenceNumber,
                            $row->pallets,
                            $row->jobWeight,
                            $row->jobStatus,
                            $row->DriverID
                    );
                }
            }
            
            
            
            
            echo '
                                    </table>
                                </div>
                            </div>
                        </div>
                    </div>';
        }
    ?>
                </div>
            </div>
        </div>
    </div>

    <!-- Bootstrap Bundle with Popper -->
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.bundle.min.js" integrity="sha384-U1DAWAznBHeqEIlVSCgzq+c9gqGAJn5c/t99JyeKa9xxaYpSvHU5awsuZVVFIhvj" crossorigin="anonymous"></script>
    <script src="../JS/app.js"></script>
    <script src="../JS/ui.js"></script>

    <?php include '../footer.php' ?>

The driverMenu function is lightly modified to include the driver ID as an attribute to the input/button - again to aid identification in Javascript. One other change was the use of OO style syntax rather than the procedural - simply for convenience.

function driverMenu(){
    global $conn;
    $results=$conn->query('select * from driver');

    while( $row = $results->fetch_object() ) {
        printf('
            <div class="col text-center">
                <input type="button" data-name="driverNameBtn" class="btn btn-primary rounded-pill text-light" data-did="%s" value="%s" />
            </div>', 
            $row->DriverID,
            $row->driverName
        );
    }
}

The Javascript to show/hide various rows - the buttons are assign the same event handler which queries the DOM to find all table rows containing data and then shows/hides rows based upon the driver name and ID - taken from attributes assigned in the button and in the table row.

<script>


    const qa=(e,n=document)=>n.querySelectorAll(e);
    
    qa('input[ data-name="driverNameBtn" ]').forEach( bttn=>bttn.addEventListener('click',function(e){
        e.preventDefault();
        qa('table.table-responsive tbody tr').forEach( tr=>{
            if( tr.dataset.did==this.dataset.did && tr.dataset.driver==this.value ){
                tr.style.display='table-row';
            }else{
                tr.style.display='none';
            }
        })
    }));
</script>

So I had to change the $row['driverName_fk'] to $row->driverName_fk because the object type couldn't be used as an array. The "buttons" now switch between users, which is awesome! But only displays jobs with dates that are on a Monday, no jobs show for the rest of the week. The containers also get smaller as the days go on including the footer that is below that code. — Anya Webb, Oct 06 '21 at 05:18
Fixed the size issue, some html at the top of the php had to be moved out and inserted before the php :) — Anya Webb, Oct 06 '21 at 06:12
`"change the $row['driverName_fk'] to $row->driverName_fk because the object type couldn't be used as an array"` - sorry about that~ silly mistake — Professor Abronsius, Oct 06 '21 at 06:20
Not a problem, any ideas on why the loop can display the different day names in the containers but stops displaying table contents after the first loop? I've had a play around with the code but nothing I've tried so far seems to be working. Thanks a lot for your help! — Anya Webb, Oct 06 '21 at 07:28
Alas - I have no way to test without the db schema and data. — Professor Abronsius, Oct 06 '21 at 07:39

Get data from buttons populated in while loop

1 Answers1