PHP Exporting excel table with mysql

[lolzyro]

Hi everyone!

I am working with this php script to generate a table in Excel.

In short, I select certain entries in the table with the help of some checkboxes and with this php script it generates an excel for me with the selected rows from the table.

The code works, but it doesn’t generate a single table with multiple rows, it generates one table for each row, repeatedly generates the header table.

I have attached a picture with the table generated in excel https://i.stack.imgur.com/Br6w7.png

Code:

<?php
session_start();
include('db.php');
$user_number = $_SESSION['user_nr'];
            if(isset($_POST['but_delete']))  
            {
                    
                            if(isset($_POST['but_delete'])){

                            if(isset($_POST['delete'])){
                            foreach($_POST['delete'] as $deleteid){
                                
                                $output = '';
                                
                                $sql4 = "SELECT * from echipamente WHERE id='$deleteid' AND user_nr='$user_number'";
                                $results = $conn->query($sql4);
                                if ($results->num_rows > 0) {
                                    $filename = 'excel';
                                    $output .= '
   <table class="table" border="1">
   <tr>
   <th style="bg-color:e0e0e0;" colspan="5">Interventii</th>
   </tr>
       <tr style="bg-color:f3f3f3;">  
       <th>Tip echipament</th>  
                         <th>Serie</th>  
                         <th>Tichet utilizat</th>  
       <th>Tehnician</th>
       <th>Status</th>
                    </tr>
  ';
                                    
                                    while($row = $results->fetch_assoc()) 
            
                                    {
                                        $output .= '
    <tr>  
                         <td>'.$row["tip"].'</td>  
                         <td>'.$row["serie"].'</td>  
                         <td>'.$row["tichet"].'</td>  
                         <td>'.$row["user_nr"].'</td>  
                         <td>'.$row["status"].'</td> </tr>
                    
   ';
                                    }
                                    $output .= '<tr><th colspan="5"></th></tr></table>';
  header('Content-Type: application/xls');
  header('Content-Disposition: attachment; filename='.$filename.'.xls');    
                                echo $output;   
                                }
                                else
                                {
                                    echo 'results0';
                                }
                                
            }
                            }
                            }
            }
            
            ?>

[phdr]

@lolzyro

repeatedly generates the header

Wouldn’t you need to build the header markup, once, before the start of any looping?

You would also execute the header() statements and echo the output, once, after the end of all the logic building the output.

In general, you should not execute SELECT queries inside of loops. This is inefficient due to the php/database communications involved for each query. You can use a single query to do this, by either using an IN() or a FIND_IN_SET() clause in the WHERE term, to match all the rows at once. You should also not put external, unknown, dynamic values directly into an sql query statement. Use a prepared query instead.

In the existing code, for a query that will match at most one row, don’t loop to fetch the single row, just directly fetch it.