Excell export from mysql

[bolfescu]

<?PHP

include('config.php');


  function cleanData(&$str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  }

  // filename for download
  $filename = "Piante_" . date('Ymd') . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header("Content-Type: application/vnd.ms-excel");

  $flag = false;
  $result = mysql_query("SELECT * FROM photo where flag='active'") or die('Query failed!');
  while(false !== ($row =mysql_fetch_assoc($result))) {
    if(!$flag) {
      // display field/column names as first row

      echo implode("\t", array_keys($row)) . "\r\n";
      $flag = true;
    }
    array_walk($row, 'cleanData');
    echo implode("\t", array_values($row)) . "\r\n";
  }
  exit;
?>

Hy i'm using this code to export excell from the db, i'm having problem that when i open the file excell show me an error something like different format that specified by the file name open anyway?, its work anyway, next problem is that i whant to customize he collum name, i see that array_keys, but i'm not able to change it i've tryied to make only echo with \r\n with the name of columns but nothing...

[almostbob]

The above code outputs comma separated values (tab separated values)
the .csv file is saved as .xls
(.xls contain way more headers etc than just the data)
excell reads .csv so the function works, but there is an informational warning about the file type
If you save the file as .csv the warning will cease, and still open in excell

[jj.dcruz]

Try this codes. its not on .xlxs but on .csv

$result = mysql_query("SELECT * FROM photo where flag='active'")
if (!$result) die('Couldn\'t fetch records'); 
$num_fields = mysql_num_fields($result); 
$headers = array(); 
for ($i = 0; $i < $num_fields; $i++) 
{     
       $headers[] = mysql_field_name($result , $i); 
} 
$fp = fopen('php://output', 'w'); 
if ($fp && $result) 
{     
       header('Content-Type: text/csv');
       header('Content-Disposition: attachment; filename="export.csv"');
       header('Pragma: no-cache');    
       header('Expires: 0');
       fputcsv($fp, $headers); 
       while ($row = mysql_fetch_row($result)) 
       {
          fputcsv($fp, array_values($row)); 
       }
die; 
} 

[almostbob]

Its not necessary to change 99% functional code,
If you do use the above, paste the sanitising lines in

or Just change the filename and application-type

14$filename = "Piante_" . date('Ymd') . ".csv";
17header("Content-Type: text/csv");

[bolfescu]

thank you all almostbob i've tryied your way its ok, but how can i customize the collum name?not to show the real collums in database?
and the different between xls and csv its that the xls it make me one row every collum, instead of csv that its shows me everithing on a row...

[bolfescu]

done it with array keys, but now i whant to show a photo from a collum from db how can i do?

[bolfescu]

  function cleanData(&$str)
  {
    $str = preg_replace("/\t/", "\\t", $str);
    $str = preg_replace("/\r?\n/", "\\n", $str);
    if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"';
  }

  // filename for download
  $filename = "Piante_" . date('Ymd') . ".xls";

  header("Content-Disposition: attachment; filename=\"$filename\"");
  header( "Content-type: application/vnd.ms-excel; charset=UTF-16LE" );
 header( "Content-type: text/html; charset=UTF-8" );
  $flag = false;
  $result = mysql_query("SELECT * FROM photo");
  while(false !== ($row =mysql_fetch_assoc($result))) {
    if(!$flag) {
      // display field/column names as first row

      echo implode("\t", array_keys($coloane)) . "\r\n";
      $flag = true;
    }

    array_walk($row, 'cleanData');
   echo "", $row["id"] . "\t";
    echo "", $row["photo"] . "\t";
    echo "", $row["prodoto"] . "\t";
    echo "", $row["piante_pianali"] . "\t";
    echo "", $row["piante_cc"] . "\t";
    echo "", $row["l_a"] . "\t";
    echo "", $row["l_c"] . "\t";
    echo "", $row["l_ing"] . "\t\n";


  }
  exit;
?>

how can i make something like <a href=""$row['photo']"">link?</a>

[bolfescu]

problem solved with phpexcell....