Passing of data from main page to php query inside a modal

Question

I have a dataTable that passes it's row to a modal. Will it be possible to pass it directly to the php page using the same modal script?

This is my main_page.php

<table id="example1" class="table table-bordered">
                <thead>
                  <th>Reference No</th>
                  <th>Finger Scan No</th>
                  <th>Date From</th>
                  <th>Date To </th>
                    <th>Tools </th>
                </thead>
                <tbody>
<?php
$user = $user['fingerscanno'];
$sql = "
SELECT 
payroll.payrollno AS payrollno,
payroll.referenceno AS referenceno,
payroll.fingerscanno AS fingerscanno,
payroll.datefrom AS datefrom,
payroll.dateto AS dateto,
USERINFO.USERID,
USERINFO.BADGENUMBER

FROM 

payroll,
USERINFO

WHERE
USERINFO.BADGENUMBER = payroll.fingerscanno AND
payroll.fingerscanno='$user'
";
                    $query = sqlsrv_query($conn, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
                    while($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)){
                      echo "
                        <tr>
                        <td>".$row['referenceno']."</td>
                          <td>".$row['fingerscanno']."</td>
                          <td>".$row['datefrom']."</td>
                          <td>".$row['dateto']."</td>
                          <td>
                            <button class='btn btn-success btn-sm edit btn-flat' data-id='".$row['referenceno']."'><i class='fa fa-edit'></i> Proof of Attendance</button>
                            <button class='btn btn-danger btn-sm delete btn-flat' data-id='".$row['referenceno']."'><i class='fa fa-edit'></i> Payslip Summary</button>
                          </td>

                        </tr>
                      ";
                    }
                  ?>
                </tbody>
              </table>

<?php include 'includes/mymodal.php'; ?>

This is the modal function

$(function(){
  $("body").on('click', '.edit', function (e){
    e.preventDefault();
    $('#edit').modal('show');
    var id = $(this).data('id');
    getRow(id);
  });

This is the modal page

mymodal.php

<div class="modal fade" id="edit">
<input type="hidden" class="decid" id="id" name="id">
<table id="example2" class="table table-bordered">
<thead>
<th>Schedule Date</th>
<th>Schedule Name</th>
<th>Recorded In</th>
<th>Recorded Out</th>
<th>Day Count</th>
<th>Day Value</th>
<th>N.D. Value</th>
<th>Leave Count</th>
<th>R.H. Count</th>
<th>R.H. Value</th>
</thead>
<tbody>
<?php 
$sql = "SELECT fingerscanno, scheduledate, schedulename, recordin, recordout, noofdays, rate, nightdifferential, leaveday, regularholiday, specialholiday, referenceno
FROM payrollrecords WHERE fingerscanno='$user' and referenceno='$id'";

                    $query = sqlsrv_query($conn, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
                    while($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)){

 echo "
                        <tr>
                          <td>".$row['scheduledate']."</td>
                          <td>".$row['schedulename']."</td>
                          <td>".$row['recordin']."</td>
                          <td>".$row['recordout']."</td>
                          <td>".$row['noofdays']."</td>
                          <td>".$row['rate']."</td>
                          <td>".$row['nightdifferential']."</td>
                          <td>".$row['leaveday']."</td>
                          <td>".$row['regularholiday']."</td>
                          <td>".$row['specialholiday']."</td>
                        </tr>
                      ";
}

                  ?>
                  </tbody>
              </table>
</div>

My question is, how will I pass this into the table? So that the variable referenceno='$id' will receive the value from the main page.

Answer 1

You need to use AJAX.

Ajax is a javascript methodology that allows you to exchange information with a back-end PHP file, just as you are attempting to do.

The AJAX code block will send data to the mymodal.php file, the mymodal.php file will do the MySQL lookup and create the HTML, then echo a string variable (which could be a json object or it could be the HTML that you built in your while loop) back to the main page. The AJAX code block will receive the data echo'd out from the PHP file inside the .done() function and, also in that function, you can modify the DOM to inject the new data. To the user, it will look like they clicked on an element with class edit and the data just appeared in the modal.

Note that you do not include the mymodal.php file in your main_file.php page, because the AJAX code block knows how to communicate with that file.

You will need to add the HTML structure for the modal to the bottom of your main page (note that it is initially set to display:none):

<style>
    #lamodal{display:none;position:fixed;width:100vw;height:100vh;background:black;opacity:0.8;}
        #mdl_inner{width:60%;height:40%;}
    .myflex{display:flex;align-items:center;justify-content:center;}
</style>
<div id="lamodal" class="myflex">
    <div id="mdl_inner"></div>
</div><!-- #lamodal -->

Your javascript (AJAX) will look something like this:

$(function(){
    $("body").on('click', '.edit', function (e){
        e.preventDefault();
        var id = $(this).data('id');
        $.ajax({
            type: 'post',
             url: 'mymodal.php',
            data: 'userid=id'
        }).done(function(d){
            //console.log('d: '+d);
            $('#mdl_inner').html(d);
            $('#lamodal').show();
        });
    });
});

Your mymodal.php file would be changed to look like this:

<?php 
    $sql = "SELECT fingerscanno, scheduledate, schedulename, recordin, recordout, noofdays, rate, nightdifferential, leaveday, regularholiday, specialholiday, referenceno
    FROM payrollrecords WHERE fingerscanno='$user' and referenceno='$id'";
    $query = sqlsrv_query($conn, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));

    $out = '
        <table id="example2" class="table table-bordered">
            <thead>
                <th>Schedule Date</th>
                <th>Schedule Name</th>
                <th>Recorded In</th>
                <th>Recorded Out</th>
                <th>Day Count</th>
                <th>Day Value</th>
                <th>N.D. Value</th>
                <th>Leave Count</th>
                <th>R.H. Count</th>
                <th>R.H. Value</th>
            </thead>
            <tbody>
    ';

    while($row = sqlsrv_fetch_array($query, SQLSRV_FETCH_ASSOC)){
        $out .= '
            <tr>
              <td>".$row['scheduledate']."</td>
              <td>".$row['schedulename']."</td>
              <td>".$row['recordin']."</td>
              <td>".$row['recordout']."</td>
              <td>".$row['noofdays']."</td>
              <td>".$row['rate']."</td>
              <td>".$row['nightdifferential']."</td>
              <td>".$row['leaveday']."</td>
              <td>".$row['regularholiday']."</td>
              <td>".$row['specialholiday']."</td>
            </tr>
        ';
    }

    $out .= '
            </tbody>
        </table>
    ';

    echo $out;
?>

Note how we are constructing a string variable and building it through concatination. When done, just echo $out and the newly-constructed HTML will appear in the .done() function of your AJAX code block.

See these additional AJAX examples and explanations:

http://www.jayblanchard.net/basics_of_jquery_ajax.html

Simple Like/Unlike text button - adding ajax etc

pass var to bootstrap modal where php will use this value