Why very slow to export the data into excel?

software development

#1

Hi Sir

I have a problem to export the database table into a excel. I’m using php code to export the data. here is my codes show below…

[php]
//Start session
session_start();

//EDIT YOUR MySQL Connection Info:
$con = mysql_connect(“localhost”,“username”,“mypassword”);
if (!$con)
{
die('Could not connect: ’ .mysql_error());
}
mysql_select_db(“motherdb”, $con);

            //your MySQL Table Name

//bridgit_d2e_cpgn, $DB_DBName, may also be commented out & passed to the browser
//as parameters in a query string, so that this code may be easily reused for
//any MySQL table or any MySQL database on your server

//DEFINE SQL QUERY:
//edit this to suit your needs
mysql_query(“CREATE INDEX tab1 ON bridgit_d2e_cpgn (coy_id,CompanyName,Newname,IDD,MainLine,FaxNumber,Address_1,Address_2,Address_3,City,State,PostalCode,Country,Employees,Website,EmailConvention,Revenue_USD,PCNumber,ServerNumber,Created_by,Date_Created,Updated_bys,Updated_Dates,Company_Status,Sic_Code,officetype,remarks,username)”);
mysql_query(“CREATE INDEX tab2 ON bridgit_d2e_cpgns_con (con_id,Salutation,FirstName,MiddleName,LastName,Jobtitle,Email,DirectNumer,mobile,Executive_Status,remarks,Updated_by,Updated_Date,TDM,BDM,I_T,Sales,mktg,Operation,fin,admin,hr)”);
$sql=“SELECT
bridgit_d2e_cpgn.coy_id,
bridgit_d2e_cpgn.CompanyName,
bridgit_d2e_cpgn.Newname,
bridgit_d2e_cpgn.IDD,
bridgit_d2e_cpgn.MainLine,
bridgit_d2e_cpgn.FaxNumber,
bridgit_d2e_cpgn.Address_1,
bridgit_d2e_cpgn.Address_2,
bridgit_d2e_cpgn.Address_3,
bridgit_d2e_cpgn.City,
bridgit_d2e_cpgn.State,
bridgit_d2e_cpgn.PostalCode,
bridgit_d2e_cpgn.Country,
bridgit_d2e_cpgn.Employees,
bridgit_d2e_cpgn.Employees1,
bridgit_d2e_cpgn.Website,
bridgit_d2e_cpgn.EmailConvention,
bridgit_d2e_cpgn.Revenue_USD,
bridgit_d2e_cpgn.PCNumber,
bridgit_d2e_cpgn.ServerNumber,
bridgit_d2e_cpgn.Created_by,
bridgit_d2e_cpgn.Date_Created,
bridgit_d2e_cpgn.Updated_bys,
bridgit_d2e_cpgn.Updated_Dates,
bridgit_d2e_cpgn.Company_Status,
bridgit_d2e_cpgn.Sic_Code,
bridgit_d2e_cpgn.officetype,
bridgit_d2e_cpgn.remarks,
bridgit_d2e_cpgn.username,
bridgit_d2e_cpgns_con.con_id,
bridgit_d2e_cpgns_con.Salutation,
bridgit_d2e_cpgns_con.FirstName,
bridgit_d2e_cpgns_con.MiddleName,
bridgit_d2e_cpgns_con.LastName,
bridgit_d2e_cpgns_con.JobTitle,
bridgit_d2e_cpgns_con.Email,
bridgit_d2e_cpgns_con.DirectNumer,
bridgit_d2e_cpgns_con.mobile,
bridgit_d2e_cpgns_con.Executive_Status,
bridgit_d2e_cpgns_con.remarks,
bridgit_d2e_cpgns_con.Updated_by,
bridgit_d2e_cpgns_con.Updated_Date,
bridgit_d2e_cpgns_con.TDM,
bridgit_d2e_cpgns_con.BDM,
bridgit_d2e_cpgns_con.I_T,
bridgit_d2e_cpgns_con.Sales,
bridgit_d2e_cpgns_con.mktg,
bridgit_d2e_cpgns_con.Operation,
bridgit_d2e_cpgns_con.fin,
bridgit_d2e_cpgns_con.admin,
bridgit_d2e_cpgns_con.hr
FROM
bridgit_d2e_cpgn
LEFT JOIN bridgit_d2e_cpgns_con ON bridgit_d2e_cpgn.coy_id = bridgit_d2e_cpgns_con.company_tbl_coy_id”;

//Optional: print out title to top of Excel or Word file with Timestamp
//for when file was generated:
//set $Use_Titel = 1 to generate title, 0 not to use title
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = DATE(‘m-d-Y H:i’);
//define title for .doc or .xls file: EDIT this if you want
$title = “bridgit_d2e_cpgns_con on $now_date”;
/*

Leave the connection info below as it is:
just edit the above.

(Editing of code past this point recommended only for advanced users.)
*/
//create MySQL connection
$result = mysql_query($sql);

//if this parameter is included ($w=1), file returned will be in word format (’.doc’)
//if parameter is not included, file returned will be in excel format (’.xls’)
IF (ISSET($w) && ($w==1))
{
$file_type = “msword”;
$file_ending = “doc”;
}ELSE {
$file_type = “vnd.ms-excel”;
$file_ending = “xls”;
}
//header info for browser: determines file type (’.doc’ or ‘.xls’)
HEADER(“Content-Type: application/$file_type”);
HEADER(“Content-Disposition: attachment; filename=bridgit_d2e_cpgns_con.$file_ending”);
HEADER(“Pragma: no-cache”);
HEADER(“Expires: 0”);

/* Start of Formatting for Word or Excel */

IF (ISSET($w) && ($w==1)) //check for $w again
{
/* FORMATTING FOR WORD DOCUMENTS (’.doc’) */
//create title with timestamp:
IF ($Use_Title == 1)
{
ECHO("$title\n\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = “\n”; //new line character

 WHILE($row = MYSQL_FETCH_ROW($result))
 {
     //set_time_limit(60); // HaRa
     $schema_insert = "";
     FOR($j=0; $j<mysql_num_fields($result);$j++)
     {
     //define field names
     $field_name = MYSQL_FIELD_NAME($result,$j);
     //will show name of fields
     $schema_insert .= "$field_name:\t";
         IF(!ISSET($row[$j])) {
             $schema_insert .= "NULL".$sep;
             }
         ELSEIF ($row[$j] != "") {
             $schema_insert .= "$row[$j]".$sep;
             }
         ELSE {
             $schema_insert .= "".$sep;
             }
     }
     $schema_insert = STR_REPLACE($sep."$", "", $schema_insert);
     $schema_insert .= "\t";
     PRINT(TRIM($schema_insert));
     //end of each mysql row
     //creates line to separate data from each MySQL table row
     PRINT "\n----------------------------------------------------\n";
 }

}ELSE{
/* FORMATTING FOR EXCEL DOCUMENTS (’.xls’) */
//create title with timestamp:
IF ($Use_Title == 1)
{
ECHO("$title\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = “\t”; //tabbed character

 //start of printing column names as names of MySQL fields
 FOR ($i = 0; $i < MYSQL_NUM_FIELDS($result); $i++)
 {
     ECHO MYSQL_FIELD_NAME($result,$i) . "\t";
 }
 PRINT("\n");
 //end of printing column names

 //start while loop to get data
 WHILE($row = MYSQL_FETCH_ROW($result))
 {
     //set_time_limit(60); // HaRa
     $schema_insert = "";
     FOR($j=0; $j<mysql_num_fields($result);$j++)
     {
         IF(!ISSET($row[$j]))
             $schema_insert .= "".$sep;
         ELSEIF ($row[$j] != "")
             $schema_insert .= "$row[$j]".$sep;
         ELSE
             $schema_insert .= "".$sep;
     }
     $schema_insert = STR_REPLACE($sep."$", "", $schema_insert);
     //following fix suggested by Josue (thanks, Josue!)
     //this corrects output in excel when table fields contain \n or \r
     //these two characters are now replaced with a space
     $schema_insert = PREG_REPLACE("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
     $schema_insert .= "\t";
     PRINT(TRIM($schema_insert));
     PRINT "\n";
 }

}

[/php]

In a first table I have 2,864 records in my database table. the type is MyISAM and size of table 545.3 KB. the name of table is bridgit_d2e_cpgn. In second table I have 7,046 records in my database table the type is MyISAM also and the size 922.3 KB. the table name is bridgit_d2e_cpgns_con. my problem is not proceed to download the data. the process to generate the date will be stop is not working. what should i do in this situation? can you help me?

Many Thanks
Jed


#2

You’re more likely to get a response if you can narrow the problem down to the specific lines that cause the problem. Unless you can make a small example that someone can reproduce, you can’t really expect anyone to look through your code, envision your database and execution environment, and find the problem unless, of course, you are paying them to do so. Most people who help find bugs do so as a challenge or opportunity to learn something, but it fails to fit those criteria if you can’t narrow the problem down to something that can be reproduced independent of your specific situation…