View Full Version : MySQL to Excel not working for large queries

09-19-2006, 11:46 PM
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


09-19-2006, 11:47 PM
How many rows are returned? Excel has a max of like 64000.
What's the script timeout on the server?

09-19-2006, 11:59 PM
the server script timeout is 60sec which should be more than enough time. I dont think im reaching the 64000 limit either.

09-20-2006, 12:01 AM
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.


09-20-2006, 03:30 AM
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?

09-20-2006, 09:19 AM
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?

09-20-2006, 09:36 AM
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)

09-20-2006, 05:47 PM


$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");


$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";
$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");

$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,


09-20-2006, 05:59 PM
if i can find some time this evening, then i'll try it out.

09-20-2006, 07:30 PM
Thanks Raf, i app it.


09-22-2006, 07:27 PM
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.