Is it possible export data from table using jQuery's datatables.js plugin afrer using PHP filter button?

Question

I have created a sales-form in which the date and place of sale of products are selected:

After clicking on the "Szukaj" button, which means "search" or "filter"

the data from the database is extracted using PHP and MSQL queries. After the results of sale are displayed I want to use the jQuery DATATABLES plug-in to export the data to EXCEL, PDF, etc. The problem is that it does not work and it only copies and exports the first line

Why?here is the admin.php file in which the sale is displayed with a script at the bottom that runs datatable:

<?php

session_start();

if (!isset($_SESSION['zalogowany']))
{
    header('Location: index.php');
    exit();
}

$connect = new PDO("mysql:host=localhost;dbname=bukowski2", "root", "");

function fill_unit_select_box2($connect)
{ 
 $output = '';
 $query = "SELECT * FROM users WHERE user_id!=7";
 $statement = $connect->prepare($query);
 $statement->execute();
 $result = $statement->fetchAll();
 foreach($result as $row)
 {
  $output .= '<option  value="'.$row["user"].'">'.$row["user"].'</option>';
 }
 
 return $output;
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
        <meta charset="UTF-8">
        <script src="jquery-3.5.1.min.js" a></script>
        <script src="https://kit.fontawesome.com/b99e675b6e.js"></script>
        <script src="js2.js" async></script>
        <link rel="stylesheet" href="bootstrap.min.css" />
        <script src="jquery-ui.js" defer></script>
        <link rel="stylesheet" href="style2.css">
        <link rel="stylesheet" href="jquery-ui.css"/> 
        <link rel="stylesheet" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css">
        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.2/css/buttons.dataTables.min.css">
        <script src="buttons\datatables.min.js" defer></script>
        <script src="buttons\dataTables.buttons.min.js" defer></script>
        <script src="buttons\buttons.flash.min.js" defer></script>
        <script src="buttons\jszip.min.js" defer></script>
        <script src="buttons\pdfmake.min.js" defer></script>
        <script src="buttons\buttons.print.min.js" defer></script>
        <script src="buttons\buttons.html5.min.js" defer></script>
        <script src="buttons\vfs_fonts.js" defer></script>
        <script src="buttons\js.js" defer></script>
</head>
<body>
<?php
$_SESSION['sales_date']=date('Y-m-d');
echo '<p class="logged">Zalogowany: '.$_SESSION['user'].' | '.$_SESSION['sales_date'].' |  [ <a href="logout.php">Wyloguj się</a> ]</p>';
?>
<div class="wrapper">
    <div class="tabs">
        <ul>
            <li class="active">
                <span class="text">TABELA</span>
            </li>
            <li>
                <span class="text">DODAJ/USUŃ</span>
            </li>
            <li>
                <span class="text">3</span>
            </li>
            <li>
                <span class="text">4</span>
            </li>
        </ul>
    </div>

    <div class="content">
        <div class="tab_wrap" style="display: block;">
            <div class="title"></div>
            <div class="tab_content">
               <div class="container" style="width:100%;">    
                    <h3 align="center">Wyszukiwanie danych sprzedaży</h3><br /> <br /> <br />  
                    <div class="col-md-3" style="float:left;">  
                         <input type="text" name="date" id="date" class="form-control" placeholder="Wybierz datę" />  
                    </div>
                    <div class="col-md-3" style="float:left;">  
                         <select required name="item_from" id="item_from" class="form-control item_from"><option value="">
                         <?php echo fill_unit_select_box2($connect); ?>
                         </option></select>  
                    </div>                      
                    <div class="col-md-5" style="float:left;">  
                         <input type="button" name="filter" id="filter" value="Szukaj" class="btn btn-info" />  
                    </div>  
                    <div style="clear:both"></div>                 
                    <br />  
                    <div id="order_table">  
                         <table class="table table-bordered" id="example">  
                            <thead>
                                <tr>  
                                   <th width="30%">Nazwa i kolor produktu</th>  
                                   <th width="10%" style="text-align:center">Rozmiar</th>  
                                   <th width="10%" style="text-align:center">Cena PLN</th>  
                                   <th width="10%" style="text-align:center">KARTA PLN</th>  
                                   <th width="10%" style="text-align:center">Inna waluta1</th>  
                                   <th width="10%" style="text-align:center">Inna waluta2</th>  
                                   <th width="10%" style="text-align:center">Od</th>  
                                </tr> 
                            </thead>
                            <tbody>
                            </tbody>    
                            <tfoot>
                                <tr>  
                                   <th width="30%">Nazwa i kolor produktu</th>  
                                   <th width="10%" style="text-align:center">Rozmiar</th>  
                                   <th width="10%" style="text-align:center">Cena PLN</th>  
                                   <th width="10%" style="text-align:center">KARTA PLN</th>  
                                   <th width="10%" style="text-align:center">Inna waluta1</th>  
                                   <th width="10%" style="text-align:center">Inna waluta2</th>  
                                   <th width="10%" style="text-align:center">Od</th>  
                                </tr> 
                            </tfoot>
                         </table>  
                    </div>          
               </div>  
            </div>
        </div>
        <div class="tab_wrap" style="display: none;">
            <div class="title"></div>
            <div class="tab_content">
                <p>CONTENT 2</p>
            </div>
        </div>
        <div class="tab_wrap" style="display: none;">
            <div class="title"></div>
            <div class="tab_content">
                <p>CONTENT 3</p>
            </div>
        </div>
        <div class="tab_wrap" style="display: none;">
            <div class="title"></div>
            <div class="tab_content">
                <p>CONTENT 4</p>
            </div>
        </div>
    </div>
</div>
</body>
</html>
<script>  
      $(document).ready(function(){  
           $.datepicker.setDefaults({  
                dateFormat: 'yy-mm-dd'   
           });  
           $(function(){  
                $("#date").datepicker();  
           });  
           $('#filter').click(function(){  
                var date = $('#date').val();  
                var item_from=$('#item_from').val();
                if(date != '')  
                {  
                     $.ajax({  
                          url:"filter.php",  
                          method:"POST",  
                          data:{date:date,item_from:item_from},  
                          success:function(data)  
                          {  
                               $('#order_table').html(data);  
                          }  
                     });  
                }  
                else  
                {  
                     alert("Please Select Date");  
                }  
           });  
      });  
 </script>

and below is the filter.php file which uses PHP and a MySQL query to extract data from the database, which I want to export using the DATATABLE plug-in buttons:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <script src="jquery-3.5.1.min.js" a></script>
    <script src="https://kit.fontawesome.com/b99e675b6e.js"></script>
    <script src="js2.js" async></script>
    <link rel="stylesheet" href="bootstrap.min.css" />
    <script src="jquery-ui.js" defer></script>
    <link rel="stylesheet" href="style2.css">
    <link rel="stylesheet" href="jquery-ui.css"/> 
    <link rel="stylesheet" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.min.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.2/css/buttons.dataTables.min.css">
    <script src="buttons\datatables.min.js" defer></script>
    <script src="buttons\dataTables.buttons.min.js" defer></script>
    <script src="buttons\buttons.flash.min.js" defer></script>
    <script src="buttons\jszip.min.js" defer></script>
    <script src="buttons\pdfmake.min.js" defer></script>
    <script src="buttons\buttons.print.min.js" defer></script>
    <script src="buttons\buttons.html5.min.js" defer></script>
    <script src="buttons\vfs_fonts.js" defer></script>
    <script src="buttons\js.js" defer></script>
</head>
<body>
<?php  
 //filter.php  
    if(isset($_POST["date"]))   {  
        

        //ustanawiamy połącznie z bazą danych
        $connect = mysqli_connect("localhost", "root", "", "bukowski2");
        
    if($_POST["item_from"]==""){
$query = "SELECT item_name.item_name,sizes.sizes,sales_table.total_pln,sales_table.total_cart_payment,sales_table.total_oc1,currencies.currency,users.user FROM item_name,sales_table,sizes,currencies,users WHERE sizes.size_id=sales_table.size_id AND item_name.id=sales_table.item_name_color AND currencies.id=sales_table.total_oc2 AND users.user_id=sales_table.user_id AND sales_date='".$_POST["date"]."' ORDER BY sales_id"; 
    }else{
    $query0="SELECT user_id FROM users WHERE user='".$_POST["item_from"]."'";
        $result = mysqli_query($connect, $query0);
        while($row = mysqli_fetch_assoc($result))
            {
                $e= $row['user_id']."<br>";
            }
        $user_id = (int)$e;
    
$query = "SELECT item_name.item_name,sizes.sizes,sales_table.total_pln,sales_table.total_cart_payment,sales_table.total_oc1,currencies.currency,users.user FROM item_name,sales_table,sizes,currencies,users WHERE sizes.size_id=sales_table.size_id AND item_name.id=sales_table.item_name_color AND currencies.id=sales_table.total_oc2 AND users.user_id=sales_table.user_id AND sales_date='".$_POST["date"]."' AND sales_table.user_id=".$user_id." ORDER BY sales_id";
    }
        $output='';  
        $result = mysqli_query($connect, $query);  
        $output .= '  
           <table class="table table-bordered" id="example">  
                <thead>
                    <tr>  
                       <th width="30%">Nazwa i kolor produktu</th>  
                       <th width="10%" style="text-align: center">Rozmiar</th>  
                       <th width="10%" style="text-align: center">Cena PLN</th>  
                       <th width="10%" style="text-align: center">KARTA PLN</th>
                       <th width="10%" style="text-align: center">Inna waluta1</th>
                       <th width="10%" style="text-align: center">Inna waluta2</th>
                       <th width="10%" style="text-align: center">Od</th> 
                    </tr>
                </thead>
        ';  
        if(mysqli_num_rows($result) > 0)  
        {  
           while($row = mysqli_fetch_array($result))  
           {  
                $output .= '  
                     <tbody>
                         <tr>  
                              <td>'. $row["item_name"] .'</td>  
                              <td style="text-align: center">'. $row["sizes"] .'</td>  
                              <td style="text-align: center">'. $row["total_pln"] .'</td>  
                              <td style="text-align: center">'. $row["total_cart_payment"] .'</td>  
                              <td style="text-align: center">'. $row["total_oc1"] .'</td>  
                              <td style="text-align: center">'. $row["currency"] .'</td>                          
                              <td style="text-align: center">'. $row["user"] .'</td>                          
                         </tr> 
                    </tbody> 
                ';  
           }  
        }  
        else  
        {  
           $output .= '  
                <tr>  
                     <td colspan="5">Brak danych</td>  
                </tr>  
           ';  
        }  
        $output .= '</table>';  
        echo $output;
        //zamykamy połączenie z bazą danych 
        mysqli_close($connect);
    } 
 ?>
</body>

The js code to initialize the table has been included in the buttons\js.js script file. Here is the code:

$(document).ready(function() {
    $('#example').DataTable( {
        dom: 'Bfrtip',
        buttons: [
            'copy',
            'csv',
            'excel',
            'pdf',
            'print'
        ]
    } );
} );

Where is your DataTables object? I don't see it in the code shown in the question... I was expecting to see something like `$('#example').DataTable( {...} )`, with your DataTables export buttons defined there. (I do see all the related DataTables button imports - but they are not used anywhere). — andrewJames, Aug 23 '20 at 14:44
**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 23 '20 at 15:49
Not because the only data that is entered is the date selection from the datapicker and the user selection from the HTML — Olaf, Aug 24 '20 at 07:56

score 0 · Answer 1 · answered Aug 24 '20 at 08:22

0

ok i solved. It was enough to remove the tag from the loop that printed the results because the tag was inserted each time a row of data was inserted. Closed

answered Aug 24 '20 at 08:22

Olaf

27
4

Is it possible export data from table using jQuery's datatables.js plugin afrer using PHP filter button?

1 Answers1