Convert Mysql data to Excel in php

Question

can anyone help me with this crunch: Specifically, when I open Excel File I get this error (from image) in translation (The file format and file extension of 'Senderliste_IPTV.xls' do not match. Maybe the file is stuffed or not secure. You should not open them if you do not trust their source. Would you like to open the file?), and when I click on OK I open it all properly.

This is my code:

Index.php

<?php
include('db_con.php');
$stmt=$db_con->prepare('select * from zusatzpakete');
$stmt->execute();
?>
<html>
<head>
<title>Senderliste</title>
 <meta charset="UTF-8">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-1.10.2.js"></script>
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
<div class="container">
  <div class="panel">
    <div class="panel-heading">
      <h3 class="sender" style="text-align: center;">Senderliste</h3>
        <div></div>
         <span>Senderlisten</span>
        </a>
      <div class="panel-body">
        <table border="0"  class="table table-bordered table-striped slider-table">
                    <thead>
                    <tr>
                        <th class="col-xs-6">Zusatzpakete</th>
                        <th class="col-xs-6">Preis</th>
                            <th class="col-xs-3">Auswählen:</th>
                        </tr>
                    </thead>
                        <form action="export.php" method="post">
                        <tbody>
                    <tr>
                        <td>Family</td>
                            <td>CHF 16.-/ Monat</td>
                            <td><input type="checkbox"  name="zpaket[]" id="toggle-1" value="1">
                            <label for="toggle-1"></label></td>
                        </tr>
                        <tr>
                        <td>Doku</td>
                            <td>CHF 9.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]"  id="toggle-2" value="2">
                            <label for="toggle-2"></label></td>
                        </tr>
                        <tr>
                        <td>TV Club One</td>
                            <td>CHF 9.-/ Monat</td>
                            <td><input type="checkbox"  name="zpaket[]"  id="toggle-3" value="3">
                            <label for="toggle-3"></label></td>
                        </tr>
                        <tr>
                        <td>3-er Paket Family/Doku/TV Club One</td>
                            <td>CHF 25.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-4" value="4" >
                            <label for="toggle-4"></label></td>
                        </tr>
                        <tr>
                        <td>Man's TV One</td>
                            <td>CHF 27.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-5" value="5" >
                            <label for="toggle-5"></label></td>
                        </tr>
                        <tr>
                        <td>Man's TV Two</td>
                            <td>CHF 19.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-6" value="6" >
                            <label for="toggle-6"></label></td>
                        </tr>
                        <tr>
                        <td>Brasilianisch</td>
                            <td>CHF 35.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-7" value="7" >
                            <label for="toggle-7"></label></td>
                        </tr>
                        <tr>
                        <td>Sport One</td>
                            <td>CHF 9.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-8" value="8" >
                            <label for="toggle-8"></label></td>
                        </tr>
                        <tr>
                        <td>Musik TV</td>
                            <td>CHF 3.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-9" value="9" >
                            <label for="toggle-9"></label></td>
                        </tr>
                        <tr>
                        <td>Albanisch</td>
                            <td>CHF 21.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-10" value="10" >
                            <label for="toggle-10"></label></td>
                        </tr>
                        <tr>
                        <td>Bosnisch</td>
                            <td>CHF 16.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-11" value="11" >
                            <label for="toggle-11"></label></td>
                        </tr>
                        <tr>
                        <td>Nat. Geographic</td>
                            <td>CHF 3.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-12" value="12" >
                            <label for="toggle-12"></label></td>
                        </tr>
                        <tr>
                        <td>Pink Plus</td>
                            <td>CHF 27.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-13" value="13" >
                            <label for="toggle-13"></label></td>
                        </tr>
                        <tr>
                        <td>Türkisch</td>
                            <td>CHF 3.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-14" value="14" >
                            <label for="toggle-14"></label></td>
                        </tr>
                        <tr>
                        <td>Portugisisch</td>
                            <td>CHF 6.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-15" value="15" >
                            <label for="toggle-15"></label></td>
                        </tr>
                        <tr>
                        <td>Sport & Fun Paket</td>
                            <td>CHF 20.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-16"  value="17" >
                            <label for="toggle-16"></label></td>
                        </tr>
                        <tr>
                        <td>My Sports Pro Paket</td>
                            <td>CHF 25.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-17" value="18" >
                            <label for="toggle-17"></label></td>
                        </tr>
                        <tr>
                        <td>Equidia LIVE</td>
                            <td>CHF 12.-/ Monat</td>
                            <td><input type="checkbox" name="zpaket[]" id="toggle-18" value="19" >
                            <label for="toggle-18"></label></td>    
                        </tr>
                        <tr class="noBorder">
                        <td><p class="checkboxStatus"></p></td>
                        <td></td>
                        <td><input type="submit" id="buttoncheck" name="submit" value=""
                         style="float: right; margin-right: 21px;"/></td>   
                    </tbody>
                    </form>
            </table>    
            <!-- <a href="export.php" target="_blank">
                <img src="images/icon_excel.png" border="0" title="XLS Export">
                </a> -->
      </div>
    </div>
  </div>
