Insert Data from Input Values With Pagination

Insert Data from Input Values With Pagination

lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

I am trying to create a page that has a dynamic datatable with several pages. The first column is a checkbox where people can select which resources they want to subscribe to. I have one Submit button at the top of the page. The page works and inserts the multiple records into the database when I stay on the same page.

The issue is if I select a few on one page and then another page and then hit submit, only the ones selected on the current page displayed get inserted. Here is my code:


<script type="text/javascript" class="init">
$(document).ready(function() {
    var table =  $('#example').DataTable( {
stateSave: true,
        "order": [[ 1, "asc" ],[ 2, "asc" ]],
        "lengthMenu": [[25, 50, 1000, -1], [25, 50, 100, "All"]],
        lengthChange: false,
        buttons: [ 'excel', 'colvis']
    } );
 
    table.buttons().container()
        .appendTo( '#example_wrapper .col-md-6:eq(0)' 
                    );  
} );

    </script>

Table and Form:

<form enctype="multipart/form-data" method="post" name="form_subscribe" id="form_subscribe"><input name="userID" type="hidden" id="userID" value="<?php echo($logged_user->getColumnVal("userID")); ?>">
    <button class="btn btn-lg btn-info mb-3" type="submit" name="submit" id="submit">Subscribe</button><br><table id="example" class="table table-responsive table-striped table-bordered display" width="100%" border="0" cellspacing="0">
      <thead>
        <tr>
        <th align="left" valign="top"><strong><font size="-1">Subscribe to Progress</font></strong></th>
        <th align="left" valign="top"><strong><font size="-1">Logo</font></strong></th>
          <?php
  if ("".$_GET['state']  ."" == "NH" or "".$_GET['state']  ."" == "MA" or "".$_GET['state']  ."" == "RI") {  // WebAssist Show If
?><th align="left" valign="top"><strong><font size="-1">Paying Member</font></strong></th><?php
  } // ("".$_GET['state']  ."" == "NH" or "".$_GET['state']  ."" == "MA" or "".$_GET['state']  ."" == "RI")
?>
        <th align="left" valign="top"><strong><font size="-1">District Name</font></strong></th>
        <th align="left"  valign="top"><strong><font size="-1">Company Name</font></strong></th>
          <th align="left"  valign="top"><strong><font size="-1">Resource Name</font></strong></th>
           <th align="left" valign="top"><strong><font size="-1">Progress</font></strong></th>
           <th align="left" valign="top"><strong><font size="-1">Last Modified On</font></strong></th>
           
        </tr>
      </thead>
      <tbody>
        <?php
