CSV export problem

[biggieuk]

Hi all,

 

I am using the following script to export a list of bookings from a mysql database:

 

<?php require_once('../Connections/hwbltaconf.php'); 
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=SessionList.csv");
header("Pragma: no-cache");
header("Expires: 0");


mysql_select_db($database_hwbltaconf, $hwbltaconf);
$query = "SELECT title, ampm, capacity, placesbooked FROM sessions";
$exp = mysql_query($query);
$count = mysql_num_fields($exp);
for ($i = 0; $i < $count; $i++) {
$header .= mysql_field_name($exp, $i).",";
}
while($row = mysql_fetch_row($exp)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . ",";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
print "$header\n$data";
exit;

?> 

 

this works fine when all fields in the middle are present, however if a 'year' value is missing the next columns value is put into this instead of leaving it blank.

 

The output looks like this (when there is a value missing):

 

username   |        email          |      year   |    course      |  selection

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

Test User        [email protected]     Course 1           99            

 

Any idea how i can have the specific column blank if the db value is blank?

 

thanks.  

 

[gevans]

<?php require_once('../Connections/hwbltaconf.php'); 
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=SessionList.csv");
header("Pragma: no-cache");
header("Expires: 0");


mysql_select_db($database_hwbltaconf, $hwbltaconf);
$query = "SELECT title, ampm, capacity, placesbooked FROM sessions";
$exp = mysql_query($query);
$count = mysql_num_fields($exp);
for ($i = 0; $i < $count; $i++) {
$header .= mysql_field_name($exp, $i).",";
}
while($row = mysql_fetch_row($exp)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = ",\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . ",";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
print "$header\n$data";
exit;

?>

 

I think that should work, just a quick look!!

[biggieuk]

Thanks gevans,

 

That seemed to work for the middle columns, however previously the 'selection' column would span over a number of extra columns as the values stored in there are seperated by commas. e.g.  (12,34,65,87,89) - This would span over 5 columns.

 

Is there a way to replace these commas with a new row?

 

thanks again

[gevans]

Can you give me an example of what's going wrong with it now?

[biggieuk]

At the moment its producing this:

 

 

instead of:

 

 

 

Thanks.

[gevans]

Can you post the csv file that gets produced?

[premiso]

The problem is in the foreach.

 

If you know which row number it is being returned I would do this:

 

<?php
///etccccc
while($row = mysql_fetch_row($exp)) {
$line = '';

$cnt = count($row);
for ($i=4; $i>$cnt; $i++) {
     $selections[] = $row[$i];
     unset($row[$i]);
}
$selection = implode(", ", $selections);
$row[4] = $selection;

foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = ",\t";
} else {
$value = str_replace('"', '""', $value);

 

And a side note, indenting is your friend!