...

View Full Version : Trouble with Query Export to Excel



fuzzy1
01-26-2007, 03:14 PM
Hey All,

I've been days trying to work out a suitable script to export to csv (Excel)
fighting Quotes (single & double) and bullets contained in text fields along the way. A dozen odd aborted scripts and this one is finally close enough to consider.
Unfortunately however, if the first column of result is NULL, the filed is ignored resulting in all successive data being mis-aligned from their respective field headings.

I believe it comes down to the following snippet.
if ( ( !isset( $value ) ) || ( $value == '' ) ) // 1
{
$value = "\t";
}
else
{
$value = str_replace( '"' , '""' , $value );
$value = '"' . $value . '"' . "\t";
}
for which I have tried variations of ...
// if ( ( !isset( $value ) ) || ( $value == '' ) ) // 1
// if ((is_null($value)) || ( $value == '' )) // 2
// if (( !isset( $value ) ) || ( $value == '' ) || (is_null($value)))// 3

None of which work.
Here's the sript in it's entirety
<?php
if (isset($_POST['submit'])) { // Handle the form.
require_once ('mysql_connect.php'); // Connect to the db.
mysql_select_db ('bike_shop');

// DB Connection here

$select = "SELECT
c.ProductNo, c.Product, c.SKU, c.OEM, c.ProductType, c.Price,
c.Taxable, c.Quantity, c.Unit, c.Weight, c.Length, c.Height,
c.Width, c.ContainerCode, c.Brief, c.Description, c.Attribute1Label,
c.Colors, c.Attribute2Label, c.Sizes, c.Thumbnail, c.Photo, c.Template,
c.Featured, c.Active, c.Isbn, c.Mpn, c.Upc, c.Media, c.NaturalSearchKeywords,
c.NaturalSearchDescription, c.Category, c.Keywords
FROM bike_shop.catalog c
WHERE c.Active='Yes'
";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
$header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
$line = '';
foreach( $row as $value )
{

// if ( ( !isset( $value ) ) || ( $value == '' ) ) // 1
// if ((is_null($value)) || ( $value == '' )) // 2
// if (( !isset( $value ) ) || ( $value == '' ) || (is_null($value)))// 3

if ( ( !isset( $value ) ) || ( $value == '' ) ) // 1
{
$value = "\t";
}
else
{
$value = str_replace( '"' , '""' , $value );
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
$data = "\n(0) Records Found!\n";
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=walb_export.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";


exit;

mysql_close(); // Close the database connection.

}
?> PLEASE, How can I fix so first column is inserted when NULL?:confused:

Fumigator
01-26-2007, 06:16 PM
Simply use the MySQL function ifnull() (http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html) when selecting that column.

Although, I have to ask, why would you be manually building a CSV-formatted file when PHP has a function do to it all for you?

fputcsv() (http://us2.php.net/manual/en/function.fputcsv.php)

fuzzy1
01-26-2007, 06:30 PM
Well Geeezz?
That's a good question indeed!
I guess the answer is that in days of searching variations on "mysql, export to csv" it has never come up?
Didn't know such a function existed. Might have saved me a ton of time and aggravation. That's what I get for trying to figure this stuff out for myself before posting in the forums in desperation. I'll check it out.

Fumigator
01-26-2007, 06:34 PM
Well you're not alone :) I too wrote my own CSV creator before I found that danged function.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum