Hi! I have a php code that generates an excel file that is populated with mysql data of course through a mysql_query.. Here it is..



header("Content-type: application/ms-excel");
header("Content-Disposition: attachment; filename=Reservation Summary_sortbydate.xls");
header("Pragma: no-cache");
header("Expires: 0");

$head1="Ayala Aviation Corporation";
$head3="For the period ___________";


$query = "select bdate as 'Date', cliename as 'Client', grpcode as 'Group Code', bperson as 'Contact', reservno as 'Reservation No.', acode as 'Aircraft', fdate as 'Flight Date', itinerary as 'Itinerary', etd as 'ETD', eta as 'ETA', pname as 'Passengers', status as 'Status', cutoff as 'Confirmation Cut-off', adminid as 'Reserved by' from reservation order by bdate LIMIT 15";

$result = mysql_query($query);
if($result) {
    $count = mysql_num_rows($result);
   for($i=0; $i<$count; $i++) {
   for ($i = 0; $i < mysql_num_fields($result); $i++) 
    $schema_insert_rows.=mysql_field_name($result,$i) . "\t"; 
        while($row = mysql_fetch_row($result)) {
            $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";
    print mb_convert_encoding("$heads\n$schema_insert_rows\n$data", 'UTF-16LE', 'UTF-8');
} else die(mysql_error());


This works perfectly but as you can see, I have put a limit on my query.. LIMIT 15. Why? Because if I don't, the downloading of the excel file will take long and if it is finally downloaded on my computer, the file will display an error: Maximum execution time of 60 seconds exceeded. I know what causes this error. My mysql table currently has 27 rows with 24 columns (though this data lessens with the query I have) and I think it can't fetch all that data that's why I get that error. But I really need to get those (many) data as it is required with what I should do. Do you have any ideas to allow the program to fetch as many data as it can? And also, how can I put the $heads="$head1\n$head2\n$head3\n"; in center without downloading any PHPExcel writer etc? Please help me. Thank you.

Recommended Answers

All 2 Replies

You can use set_time_limit(0); at the top of your script.

The problem in your code is that you have three nested loops... are they really necessary? Lines 21+22 actually do the same thing as line 28. So you are trying to fetch 27 * (24 columns and 27 rows).

@pritaes, I already tried set_time_limit(0); but it still take long to download the file and also I got a new error once the excel file got downloaded: Fatal Error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 133693422) on line 26. This is the line 26: $schema_insert_rows.=mysql_field_name($result,$i) . "\t";. About the nested loops, yes, they are necessary. I don't get what I need if I modify them. I have to fetch as many data as I can. Please help me. Thank you!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.