Force Download CSV File

Question 1

I am trying to create a button that will force a CSV file download but for some reason I am not able to get it working. This is the code that I have:

public function export_to_csv($result) {

$shtml="";

for ($i=0; $i<count($result); $i++){

$shtml = $shtml.$result[$i]["order_number"]. "," .$result[$i]["first_name"]. "," .$result[$i]["middle_name"]. "," .$result[$i]["last_name"]. "," .$result[$i]["email"]. "," .$result[$i]["shipment_name"]. "," .$result[$i]["payment_name"]. "," .$result[$i]["created_on"]. "," .$result[$i]["modified_on"]. "," .$result[$i]["order_status"]. "," .$result[$i]["order_total"]. "," .$result[$i]["virtuemart_order_id"]. "n";

}

Header('Content-Description: File Transfer');
Header('Content-Type: application/force-download');
Header('Content-Disposition: attachment; filename=pedidos.csv');

}

The $result variable comes from here:

public function get_orders_k() {

$db=JFactory::getDBO();

    $query = " select o.order_number, o.virtuemart_order_id, o.order_total, o.order_status, o.created_on, o.modified_on, u.first_name,u.middle_name,u.last_name "
    .',u.email, pm.payment_name, vsxlang.shipment_name ' .
    $from = $this->getOrdersListQuery();

$db->setQuery($query);

$result=$db->loadAssocList();

    if ( $result > 0 )
    {
        $datos = VirtueMartModelKiala::export_to_csv($result);

    }else return 0;
}

Im not sure even where to start looking. I have look over the internet on various ways of doing this and tried all of them and still can't manage to get it working. Please help me out!

-Thanks

Question 2

Put this code below your loop.

header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="pedidos.csv"');

echo $shtml;

How did you find out the content type application/force-download? I've never heard of it. The proper MIME type for CSV is text/csv

You can find more examples how to use header function in php manual

You need also display $shtml you didn't do it in your code.

Question 3

Using fopen with w will create the file if does not exist:

$list = [
    ["Name" => "John", "Gender" => "M"],
    ["Name" => "Doe", "Gender" => "M"],
    ["Name" => "Sara", "Gender" => "F"]
];

$fp = fopen($filename, 'w');
//Write the header
fputcsv($fp, array_keys($list[0]));
//Write fields
foreach ($list as $fields) {
    fputcsv($fp, $fields);
}
fclose($fp);

If you don't like fputcsv and fopen you can use this alternative:

$list = [
    ["Name" => "John", "Gender" => "M"],
    ["Name" => "Doe", "Gender" => "M"],
    ["Name" => "Sara", "Gender" => "F"]
];

$csvArray = ["header" => implode (",", array_keys($list[0]))] + array_map(function($item) {
    return implode (",", $item);
}, $list);

file_put_contents($filename, implode ("n", $csvArray));

I hope this will help you.

Question 4

This is the way I would do this:

<?php
/*
   PDO named placeholders require that the array keys are matched
   so we have to prefix them with a colon : as in 'field' becomes ':field'
   the benefit here is the array key order is irrelevant,
   so your csv could have the headers in any order.
*/
function prefixPdoArray(array $array){
    return array_map(function($item){
        return ':'.$item;
    }, $array);
}

//PDO database driver
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$db = new PDO($dsn, $user, $password);

//header array should match DB fields
$header_map = [
    'field1',
    'field2',
    'field3',
    'field4',
];

$placeholders = prefixPdoArray($header_map);

//prepare the query outside of the loop
$stmt = $db->prepare('INSERT INTO `tran_detail` (`'.implode('`,`', $header_map).'`)VALUES('.implode(',', $placeholders).')');

/*
    we can dynamically build the query because $header_map and $placeholders
    are "canned" data, but you could just type it out as well.

    if you do the SQL manually you can dump $header_map and $placeholders
    and manually create $default_map. You could also dump this function
    prefixPdoArray() and just move the array map to $headers.
    so it would be a bit more efficient, but I thought I would show you
    a proper way to build the query dynamically.
*/

$default_map = array_fill_keys($placeholders, '');

//read the first line
$headers = fgetcsv($source, 1000, ","); 
//$header_count = count($csv_headers); //for error chcking if needed

//prefix csv headers
$headers =  prefixPdoArray($headers);

while ($data = fgetcsv($source, 1000, ",")){      
    /*
        array combine will throw an error if the header length
        is different then the data length.
        this indicates a missing or extra delimiter in the csv file.
        you may or may not have to check for this condition  
        -------------------------------------
        if( $header_count != count($data) ){ //do something on error }
    */
    //map file data to file headers
    $csv_mapped = array_combine( $headers, $data);

    //map file row to database query
    $csv_mapped = array_replace($default_map, $csv_mapped );

    //execute the query
    $stmt->execute($csv_mapped);
}

fclose($source);

Note I can only do limited testing on this ( no DB or files ), so for testing and explanation purposes here is the code for testing the basic functionality.

<?php
function prefixPdoArray(array $array){
    return array_map(function($item){
        return ':'.$item;
    }, $array);
}

//header array should match DB fields
$header_map = [
    'field1',
    'field2',
    'field3',
    'field4',
];

$placeholders = prefixPdoArray($header_map);
echo str_pad(' Placeholders ', 45, '-', STR_PAD_BOTH)."n";
var_dump($placeholders);

//prepare the query
echo "n".str_pad(' Raw SQL ', 45, '-', STR_PAD_BOTH)."n";
echo 'INSERT INTO `tran_detail` (`'.implode('`,`', $header_map).'`)VALUES('.implode(',', $placeholders).')';