</div>
<script>
var chk1 = $("input[type='checkbox'][value='4']");
var chk2 = $("input[type='checkbox'][value='1']");
var chk3 = $("input[type='checkbox'][value='2']");
var chk4 = $("input[type='checkbox'][value='3']");

chk1.on('change', function(){
    chk2.prop('checked',this.checked);
        chk3.prop('checked',this.checked);
        chk4.prop('checked',this.checked);
});


$(function(){
   $("#buttoncheck").click(function(){
        if($('[type="checkbox"]').is(":checked")){
            $('.checkboxStatus').html(" ");
      return true;
        }else{
            $('.checkboxStatus').html("Bitte wählen Sie ein Zusatzpaket aus.");
         }
         return false;
   })

});
</script>
</body>
</html>

export.php

<?php
include('db_con.php');


$setSql = "SELECT `ID`,`HD`,`Sender`, `7 Tage Replay`, `Sprachkuerzel` FROM `programme_iptv`";
$setRec = mysqli_query($conn,$setSql);

if(isset($_POST['submit']))
{
  if(!empty($_POST['zpaket'])) {
 //  var_dump($_POST['zpaket']); exit;
    $value = implode(",",$_POST['zpaket']);
       if($value==17)
        {
        //$stmt=$db_con->prepare('SELECT ID, HD, Sender, 7TageReplay, Zuzatzpakete, Sprachkuerzel FROM programme_iptv WHERE ZP_ID IN (0,6) OR ID IN (83,90,89,85,86,87,37,38,39) OR ZP_ID IN (0,'.$value.')');
        $stmt=$db_con->prepare('SELECT @rownum :=@rownum+1 as num, HD, Sender, 7TageReplay, Zuzatzpakete, Sprachkuerzel FROM programme_iptv s, (SELECT @rownum := 0) r WHERE ZP_ID IN (0,6) OR ID IN (83,90,89,85,86,87,37,38,39) OR ZP_ID IN (0,'.$value.')'); 
        }
       else if($value==18)
       {
       // $stmt=$db_con->prepare('SELECT ID, HD, Sender, 7TageReplay, Zuzatzpakete, Sprachkuerzel FROM programme_iptv WHERE ZP_ID IN (0,8,19,'.$value.')');       
        $stmt=$db_con->prepare('SELECT @rownum :=@rownum+1 as num, HD, Sender, 7TageReplay, Zuzatzpakete, Sprachkuerzel FROM programme_iptv s, (SELECT @rownum := 0) r WHERE ZP_ID IN (0,8,19,'.$value.')');       
        }
       else{
        $stmt=$db_con->prepare('SELECT @rownum :=@rownum+1 as num, HD, Sender, 7TageReplay, Zuzatzpakete, Sprachkuerzel FROM programme_iptv s, (SELECT @rownum := 0) r WHERE ZP_ID IN (0,'.$value.')');
        } 
        }
       else{
        header('refresh: 0; url=index.php');
      }
}
$stmt->execute();

$columnHeader = "Nr."."\t"."HD"."\t"."Programm"."\t"."Replay 7 Tage"."\t"."Zusatzpakete"."\t"."Sprache"."\t";


$setData='';

while($rec =$stmt->FETCH(PDO::FETCH_ASSOC))
{
  $rowData = '';
  foreach($rec as $value)
  {
    $value = '"' . $value . '"' . "\t";
    $rowData .= $value;
  }
  $setData .= trim($rowData)."\n";
}

header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=Senderliste_IPTV.xls");
header("Pragma: no-cache");
header("Expires: 0");


