CODEWITHSUNDEEP
phpmysql
user990767
user990767

Reputation: 1019

Export mysql to txt / xml file and download it

i have a table which shows some mysql data, every entry has a checkbox to select individual entries, now i want to be able to export those selected entries into a xml or txt file, i tried this:

<?php
if ($_POST['exporttxt']) {
    for ($i = 0; $i < count($_POST['checkbox']); $i++) {
        $export_id = $checkbox[$i];

        $sql = "SELECT * FROM table WHERE id='$export_id'";
        $result = mysql_query($sql);
    }


    $output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n<root>\n";
    if ($result->num_rows > 0) {
        while ($myrow = $result->fetch_assoc())
        {
            $output .= "\t<row>\n";
            foreach ($myrow as $_name => $_value)
            {
                $output .= "\t\t<$_name>$_value</$_name>\n";
            }
            $output .= "\t</row>\n";
        }
    }
    $output .= "</root>";
}

header('content-type: text/xml');
header('content-disposition: attachment; filename=data_export.xml');
echo $output;
exit;

?>

But that didn't work at all, any hints ?

I've changed the code a bit, i am now using this

<?php
if ($_POST['exporttxt']) {
for($i=0;$i<count($_POST['checkbox']);$i++){
   $export_id = $checkbox[$i];
$text = mysql_query("SELECT code FROM ticket WHERE id='$export_id'");
$text = mysql_fetch_assoc($text);
$text = $text["code"];

ob_end_flush();
header("Content-type: text/plain");
header("Content-disposition: attachment;filename=\"filename.txt\"");
   header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header("Content-Description: File Transfer");
    header("Content-Length: ".strlen($output).";\n");



echo($text);


}
}


?>

I now get proper output on my screen, but it won't offer me to download the file ?

Upvotes: 1

Views: 6097

Answers (2)

Luke Stevenson
Luke Stevenson

Reputation: 10341

Your original code was querying each of the checkboxed IDs individually (a long and laborious way to handle it) and was trying to dump the results individually per row (not going to work well for you).

Try this. (NOTE: Untested, but should give you a good starting point to develop.)

if( $_POST['exporttxt'] ){

  if( count( $_POST['checkbox'] )>0 ){

    // If the checkbox values are meant to all be integers, you might want to perform some validation/sanitisation/filtering here
    // Up to you to do that

    // Collapse the IDs from the checkboxes into a comma-delimited string
    $export_ids = implode( ',' , $_POST['checkbox'] );

    // Template the SQL Query
    $sqlTpl = 'SELECT code FROM ticket WHERE id IN ( %s )';
    // Compile the SQL Query String
    $sqlStr = sprintf( $sqlTpl , $export_ids );

    // Execute the SQL Query
    if( !( $sqlRes = mysql_query( $sqlStr ) ) ){
      // SQL Error - Log it, Handle it
    }elseif( mysql_num_rows( $sqlRes )==0) {
      // No Rows Returned - Log it, Handle it
    }else{
      // We have results - process them
      $text = array();
      while( $r = mysql_fetch_assoc( $sqlRes ) ){
        // Looping through the returned rows, adding them to the $text array
        $text[] = $r['code'];
      }
      // Collapse the $text array down into a normal string, with one element per line
      $output = implode( "\n" , $text );

      // Output Handling from @narcisradu's answer
      header("Pragma: public");
      header("Expires: 0");
      header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
      header("Cache-Control: private",false);
      header("Content-Transfer-Encoding: binary;\n");
      header("Content-Disposition: attachment; filename=\"filename.txt\";\n");
      header("Content-Type: application/force-download");
      header("Content-Type: application/octet-stream");
      header("Content-Type: application/download");
      header("Content-Description: File Transfer");
      header("Content-Length: ".strlen($output).";\n");
      echo $output;

      die; // Prevent any further output
    }

  }else{
    // No Checkboxes Checked
    echo 'You must select one or more checkboxes to export something, muppet.';
  }

}

Upvotes: 1

Narcis Radu
Narcis Radu

Reputation: 2547

First of all:

for($i=0;$i<count($_POST['checkbox']);$i++){
$export_id = $checkbox[$i];

$sql = "SELECT * FROM table WHERE id='$export_id'";
$result = mysql_query($sql);}

This piece of code will actually do a query for every element in _POST['checkbox'] array but then you are using the result of the last query and you are not parsing the result of every query.

Please make sure the generated XML is well-formed and you actually have some data, by displaying directly into the browser.

After that you may try to force download:

    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Cache-Control: private",false);
    header("Content-Transfer-Encoding: binary;\n");
    header("Content-Disposition: attachment; filename=\"".urlencode(basename($file_name))."\";\n");
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header("Content-Description: File Transfer");
    header("Content-Length: ".strlen($output).";\n");
    ob_end_flush();

Upvotes: 0

Related Questions