Insert data in database using checkbox different row

Question

Scenario: I am developing a sales and inventory system and I am on the Purchase Order stage now. What I am trying to do is to insert a data of checkbox to the database for item_po table.

Scenario: I already generate all the items that have below minimum stock (need to replenish). I add a checkbox on the left side of the data generated (see picture for reference). I want to insert that data to my item_po table and prepare it for generation of reports base on the items checked for purchase order. Is that possible?

This is my current code for insert but it's not working.

<?php
require_once('../../inc/config/constants.php');
require_once('../../inc/config/db.php');

$itemDetailsSearchSql = 'SELECT * FROM item WHERE stock < itemMinStock';
$itemDetailsSearchStatement = $conn->prepare($itemDetailsSearchSql);
$itemDetailsSearchStatement->execute();



$output = '<table id="itemMinStockReportsTable" class="table table-sm table-bordered table-hover" style="width:100%">
            <thead>
                <tr>
                    <th style="width: 10px !important;"><input type="checkbox" name="check" class="cbxMain" onchange="checkMain(this)"/></th>
                    <th>Lookup Code</th>
                    <th>Item Name</th>
                
                    <th>Current Stock</th>
                    <th>Unit Cost</th>
                    <th>Status</th>
                    <th>Description</th>
                    <th>Minimum Stock</th>
                    <th>Vendor</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody>';

// Create table rows from the selected data
while($row = $itemDetailsSearchStatement->fetch(PDO::FETCH_ASSOC)){
$itemNumber = $row['itemNumber'];
$itemName = $row['itemName'];
$stock = $row['stock'];
$unitPrice = $row['unitPrice'];
$status = $row['status'];
$description = $row['description'];
$itemMinStock = $row['itemMinStock'];
$itemVendor = $row['itemVendor'];

    $output .= '<tr>' .
                    
                    '<td>' . ' <input type="checkbox" name="check[]" class="check"  '. '</td> '. 
                    '<td>' . $itemNumber . '</td>' .
                    //'<td>' . $row['itemName'] . '</td>' .
                    '<td><a href="#" class="itemDetailsHover" data-toggle="popover" id="' . $row['productID'] . '">' . $itemName . '</a></td>' .
    
                    '<td>' . $stock. '</td>' .
                    '<td>' . $unitPrice. '</td>' .
                    '<td>' . $status . '</td>' .
                    '<td>' . $description . '</td>' .
                    '<td>' . $itemMinStock . '</td>' .
                    '<td>' . $itemVendor . '</td>' .
                    '<td>' .'<button type="button" id="deleteItem" class="btn btn-danger">Delete</button> '. '</td>' .
                '</tr>';
}

$itemDetailsSearchStatement->closeCursor();

$output .= '</tbody>
                <tfoot>
            
                <br>
                <th> <br>   <button type="button" id="addItem" name="btn_po" class="btn btn-success">Proceed to PO</button> </th>
            
                 </tfoot>
            </table>';
echo $output;


if(isset($_POST['btn_po']))
{
   $checkbox = $_POST['check'];         
   for($i=0;$i<count($checkbox);$i++)
   {
        $check_id = $checkbox[$i];


        $stockSql = 'SELECT stock FROM item WHERE itemNumber=:itemNumber';
            $stockStatement = $conn->prepare($stockSql);
            $stockStatement->execute(['itemNumber' => $itemNumber]);
            if($stockStatement->rowCount() > 0){
                //$row = $stockStatement->fetch(PDO::FETCH_ASSOC);
                //$quantity = $quantity + $row['stock'];
                echo '<div class="alert alert-danger"><button type="button" class="close" data-dismiss="alert">&times;</button>Item already exists in DB. Please click the <strong>Update</strong> button to update the details. Or use a different Item Number.</div>';
                exit();
            } else {
                // Item does not exist, therefore, you can add it to DB as a new item
                // Start the insert process
                $insertItemSql = 'INSERT INTO item_po(itemNumber,itemName,stock,unitPrice,status,description,itemMinStock,itemMaxStock,itemVendor) VALUES (:itemNumber, :itemName,:stock, :unitPrice, :status, :description, :itemMinStock, :itemVendor)';

                $insertItemStatement = $conn->prepare($insertItemSql);
                $insertItemStatement->execute(['itemNumber' => $itemNumber, 'itemName' => $itemName, 'stock' => $stock, 'unitPrice' => $unitPrice, 'status' => $status, 'description' => $description, 'itemMinStock' => $itemMinStock, 'itemVendor' => $itemVendor   ]);

                echo '<div class="alert alert-danger"><button type="button" class="close" data-dismiss="alert">&times;</button>Item failed to insert in database.</div>';
                exit();
        }
    }
}

