Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Trouble with Query Export to Excel

    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.
    PHP Code:
        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 Code:
    <?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?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Simply use the MySQL function ifnull() 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()

  • #3
    Regular Coder
    Join Date
    Apr 2006
    Posts
    311
    Thanks
    17
    Thanked 0 Times in 0 Posts
    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.

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Well you're not alone I too wrote my own CSV creator before I found that danged function.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •