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
    Jul 2004
    Posts
    158
    Thanks
    4
    Thanked 0 Times in 0 Posts

    PHP, MySQL and Excel

    I've been using the below code to grab data from a MySQL database and export it into an Excel xls file. The problem I now am tasked with is that there are two columns at the end of the file that are obsolete to the enduser however are needed to complete the initial query to MySQL (guest_pass.HostingMember and directory.id). I'd like to remove them from the xls on the fly but I can't figure out how to do it.

    The code:

    PHP Code:
    $select "SELECT guest_pass.FirstName AS 'First Name', guest_pass.LastName AS 'Last Name', guest_pass.Address AS 'Street Address', guest_pass.City AS 'City', guest_pass.State AS 'State', guest_pass.Zip AS 'Zip Code', guest_pass.Phone AS 'Phone', guest_pass.Email AS 'Email', guest_pass.ReasonVisit AS 'Reason for Visit', guest_pass.date_full AS 'Date and Time', CONCAT(directory.LastName,', ' , directory.MenFirstName, ' ', directory.WomenFirstName) AS 'Hosting Member', guest_pass.HostingMember, directory.id FROM guest_pass LEFT JOIN directory ON directory.id = guest_pass.HostingMember ORDER BY `date_full` DESC";
            
    $export mysql_query($select); 
            
    $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)) OR ($value == "")) { 
                        
    $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/x-msdownload"); 
            
    header("Content-Disposition: attachment; filename=guest-passes.xls"); 
            
    header("Pragma: no-cache"); 
            
    header("Expires: 0"); 
            print 
    "$header\n$data"

  • #2
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    Quote Originally Posted by keith1995 View Post
    The problem I now am tasked with is that there are two columns at the end of the file that are obsolete to the enduser however are needed to complete the initial query to MySQL (guest_pass.HostingMember and directory.id).
    why do you need these columns to complete the query?
    Last edited by angst; 02-15-2008 at 08:01 PM.

  • #3
    Banned
    Join Date
    Feb 2008
    Location
    Winnipeg, Canada
    Posts
    396
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Just because you use a column in a JOIN doesn't mean you need to include it in your SELECT.

  • #4
    Regular Coder
    Join Date
    Jul 2004
    Posts
    158
    Thanks
    4
    Thanked 0 Times in 0 Posts
    angst and Andrew,

    Thanks! I was always under the assumption that columns in a JOIN were needed in the SELECT. I've removed them and all works perfectly!

    Thanks for both of your quick responses


  •  

    Posting Permissions

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