$wa_startindex = 0;
while(!$district_results_private->atEnd()) {
  $wa_startindex = $district_results_private->Index;
?>
        <tr>
        <td>
         <input class="dataID" type="checkbox" value="<?php echo($district_results_private->getColumnVal("data_final")); ?>" id="dataID" name="dataID[]">
            </td>

Any help would be appreciated. Thanks

Answers

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    edited December 2022

    When using Datatables only the rows shown on the page or in the DOM. Using Javascript or jQuery methods won't work to get the checked checkboxes across pages. This example shows one option using Datatables APIs:
    http://live.datatables.net/mutavegi/1/edit

    It uses rows() with a row-selector as a function to get the row's HTML node to find the checkbox input.

    If you still need help please provide a test case that shows what you have so we can offer suggestions.
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    Using this example, will this work with a dynamic table where the checkbox value is pulled dynamically based on the recordset?

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736

    Chances are the code in the example will need to be modified for your solution. I might be missing it from your code snippets but how are you getting the checked checkboxes now?

    In this part of the code

        var data = table
        .rows( function ( idx, data, node ) {
            return $(node).find('input[type="checkbox"][name="chkbx"]').prop('checked');
        } )
    

    The node is the row's node. The return statement will need to be adjusted to meet the specifics of your solution to find the checked checkboxes.

    Kevin

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    This is the code that gathers the data from the checkboxed and inserts it into the database:

    <?php  
    if (isset($_POST["submit"]) || isset($_POST["submit_x"])) {
    for($i=0;$i<count($_POST['dataID']);$i++){
            $userID = $_POST['userID'];
            $dataID = $_POST['dataID'][$i];
            if($userID!=='' && $dataID!==''){
        $sql="INSERT INTO subscription(dataID, userID)VALUES($dataID,$userID)";
                $stmt=$sdpc_i->prepare($sql);
                $stmt->execute();
                //echo '<div class="alert alert-success" role="alert">Submitted Successfully</div>';
            }
            else{
                
                echo '<div class="alert alert-danger" role="alert">Error Submitting in Data</div>';
            }
        }
        echo "<script type='text/javascript'>";
            echo "alert('Subscribed successfully')";
            echo "</script>";
    }
    ?>
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,053 Site admin

    I don't know how you are doing your Ajax submit, but it looks like you want to assign the row ids into a dataID property.

    var data = table
      .rows( function ( idx, data, node ) {
        return $(node).find('input[type="checkbox"][name="chkbx"]').prop('checked');
      } )
      .data()
      .pluck('id')
      .toArray();
    
    var dataToSubmit = {
      dataID: data
    };
    
    // Send to server
    

    I don't know if .pluck('id') is what you want - it is if your row data has an id property. Adjust as needed.

    Also, your HTML is not valid. I'd suggest you run your page through the W3C validator. DataTables assumes that valid HTML will be given to it.

    Allan

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    Ok. Thanks so much. It isn't using an Ajax submit. Just a regular form post with submit button. There are hidden fields in the form to know which records to be added.

  • allanallan Posts: 61,446Questions: 1Answers: 10,053 Site admin

    Awesome - loop over the data array and create the input elements you need in that case.

    Allan

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    Yes, that is what I did in the code above and it works but, it only inserts the data from that page, not the checked boxes on the other page.

  • kthorngrenkthorngren Posts: 20,142Questions: 26Answers: 4,736
    edited December 2022

    Are all of the pages or just the current page stored in the data variable with this statement?

    var data = table
      .rows( function ( idx, data, node ) {
        return $(node).find('input[type="checkbox"][name="chkbx"]').prop('checked');
      } )
      .data()
      .pluck('id')
      .toArray();
    

    Kevin

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    I haven't added that statement. All I have is this:

    The Datatables Scripts:

    <script type="text/javascript" class="init">
    $(document).ready(function() {
        var table =  $('#example').DataTable( {
    stateSave: true,
            "order": [[ 1, "asc" ],[ 2, "asc" ]],
            "lengthMenu": [[25, 50, 1000, -1], [25, 50, 100, "All"]],
            lengthChange: false,
            buttons: [ 'excel', 'colvis']
        } );
      
        table.buttons().container()
            .appendTo( '#example_wrapper .col-md-6:eq(0)'
                        ); 
    } );
     
        </script>
    

    The Table and Form:

    <form enctype="multipart/form-data" method="post" name="form_subscribe" id="form_subscribe"><input name="userID" type="hidden" id="userID" value="<?php echo($logged_user->getColumnVal("userID")); ?>">
        <button class="btn btn-lg btn-info mb-3" type="submit" name="submit" id="submit">Subscribe</button><br><table id="example" class="table table-responsive table-striped table-bordered display" width="100%" border="0" cellspacing="0">
          <thead>
            <tr>
            <th align="left" valign="top"><strong><font size="-1">Subscribe to Progress</font></strong></th>
            <th align="left" valign="top"><strong><font size="-1">Logo</font></strong></th>
              <?php
      if ("".$_GET['state']  ."" == "NH" or "".$_GET['state']  ."" == "MA" or "".$_GET['state']  ."" == "RI") {  // WebAssist Show If
    ?><th align="left" valign="top"><strong><font size="-1">Paying Member</font></strong></th><?php
      } // ("".$_GET['state']  ."" == "NH" or "".$_GET['state']  ."" == "MA" or "".$_GET['state']  ."" == "RI")
    ?>
            <th align="left" valign="top"><strong><font size="-1">District Name</font></strong></th>
            <th align="left"  valign="top"><strong><font size="-1">Company Name</font></strong></th>
              <th align="left"  valign="top"><strong><font size="-1">Resource Name</font></strong></th>
               <th align="left" valign="top"><strong><font size="-1">Progress</font></strong></th>
               <th align="left" valign="top"><strong><font size="-1">Last Modified On</font></strong></th>
                
            </tr>
          </thead>
          <tbody>
            <?php
    $wa_startindex = 0;
    while(!$district_results_private->atEnd()) {
      $wa_startindex = $district_results_private->Index;
    ?>
            <tr>
            <td>
             <input class="dataID" type="checkbox" value="<?php echo($district_results_private->getColumnVal("data_final")); ?>" id="dataID" name="dataID[]">
                </td>
    

    The Insert Record Statement with Loop:

    <?php 
    if (isset($_POST["submit"]) || isset($_POST["submit_x"])) {
    for($i=0;$i<count($_POST['dataID']);$i++){
            $userID = $_POST['userID'];
            $dataID = $_POST['dataID'][$i];
            if($userID!=='' && $dataID!==''){
        $sql="INSERT INTO subscription(dataID, userID)VALUES($dataID,$userID)";
                $stmt=$sdpc_i->prepare($sql);
                $stmt->execute();
                //echo '<div class="alert alert-success" role="alert">Submitted Successfully</div>';
            }
            else{
                 
                echo '<div class="alert alert-danger" role="alert">Error Submitting in Data</div>';
            }
        }
        echo "<script type='text/javascript'>";
            echo "alert('Subscribed successfully')";
            echo "</script>";
    }
    ?>
    

    Where do I add this code to?

    var data = table
      .rows( function ( idx, data, node ) {
        return $(node).find('input[type="checkbox"][name="chkbx"]').prop('checked');
      } )
      .data()
      .pluck('id')
      .toArray();
    

    By the way, I appreciate all the time you have taken to help me figure this out.

  • allanallan Posts: 61,446Questions: 1Answers: 10,053 Site admin

    You'd put it into a submit event handler for your form. Then use it to add all of the checkboxes you need to the document, which can then be submitted.

    Allan

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    Ok. I added this to the page:

    <script type="text/javascript" class="init">
         var data = table 
      .rows( function ( idx, data, node ) {
        return $(node).find('input[type="checkbox"][name="chkbx"]').prop('checked');
      } )
      .data()
      .pluck('id')
      .toArray();
        </script>
    

    Do I need to add anything to the form tag? Like an onsubmit? Sorry, I am a bit new to this. So far, there has no change with just adding the code to the page.

  • allanallan Posts: 61,446Questions: 1Answers: 10,053 Site admin

    Yes, as I mentioned you'd need to have that code inside a submit event handler. See the jQuery documentation for details about that.

    Without that, your code would just be running immediately. You want it to run when the form is submitted. (Speaking of which, did you make the HTML on your page valid?).

    Then, once you have the array of ids, you will need to loop over them, and add a checkbox for each to the document, so that can be included in the submit.

    Alternatively and Ajax submit would be easier - just pass the array of data to the jQuery Ajax handler. There are loads of tutorials on jQuery and Ajax around the web if you have done that before.

    Allan

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    Ok. Here is what I have now:

    <script>
    $(document).ready(function() {
    $("form").submit(function (event) {
    var data = table 
      .rows( function ( idx, data, node ) {
        return $(node).find('input[type="checkbox"][name="chkbx"]').prop('checked');
      } )
      .data()
      .pluck('id')
      .toArray();
    }
    $('#submit').on('click', function() {
    $("#submit").attr("disabled", "disabled");
    var dataID = $('#dataID').val();
    var userID = $('#userID').val();
    if(dataID!="" && userID!=""){
        $.ajax({
            url: "process2.php",
            type: "POST",
            data: {
                dataID: dataID,
                userID: userID,         
            },
            cache: false,
            success: function(dataResult){
                var dataResult = JSON.parse(dataResult);
                if(dataResult.statusCode==200){
                    $("#butsave").removeAttr("disabled");
                    $('#fupForm').find('input:text').val('');
                    $("#success").show();
                    $('#success').html('Data added successfully !');                        
                }
                else if(dataResult.statusCode==201){
                    alert("Error occured !");
                }
                
            }
        });
        }
        else{
            alert('Please fill all the field !');
        }
    });
    });
    </script>
    

    process2.php

    <?php  
    if (isset($_POST["submit"]) || isset($_POST["submit_x"])) {
    for($i=0;$i<count($_POST['dataID']);$i++){
            $userID = $_POST['userID'];
            $dataID = $_POST['dataID'][$i];
            if($userID!=='' && $dataID!==''){
        $sql="INSERT INTO subscription(dataID, userID)VALUES($dataID,$userID)";
                $stmt=$sdpc_i->prepare($sql);
                $stmt->execute();
                //echo '<div class="alert alert-success" role="alert">Submitted Successfully</div>';
            }
            else{
                
                echo '<div class="alert alert-danger" role="alert">Error Submitting in Data</div>';
            }
        }
        echo "<script type='text/javascript'>";
            echo "alert('Subscribed successfully')";
            echo "</script>";
    }
    ?>
    

    Where am I going wrong? :)

  • allanallan Posts: 61,446Questions: 1Answers: 10,053 Site admin

    I think you've got a syntax error on line 10/11? What does your browser's console say?

    Allan

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    It doesn't show any error. It submits successfully but, still only the ones on that page and ignores the checked items on the other pages.

  • allanallan Posts: 61,446Questions: 1Answers: 10,053 Site admin

    Can you link to the page showing the error? I'm absolutely certain that the script shown above will not be running - it will generate an error. Probably resulting in the original form being submitted.

    Which is a point, you want to add event.preventDefault() into your submit event handler.

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    Unfortunately, you have to login to see that page so, that won't work. There is no error, it just only inserts the one record. Here are screenshots of the before and after submission.


  • allanallan Posts: 61,446Questions: 1Answers: 10,053 Site admin

    If you can't link to your actual page, you can use JS Fiddle to create an example page.

    Did you add a prevent default? The main form shouldn't be submitting at all. It is, because you have a syntax error.

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    Here is the link to the JS fiddle. I have never set one up before so, I am pretty sure it isn't correct but you can at least see the code.

    JS Fiddle

  • allanallan Posts: 61,446Questions: 1Answers: 10,053 Site admin

    JS Fiddle can't run PHP. I'd suggest you setup a simple test case on it showing what you are trying to do. Do a "View source" on your own page and create a new test case based on that. I realise that it involved extra time and work on your part, but I'm trying to help if you want it and put the time into it.

    Allan

  • lwaters5lwaters5 Posts: 12Questions: 1Answers: 0

    Thanks. I really appreciate it. Do you know a website where I can create a test case that supports PHP?

  • allanallan Posts: 61,446Questions: 1Answers: 10,053 Site admin

    Apparently StackBlitz can, but I can't figure out how to make it happen!

    Can you not create a static HTML version of your page based on the "View source"?

    Allan

Sign In or Register to comment.