$default_map = array_fill_keys($placeholders, '');
echo "nn".str_pad(' Default Map ', 45, '-', STR_PAD_BOTH)."n";
var_dump($default_map);

//(CANNED TEST DATA) read the first line
//example data for testing ( missing field1 ), and field3 out of order
$headers =  [
    'field3',
    'field2',
    'field4',
];
//prefix headers with placeholders
$headers =  prefixPdoArray($headers);

echo "n".str_pad(' CSV headers ', 45, '-', STR_PAD_BOTH)."n";
var_dump($headers);

//while ($data = fgetcsv($source, 1000, ",")){
    //(CANNED TEST DATA) read the data line(s)
    //example data for testing ( missing field1 ), and field3 out of order
    $data = [
        'value3',
        'value2',
        'value4',
    ];
    echo "n".str_pad(' CSV data ', 45, '-', STR_PAD_BOTH)."n";
    var_dump($data); 

    $csv_mapped = array_combine( $headers, $data);
    echo "n".str_pad(' CSV mapped data ', 45, '-', STR_PAD_BOTH)."n";
    var_dump($csv_mapped); 

    $csv_mapped = array_replace($default_map, $csv_mapped );
    echo "n".str_pad(' CSV filled data ', 45, '-', STR_PAD_BOTH)."n";
    var_dump($csv_mapped); 
//}

Outputs

    --------------- Placeholders ----------------
array(4) {
  [0]=>   string(7) ":field1"
  [1]=>   string(7) ":field2"
  [2]=>   string(7) ":field3"
  [3]=>   string(7) ":field4"
}

------------------ Raw SQL ------------------
INSERT INTO `tran_detail` (`field1`,`field2`,`field3`,`field4`)VALUES(:field1,:field2,:field3,:field4)

---------------- Default Map ----------------
array(4) {
  [":field1"]=>   string(0) ""
  [":field2"]=>   string(0) ""
  [":field3"]=>   string(0) ""
  [":field4"]=>   string(0) ""
}

---------------- CSV headers ----------------
array(3) {
  [0]=>   string(7) ":field3"
  [1]=>   string(7) ":field2"
  [2]=>   string(7) ":field4"
}

----------------- CSV data ------------------
array(3) {
  [0]=>   string(6) "value3"
  [1]=>   string(6) "value2"
  [2]=>   string(6) "value4"
}

-------------- CSV mapped data --------------
array(3) {
  [":field3"]=>   string(6) "value3"
  [":field2"]=>   string(6) "value2"
  [":field4"]=>   string(6) "value4"
}

-------------- CSV filled data --------------
array(4) {
  [":field1"]=>   string(0) ""
  [":field2"]=>   string(6) "value2"
  [":field3"]=>   string(6) "value3"
  [":field4"]=>   string(6) "value4"
}

You can check it out here.

http://sandbox.onlinephpfunctions.com/code/ab868ac6c6fbf43d74cf62ef2907b0c72e1f59bf

The most important part in the output is the last 2 arrays, as you can see how we map the data to the file headers, and then use the $default_map to fill in any missing columns. You can put whatever defaults in there you need, such as null or what have you, but you'll have to do it manually instead of using $default_map = array_fill_keys($placeholders, '');

This should make it pretty self explanatory, if not feel free to ask.

Hopefully I got everything matched up between them and for the DB and file stuff, if not it should be really close. But this is quite a bit of fairly complex code, so it's not inconceivable I may have missed something.

The important thing is this will let you map out the CSV data in an elegant way, and avoid any SQL Injection nastyness.

Question 5

<?php
$file = 'monkey.gif';

if (file_exists($file)) {
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename='.basename($file));
    header('Content-Transfer-Encoding: binary');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file));
    ob_clean();
    flush();
    readfile($file);
    exit;
}

?>

As you can see Content type is application/octet-steam meaning file is byte by byte encoded. Also the cache headers are set. Then headers are forcefully sent by ob_clean();flush(); and then the file is read.

The file_exists is there to ensure that given file exists. You should also try not not thrust user input as they could easy write names for your php codes and download EACH file. And with ../ in names of the files, even your documents or system files and so on.

Question 6

I discover for my answer android-Java Code __example:

        byte[] buffer = new byte[1024];         
        String url = "http://www.bla-bla.com/forcedownload.php"


            DefaultHttpClient client = new DefaultHttpClient();
            HttpGet httpGet = new HttpGet(url);
            try {
                HttpResponse execute = client.execute(httpGet);
                InputStream content = execute.getEntity().getContent();

                filesize = execute.getEntity().getContentLength();
                fileOutput = new FileOutputStream(new File(FILE_PATH, "file_copyformserver.apk"));

                 while ((len = content.read(buffer, 0, 1024)) > 0) {
                     fileOutput.write(buffer, 0, len);
                     Thread.sleep(100);
                 }

                fileOutput.close();


            } catch (Exception e) {
                e.printStackTrace();
            }

php file __example:

      $file = '/home/bla-bla/domains/bla-bla.com/file/file.apk'; //not public folder
      if (file_exists($file)) {
         header('Content-Description: File Transfer');
         header('Content-Type: application/vnd.android.package-archive');
         header('Content-Disposition: attachment; filename='.basename($file));
         header('Content-Transfer-Encoding: binary');
         header('Expires: 0');
         header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
         header('Pragma: public');
         header('Content-Length: ' . filesize($file));
         ob_clean();
         flush();
         readfile($file);
         exit;
     }

this is short code , sorry if cannot run. :)

Force Download CSV File

Answers

php,csv