php fwrite to xls file blank cells not showing up

Question

I'm trying to write data that I've gotten from an SQL query to a tab-delimited text file. But there is a problem that if there is a blank cell it doesn't register. and just writes the next text in the blank cell.

database data:

id    text    2ndText
1     1t1     2t1
2             2t2
3     1t3     2t3

excel output:

id    text            2ndText
1     1t1             2t1
2     2t2
3     1t3             2t3

the code we're using is this:

while ($row=mysql_fetch_row($Recordset1)) 
{ 
    $line=''; 
    $fc=0;
    for ($i=0;$i<$fields;$i++)
    {
        if (substr(strtoupper(mysql_field_name($Recordset1,$i)),0,7)!="DBSTACK")
        {
            if (strtoupper($_POST['checkbox'.$fc])==strtoupper(mysql_field_name($Recordset1,$i)))
            {   
                $value=$row[$i];
                if ((!isset($value)) | ($value == "")) 
                { 
                    $value = "\t"; 
                } else 
                { 
                    $value=str_replace('"','""',$value); 
                    $value='"'.$value.'"'."\t"; 
                } 
                $line.=$value;
            }
            $fc++;
        }
    } 
    $data.=trim($line)."\n"; 
} 
$data=str_replace("\r","",$data);
$TargetFileName="temp/".session_id().time().".xls";
$target=fopen($TargetFileName,"wb");
$XLData=$header."\n".$data;
fwrite($target,$XLData);
fclose($target);

where $Recordset1 is the result of the query

In my search for a solution this came out as a possible solution, but this is a frequently used piece of code so i don't want it to change too much. So is there a simpler solution?

Changing the value to na\t if the cell is empty, could be a solution but that's kind of dirty.

Any ideas?

Answer 1

I'm not going to touch your database code with a ten-foot pole. You're using a database API that's been deprecated for years; alternatives have been available for a decade now. Use them.

So my first suggestion would be to use a proper database API that returns an array of rows that are indexed by column name. Much easier to loop through.

I'd also suggest that your database query only select the columns you need instead of SELECT *.

If you want to write a CSV file (technically a TSV, but that's fine) you should use the built-in function fputcsv() that will save you having to roll your own CSV writer, and allow you to work with arrays instead of strings.

You should also save it with a csv extension to avoid errors when trying to open it in Excel.

But, as to the problem you asked about? You're not writing a value when there's an empty field, just a tab separator. How about:

if ((!isset($value)) | ($value == "")) { 
    $value = "\"\"\t"; 
}

PS $value will always be set, because you're setting it in the line above.