phpdatabasedynamic

Create a dynamic table row and submit data to database with Prepared Statement


I have a modal with dynamic table row. Sending data to the database using the normal query works fine. I need to send the data using prepared statement. The following are the codes I have used that is working fine. But I don't know how to use a prepared statement with these codes.

if (isset($_POST["save_landd_btn"])){
    for ($a = 0; $a < count($_POST["training_title"]); $a++)
    {
        $training_title = $_POST['training_title'][$a];
        $empnum = $_SESSION['empnum'];

        $query = "INSERT INTO tbl_landd (empnum, training_title, date_from, date_to, num_hours, l_n_d_type, conducted_by) 
                    VALUES ('$empnum', '" . $_POST["training_title"][$a] . "', '" . $_POST["date_from"][$a] . "', '" . $_POST["date_to"][$a] . "',
                     '" . $_POST["num_hours"][$a] . "', '" . $_POST["l_n_d_type"][$a] . "', '" . $_POST["conducted_by"][$a] . "')";
        mysqli_query($connection, $query);
    }
    $_SESSION['landd_updated']= 'The information about your training attended entitled <b>&quot;'.$training_title.'&quot;</b> has been added, successfully.';
    $_SESSION['alert-class'] = "alert-success";
    
    header ('Location: about_page2.php');
    exit(0);
}

Here is the form inside the modal body:

<form action="updateController.php" method="POST">
    <div class="modal-body">
        <div class="table-responsive">
            <table class="table text-muted table-striped table-bordered small">
                <thead class="align-middle text-center">
                    <tr>
                        <th scope="col" rowspan="2">Title of the Learning and Development (L&D)Interventions/Training/Programs</th>
                        <th scope="col" colspan="2">Inclusive Dates<br><span class="small">(Ex: December 20, 2018)</span></th>
                        <th scope="col" rowspan="2">Number of Hours</th>
                        <th scope="col" rowspan="2">Type of LD<br>(Managerial/ Supervisory/ Technical/etc.)</th>
                        <th scope="col" rowspan="2">Conducted/Sponsored by</th>
                        <th scope="col" rowspan="2">Action</th>
                    </tr>
                    <tr>
                        <th>From</th>
                        <th>To</th>
                    </tr>
                </thead>
                <tbody id="table_body"></tbody>
            </table>
        </div>
        <button type="button" class="btn btn-sm text-bg-primary" onclick="add_Item();">
            <i class="fa fa-plus" aria-hidden="true"></i> LnD
        </button>
    </div>
    <div class="modal-footer">
        <input type="hidden" name="id" value="<?=$row['id'];?>">
        <button type="button" class="btn btn-outline-secondary" data-bs-dismiss="modal">Close</button>
        <button type="submit" class="btn btn-outline-success" name="save_landd_btn">Save changes</button>
    </div>
</form>

And here is the script codes

<script>
    var newitems = 0;

    function add_Item() {
        newitems++;

        var html = "<tr>";
        html += "<td><textarea style='height: 8px' class='form-control' name='training_title[]' required></textarea></td>";
        html += "<td><input class='form-control' type='text' name='date_from[]' required></td>";
        html += "<td><input class='form-control' type='text' name='date_to[]' required></td>";
        html += "<td><input class='form-control' type='text' name='num_hours[]' required></td>";
        html += "<td><input class='form-control' type='text' name='l_n_d_type[]' required></td>";
        html += "<td><input class='form-control' type='text' name='conducted_by[]' required></td>";
        html +=
            "<td><button class='btn text-danger' type='button' onclick='delete_Row(this);'><i class='fa fa-times' aria-hidden='true'></i></button></td>"
        html += "</tr>";

        var row = document.getElementById("table_body").insertRow();
        row.innerHTML = html;
    }

    function delete_Row(button) {
        newitems--
        button.parentElement.parentElement.remove();
    }
</script>

Solution

  • There are plenty of resources here on stack and elsewhere throughout the interwebs to guide you through using prepared statements but as a quick example perhaps the following will be useful.

    The comments within the code explain the various stages.

    /*
        Rather than simply testing if the "submit" button is present
        in the POST array, you should test that the important fields
        themselves are present in the array. 
        
        You can add multiple items to `isset` and if any of them are
        no present then entire test fails so you can reasonably assume
        it is OK to proceed beyond this point.
    */
    if( $_SERVER['REQUEST_METHOD']=='POST' && isset(
        $_SESSION['empnum'],
        $_POST['training_title'],
        $_POST['date_from'],
        $_POST['date_to'],
        $_POST['num_hours'],
        $_POST['l_n_d_type'],
        $_POST['conducted_by']
    )){
        
        $empnum=$_SESSION['empnum'];
    
        /*
            Create the basic SQL INSERT statement with placeholders
            for the various values that will be piped in at runtime.
        */
        $sql='INSERT INTO `tbl_landd` 
            ( `empnum`, `training_title`, `date_from`, `date_to`, `num_hours`, `l_n_d_type`, `conducted_by`) 
            values 
            ( ?, ?, ?, ?, ?, ?, ? )';
        
        /*
            Create the "Prepared Statement"
            ...and then bind the Placeholders to named variables!
            Note: In mySQLi the named variables do not need to exist at this stage
            whereas in PDO they do.
            
            The placeholders here have been assigned with the type "s" - for String
            There are other possible placeholder types but "s" generally suffices
            
        */
        $stmt=$connection->prepare($sql);
        $stmt->bind_param('sssssss', $empnum, $training_title, $date_from, $date_to, $num_hours, $l_n_d_type, $conducted_by );
    
        
        
        /*
            As each named field in the POST array uses array syntax
            you need to find and use an index to access individual items
            within each named element. A `foreach` loop makes this easy.
        */
        foreach( $_POST['training_title'] as $index => $training_title ){
            /* 
                It is at this stage that the previously declared named variables
                are created and populated with values directly from the POST items.
                
                Once the variables are populated execute the SQL statement.
            */
            $date_from=$_POST['date_from'][$index];
            $date_to=$_POST['date_to'][$index];
            $num_hours=$_POST['num_hours'][$index];
            $l_n_d_type=$_POST['l_n_d_type'][$index];
            $conducted_by=$_POST['conducted_by'][$index];
            
            $stmt->execute();
        }
        $stmt->close();
        
    
        $_SESSION['landd_updated']= 'The information about your training attended entitled <b>&quot;'.$training_title.'&quot;</b> has been added, successfully.';
        $_SESSION['alert-class'] = "alert-success";
        
        exit( header('Location: about_page2.php') );
    }