2

I have a form to do some filtration which works fine. In my form i can only filter record and to generate the filtered results into excel(only the filtered record).Here i have to add date range filter and If the user selects only from_date and to_date then I need to generate all the records between those dates. If he/she wants to generate column wise or another column wise then I should be able to generate reports customized that way also. I can only filter records with these condition but I'm not able to write the logic to create the SQL query for report generation. Thank You!

Conncection:

<?php
/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "db_name";

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

?>

Responsive Code:

<?php
    //include connection file 
    include_once("connection.php");

    // initilize all variable
    $params = $columns = $totalRecords = $data = array();

    $params = $_REQUEST;

    //define index of column
    $columns = array( 
        0 =>'pid',
        1 =>'product_name', 
        2 => 'product_price',
        3 => 'added_date'
    );

    $where = $sqlTot = $sqlRec = "";

    // getting total number records without any search
    $sql = "SELECT pid,product_name,product_price,added_datek FROM `products` ";
    $sqlTot .= $sql;
    $sqlRec .= $sql;


    $sqlRec .=  " ORDER BY added_date";

    $queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));


    $totalRecords = mysqli_num_rows($queryTot);

    $queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch employees data");

    //iterate on results row and create new index array of data
    while( $row = mysqli_fetch_row($queryRecords) ) { 
        $data[] = $row;
    }   

    $json_data = array(
            "draw"            => 1,   
            "recordsTotal"    => intval( $totalRecords ),  
            "recordsFiltered" => intval($totalRecords),
            "data"            => $data   // total data array
            );

    echo json_encode($json_data);  // send data as json format
?>

I have a form to do some filtration which works fine. In my form i can only filter record and to generate the filtered results into excel(only the filtered record).Here i have to add date range filter and If the user selects only from_date and to_date then I need to generate all the records between those dates. If he/she wants to generate column wise or another column wise then I should be able to generate reports customized that way also. I can only filter records with these condition but I'm not able to write the logic to create the SQL query for report generation. Thank You!

HTML Content Code:

    <html>
    <head>

    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.css"/>

    <script type="text/javascript" src="https://cdn.datatables.net/r/dt/jq-2.1.4,jszip-2.5.0,pdfmake-0.1.18,dt-1.10.9,af-2.0.0,b-1.0.3,b-colvis-1.0.3,b-html5-1.0.3,b-print-1.0.3,se-1.0.1/datatables.min.js"></script>


        <div class="container" style="padding:20px;20px;">
          <div class="">
            <h1>Data Table with Export features Using PHP server-side</h1>
            <div class="">
            <table id="employee_grid" class="display" width="100%" cellspacing="0">
            <thead>
                <tr>
                    <th>PID</th>
                    <th>Product Name</th>
                    <th>Price</th>
                    <th>Purchase Date</th>
                </tr>
            </thead>

            <tfoot>
                <tr>
                   <th>PID</th>
                    <th>Product Name</th>
                    <th>Price</th>
                    <th>Purchase Date</th>

                </tr>
            </tfoot>
        </table>
        </div>
          </div>

        </div>

    <script type="text/javascript">
    $( document ).ready(function() {
    $('#employee_grid').DataTable({
             "processing": true,
             "sAjaxSource":"response.php",
             "dom": 'lBfrtip',
             "buttons": [
                {
                    extend: 'collection',
                    text: 'Export',
                    buttons: [
                        'copy',
                        'excel',
                        'csv',
                        'pdf',
                        'print'
                    ]
                }
            ]
            });
    });
    </script>
MONEYMSN
  • 69
  • 9

0 Answers0