echo ucwords($columnHeader)."\n".$setData."\n";

 ?>

This is my base:

-- phpMyAdmin SQL Dump
-- version 4.8.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Erstellungszeit: 21. Nov 2018 um 08:29
-- Server-Version: 10.1.36-MariaDB
-- PHP-Version: 5.6.38

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Datenbank: `testing`
--

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `programme_iptv`
--

CREATE TABLE `programme_iptv` (
  `ID` int(10) NOT NULL,
  `HD` varchar(20) NOT NULL,
  `Sender` varchar(100) CHARACTER SET utf8 COLLATE utf8_german2_ci NOT NULL,
  `7TageReplay` varchar(10) NOT NULL,
  `Zuzatzpakete` varchar(20) NOT NULL,
  `Sprachkuerzel` varchar(20) NOT NULL,
  `ZP_ID` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `programme_iptv`
--

INSERT INTO `programme_iptv` (`ID`, `HD`, `Sender`, `7TageReplay`, `Zuzatzpakete`, `Sprachkuerzel`, `ZP_ID`) VALUES
(1, 'HD', ' Leutv', '7', '', 'DE', 0),
(2, 'HD', 'SRF 1', '7', '', 'DE', 0),
(3, 'HD', 'SRF 2', '7', '', 'DE', 0),
(4, 'HD', 'SRF Info', '7', '', 'DE', 0);

-- --------------------------------------------------------

--
-- Tabellenstruktur für Tabelle `zusatzpakete`
--

CREATE TABLE `zusatzpakete` (
  `ZP_ID` int(10) NOT NULL,
  `Zusatzpakete` varchar(50) NOT NULL,
  `Preis` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Daten für Tabelle `zusatzpakete`
--

INSERT INTO `zusatzpakete` (`ZP_ID`, `Zusatzpakete`, `Preis`) VALUES
(1, 'Family', 'CHF 16.-/ Monat'),
(2, 'Doku', 'CHF 9.-/ Monat'),
(3, 'TV Club One', 'CHF 9.-/ Monat'),
(4, '3-er Paket Family/Doku/TV Club One', 'CHF 25.-/ Monat'),
(5, 'Man\'s TV One', 'CHF 27.-/ Monat'),
(6, 'Man\'s TV Two', 'CHF 19.-/ Monat'),
(7, 'Brasilianisch', 'CHF 35.-/ Monat'),
(8, 'Sport One', 'CHF 9.-/ Monat'),
(9, 'Musik TV', 'CHF 3.-/ Monat'),
(10, 'Albanisch', 'CHF 21.-/ Monat'),
(11, 'Bosnisch', 'CHF 16.-/ Monat'),
(12, 'Nat. Geographic', 'CHF 3.-/ Monat'),
(13, 'Pink Plus', 'CHF 27.-/ Monat'),
(14, 'Türkisch', 'CHF 3.-/ Monat'),
(15, 'Portugisisch', 'CHF 6.-/ Monat'),
(16, 'Sport & Fun Paket', 'CHF 20.-/ Monat'),
(17, 'My Sports Pro Paket', 'CHF 25.-/ Monat'),
(18, 'Equidia LIVE', 'CHF 12.-/ Monat');
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

This is my entire code, can someone look and see why this error is showing me. Thank you all for your help.

You are creating a CSV (character separated values), not an XLS. Change your content-type to `text/csv` and you should be fine. That said, it is better to actually generate an XLSX file. There are decent tools out there that can do that for you (e.g. PHPExcel). Note that CSV is not a single standard and may break in strange ways when changing anything, most notable locale. — Bart Friederichs, Nov 21 '18 at 07:40
@BartFriederichs I set this up header('Content-type: text/csv');, but I still get the same error, but I would like to have data in xls not csv if possible — Mara, Nov 21 '18 at 07:49
It is possible to write out XLS, but you will need to use a library for that. — Bart Friederichs, Nov 21 '18 at 07:51
@BartFriederichs how to use the library, can you help me a bit on my case? — Mara, Nov 21 '18 at 08:12
Helping you out on the library is out of scope and off-topic here. Go out and research some (Google is your friend). If you run into a specific problem, feel free to ask on StackOverflow again. I have had decent results with PHPExcel (which apparently is PHPSpreadsheet now). — Bart Friederichs, Nov 21 '18 at 08:14

Convert Mysql data to Excel in php

0 Answers0