I have a document classification table based on user role, for example:

A worker table

And a workforce_docs where are uploaded pdf

I'm trying to get with foreach document classification and with a new select get document document validity

<table >
    <tbody>
        <?php
            foreach ($data as $row) {
            $workforce_class_worker_doc_id = $row['workforce_class_worker_doc_id'];
            $workforce_class_worker_doc_description = $row['workforce_class_worker_doc_description'];

            $stmt = $conn->prepare(' SELECT workforce_doc_class_id, workforce_doc_valid_from, workforce_doc_valid_to
                                       FROM workforce_docs
                                      WHERE workforce_doc_class_id = ?
                                        AND workforce_doc_worker_id = ? ');
            $stmt->bind_param('ii', $workforce_class_worker_doc_id, $workforce_worker_id);
            $stmt->execute();
            $stmt->bind_result( $workforce_doc_class_id, $workforce_doc_valid_from, $workforce_doc_valid_to ); 
            $stmt->fetch();
            $stmt->close();
        ?>

            <tr>
                <th><?php echo $workforce_class_worker_doc_description; ?></th>
                <th><?php echo $workforce_doc_valid_from; ?></th>
                <th><?php echo $workforce_doc_valid_to; ?></th>
                <td><button >  Update Document</button></td>
            </tr>

        <?php
            }
        ?>
    </tbody>
</table>

I'm experiencing a trouble with foreach, i expect result like this:

But my result is

seem get previous variables, need must be blank because i not have record on doc table

CodePudding user response：

I suggest to make a LEFT JOIN before that, query is simply and more faster

// in your foreach query your query add
LEFT JOIN workforce_docs ON workforce_doc_class_id = workforce_class_worker_doc_id

and change your foreach to:

 foreach ($data as $row) {
     $workforce_class_worker_doc_id = $row['workforce_class_worker_doc_id'];
     $workforce_class_worker_doc_description = $row['workforce_class_worker_doc_description'];
     $workforce_doc_valid_from = $row['workforce_doc_valid_from'];
     $workforce_doc_valid_to = $row['workforce_doc_valid_to'];
     ... rest of the code ...

CodePudding user response：

Here's the issue:

$stmt->fetch();

This doesn't have result on the second run, therefore

$stmt->bind_result( $workforce_doc_class_id, $workforce_doc_valid_from, $workforce_doc_valid_to ); 

Is not executed. This would make $workforce_doc_class_id, $workforce_doc_valid_from, $workforce_doc_valid_to to have previous value.

I would suggest to reset value prior to that line:

foreach ($data as $row) {
    $workforce_doc_class_id = '';
    $workforce_doc_valid_from = '';
    $workforce_doc_valid_to = '';
    ...
    //rest of the code