?>

Answer 1

Your checkbox is should be inside a <form> tag.

Also, you need to wrap all data you need inside with <input> tag and give each column field array name like name=data1[] so you can retrieve back the data after you submit the form. ( You can use $_GET[ ] or $_POST[ ] method ).

Updated sample#1: Completed form

Just a sample. You can add your MySQL INSERT inside foreach(), where it will traverse through each row having checkbox checked.

<!DOCTYPE html>
<html>
<head>
    <title>Multiple Checkbox Form: Insert data only when Checkbox is checked</title>
<style type="text/css">
    table{
        width:250px;
    }
    table td {
        padding:5px;
        border: 1px solid black;
    }
</style>
</head>
<body>
<form action="" method="post">
    <table id="table">
        <tr>
            <?php $id = 1; ?>
            <td> <input type="checkbox" name="check[]" value="<?php echo $id; ?>" <?= in_array('1',$_POST['check'])? 'checked' : ''; ?> ></td>
            <td><input type="hidden" name="name[<?php echo $id; ?>]" placeholder="name" value="Name1"/>Name1</td>
            <td><input type="hidden" name="age[<?php echo $id; ?>]" placeholder="age" value="Age1">Age1</td>
            <td><button class="delBtn">Delete</button></td>
        </tr>
        <tr>
            <?php $id = 2; ?>
            <td> <input type="checkbox" name="check[]" value="<?php echo $id; ?>" <?= in_array('2',$_POST['check'])? 'checked' : ''; ?>></td>
            <td><input type="hidden" name="name[<?php echo $id; ?>]" placeholder="name" value="Name2"/>Name2</td>
            <td><input type="hidden" name="age[<?php echo $id; ?>]" placeholder="age" value="Age2">Age2</td>
            <td><button class="delBtn">Delete</button></td>
        </tr>
        <tr>
            <?php $id = 3; ?>
            <td> <input type="checkbox" name="check[]" value="<?php echo $id; ?>" <?= in_array('3',$_POST['check'])? 'checked' : ''; ?>></td>
            <td><input type="hidden" name="name[<?php echo $id; ?>]" placeholder="name" value="Name3"/>Name3</td>
            <td><input type="hidden" name="age[<?php echo $id; ?>]" placeholder="age" value="Age3">Age3</td>
            <td><button class="delBtn">Delete</button></td>
        </tr>

    </table>
  <input type="submit" name="submit" value="Proceed to PO">
</form>

<div id="result">
    <p>Data sent to PO DB:</p>
    <ol>
        <?php
            if (isset($_POST['submit'])){
                $name=$_POST['name']; //array received
                $age=$_POST['age']; //array received
                $checks=$_POST['check']; //array received (checked only)
                $i = 0;
                /* Foreach checked checkbox: Echo each data row */
                /* Add your Insert Query Here */
                foreach($checks as $key=>$check) //loop through all checks that are sent
                {
                    echo "<li>[Name ".$check."] = ".$name[$check].", [Age ".$check."] = ".$age[$check]."</li>";
                }
            }
        ?>
    </ol>
</div>
</body>
</html>