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 11 of 11
  1. #1
    New Coder
    Join Date
    Sep 2006
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL to Excel not working for large queries

    Hey everyone, I am a frequent reader of these forums, but this is my first post, so bear with me.
    The problem I'm having, in the nutshell, is that I have a PHP script that takes a MySQL query, and dumps it to a properly formatted Excel (xls) file. When the query is reasonably small, it works like a dream. However, when I do a SELECT * basically, the file is still created, but its empty. Any ideas of what could be causing this behavior?

    P.S. I have made sure that php.ini has more than enough accessible memory to do this.

    Thanks for any help in Advance

    Q

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    How many rows are returned? Excel has a max of like 64000.
    What's the script timeout on the server?

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    New Coder
    Join Date
    Sep 2006
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    the server script timeout is 60sec which should be more than enough time. I dont think im reaching the 64000 limit either.

  • #4
    New Coder
    Join Date
    Sep 2006
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Also, i should add, that I've done the implementation of the script several different ways, but I always get down to this exact behavior. Thats why I think something else is going on that is outside my expertise. Thanks again.

    Q

  • #5
    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
    Do you have error or warning displays suppressed? There may be an error occuring that you aren't seeing. How many rows does that big query return?

  • #6
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    Excel has a limit of 65535 rows ((2^16) -1)
    But I doubt thats your problem here - at what point in your script do you open the file for writing? Before or after the query?
    If its before the query and it remains empty - first see if you can execute the query without the file write and see how long that takes
    Another thing to look at is are you writing 1 line at a time or storing the whole thing in a variable and writing it out to the file at the end?
    How long does the script take to run?
    Do you have any output in to debug?

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    welcome here quakerstate79 !

    we need to see this PHP code in order to help you.

    to be honest: i don't think you're on the right track here, because, as i frequently say, such large db-opreations shouldn't be executed over the webserver. it would make a lott more sense to just dump the table in a csv and open that in excell. or to use your db-front to make the export to excell. or to return the select as an html-table and dump that in a file with an xls extension. all of these will probably be more efficient and easier then your current script (althoug we haven't seen it --> post it and you'll get better help)
    Last edited by raf; 09-20-2006 at 09:39 AM.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #8
    New Coder
    Join Date
    Sep 2006
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PHP Code:
    <?php 


    ini_set
    ("memory_limit","202M");           
    ini_set("time_limit","5555");
    ini_set("post_max_size","201M");
    ini_set("upload_max_filesize","200M");

    $database "some_DB";
    $db_server "some_Server";
    $db_user "some_User";
    $db_password "some_passwd";
      
      
    $connection mysql_connect($db_server$db_user$db_password) or
         die(
    "Unable to connect to database");
           
    mysql_select_db($database$connection) or
           die(
    "Unable to select database");

    mysql_select_db($database);       


    $query str_replace(" quote "'"'$_GET["output"]);

    $export mysql_query($query);
    $fields mysql_num_fields($export); 
    $header =  ""
    for (
    $i 0$i $fields$i++)
         { 
            
    $header .= mysql_field_name($export$i) . "\t"
        } 
    $data "";
    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";
                             

    printf($data);

    header("Content-type: application/x-msdownload");


    $time time();
    $mydate date("Y-m-d",$time);
    $filename $mydate 'excelout.xls';

    header("Content-Disposition: attachment; filename=" $filename);
    header("Pragma: no-cache"); 
    header("Expires: 0"); 
    print 
    "$header\n$data";  
    ?>
    here's the script, have at it. The most rows I have ever tried to return was 1346, but the script will fail at about 100. If you guys have any ideas about where to put some debugging outs, I'm all ears.

    Thanks so much for your generous gift of your time and knowledge,

    Q
    Last edited by guelphdad; 09-20-2006 at 09:21 PM. Reason: added php tags

  • #9
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    if i can find some time this evening, then i'll try it out.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #10
    New Coder
    Join Date
    Sep 2006
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Raf, i app it.

    Q

  • #11
    New Coder
    Join Date
    Sep 2006
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I thought i would post the solution that i found to work. Basically I cache the file server-side then use the header stuff to send it to the user. Now it works perfectly. Thanks for the help guys.

    Q


  •  

    Posting Permissions

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