Insert data from db into csv file with php pdo

Question

I'm trying to insert data that I select from my database into a csv file. I got stuck here and don't know why it doesn't work. It keeps me giving a file like this:

For some reason it put the column names into 1 field (A1) I'm using this code now:

<?php
include "includes/connection.php";

if(isset($_POST['submit'])){

$tabel = $_POST['tabel'];
$date1 = $_POST['date1'];
$date2 = $_POST['date2'];

      header('Content-Type: text/csv; charset=utf-8');  
      header('Content-Disposition: attachment; filename="data.csv";');  
      $output = fopen("php://output", "w");  
      fputcsv($output, array('Dev_ID', 'Barcode', 'Naam', 'Ip_adres', 'Merk', 'Model', 'CPU', 'Memory', 'Moederbord', 'Serialnummer', 'Aanschaf_dat', 'Aanschaf_waarde', 'Opmerkingen', 'Picture_dev'));  
      $sql = "SELECT * FROM ".$tabel." WHERE Aanschaf_dat BETWEEN ".$date1." AND ".$date2."";  
      $query = $conn->prepare($sql); 
      while($row = $query->fetch(PDO::FETCH_ASSOC))  
      {  
           fputcsv($output, $row);  
      }  
      fclose($output);      
}
?>

Does anyone know what I'm doing wrong?

Add [error reporting](http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php/845025#845025) to the top of your file(s) _while testing_ right after your opening PHP tag for example ` — RiggsFolly, Jun 19 '18 at 09:32
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) **properly** in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Jun 19 '18 at 09:33
@RiggsFolly Yes I tried and thanks for your help but at the moment this aint the problem of my question — Ende, Jun 19 '18 at 09:34
You do know that `$variables` will get expanded automatically in a double quoted string literal dont you? — RiggsFolly, Jun 19 '18 at 09:36
And that `."";` is completely unnecessary at the end of a string literal — RiggsFolly, Jun 19 '18 at 09:37
Its a SQL Injection Nightmare but try `$sql = "SELECT * FROM $tabel WHERE Aanschaf_dat BETWEEN '$date1' AND '$date2'";` — RiggsFolly, Jun 19 '18 at 09:38
And that it achieves nothing `preparing` a statement like this with no parameters in it — RiggsFolly, Jun 19 '18 at 09:39
I had dates wrapped in single quotes but didnt help anything, even with your new sql query the csv file will stay empty. And I know it is widely open to sql injection but It doesn't matter here — Ende, Jun 19 '18 at 09:40
What format are the dates entered by the user in? They would have to be `YYYY-MM-DD` for the query to work — RiggsFolly, Jun 19 '18 at 09:42
Do you have data in the table for the date range specified by the user — RiggsFolly, Jun 19 '18 at 09:44
Echo out the query, then run it in phpMyAdmin or whatever tool you use to run queries — RiggsFolly, Jun 19 '18 at 09:44
@RiggsFolly sql query gives me result https://imgur.com/a/iooeD5e I'm not sure but I don't think that would be the problem — Ende, Jun 19 '18 at 09:47
@RiggsFolly I did some changes and it outputs now the data from the db put it puts everything in the A field instead of taking a new field every time — Ende, Jun 19 '18 at 10:16

score 1 · Answer 1 · answered Jun 19 '18 at 09:40

Check below code and notice change in SQL statement. Also need to do data sanitization to prevent SQL injection,

<?php
include "includes/connection.php";

if(isset($_POST['submit'])){

$tabel = $_POST['tabel'];
$date1 = $_POST['date1'];
$date2 = $_POST['date2'];

header('Content-Type: text/csv; charset=utf-8');  
header('Content-Disposition: attachment; filename="data.csv";');  
$output = fopen("php://output", "w");  
fputcsv($output, array('Dev_ID', 'Barcode', 'Naam', 'Ip_adres', 'Merk', 'Model', 'CPU', 'Memory', 'Moederbord', 'Serialnummer', 'Aanschaf_dat', 'Aanschaf_waarde', 'Opmerkingen', 'Picture_dev'));  
$sql = "SELECT * FROM $tabel  WHERE Aanschaf_dat BETWEEN '$date1' AND '$date2'";    
$query = $conn->prepare($sql); 
while($row = $query->fetch(PDO::FETCH_ASSOC))  
{  
fputcsv($output, $row);  
}  
fclose($output);      
}
?>

This was already mentioned in the comments but thanks for you answer. — Ende, Jun 19 '18 at 09:48

Insert data from db into csv file with php pdo

1 Answers1