Insert Data from Input Values With Pagination

lwaters5 · December 2022

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

<?php >Paying Member<?php } // ("".$_GET['state'] ."" == "NH" or "".$_GET['state'] ."" == "MA" or "".$_GET['state'] ."" == "RI") > District Name Company Name Resource Name Progress Last Modified On <?php $wa_startindex = 0; while(!$district_results_private->atEnd()) { $wa_startindex = $district_results_private->Index; > getColumnVal("data_final")); ?>" id="dataID" name="dataID[]"> ``` ---------------------------------------- ?>

Any help would be appreciated. Thanks

kthorngren · 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

lwaters5 · December 2022

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

kthorngren · December 2022

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

lwaters5 · December 2022

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 '

Submitted Successfully

';
}
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>";

}

<?php > ``` ?>

allan · December 2022

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

lwaters5 · December 2022

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.

allan · December 2022

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

Allan

lwaters5 · December 2022

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.

kthorngren · 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

lwaters5 · December 2022

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

<?php
>Paying Member<?php
  } // ("".$_GET['state']  ."" == "NH" or "".$_GET['state']  ."" == "MA" or "".$_GET['state']  ."" == "RI")
>
        District Name
        Company Name
          Resource Name
           Progress
           Last Modified On
            
        
      
      
        <?php
$wa_startindex = 0;
while(!$district_results_private->atEnd()) {
  $wa_startindex = $district_results_private->Index;
>
        
        
         getColumnVal("data_final")); ?>" id="dataID" name="dataID[]">
            
```
?>


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 '

Submitted Successfully

';
}
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>";

}

<?php > ``` ?>

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.

allan · December 2022

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

lwaters5 · December 2022

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.

allan · December 2022

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

lwaters5 · December 2022

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 '

Submitted Successfully

';
}
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>";

}

<?php > ``` ?>

Where am I going wrong?

allan · December 2022

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

Allan

lwaters5 · December 2022

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.

allan · December 2022

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.

lwaters5 · December 2022

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.

allan · December 2022

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.

lwaters5 · December 2022

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

allan · December 2022

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

lwaters5 · December 2022

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

allan · December 2022

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

Insert Data from Input Values With Pagination

Insert Data from Input Values With Pagination

Answers

Howdy, Stranger!

Categories

DataTables

Insert Data from Input Values With Pagination

Insert Data from Input Values With Pagination

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables