CODEWITHSUNDEEP
phpmysqlexcelimport
Mohsan Azad
Mohsan Azad

Reputation: 1

Export large amount of data (20000 rows) from Mysql to excel (with Php)

I want to export large amount of data (20000 rows) from Mysql to excel (with PHP) data comes from two tables. Each record of first table have the ID of second table record. I am using PHPExcel Library.

Pseudocode:

$first_table = mysql_query("SELECT * FROM table_1")

foreach($first_table as $row){

   $store_id = $row['store_id'];

   $second_table =  mysql_query("SELECT * FROM table_2 Where ID = $store_id");

}

In this case mysql query runs number of time (record found in first table) now problem is that it takes too long time for processing and at the end no result php screen becomes blank(white)...... I think this is execution timeout problem...

Any solution ??

Upvotes: 0

Views: 3680

Answers (1)

peterm
peterm

Reputation: 92805

IMHO 20k rows isn't so big. How about utilizing JOIN and get a complete resultset in one roundtrip to MySql

SELECT t1.*, t2.*
  FROM table1 t1 JOIN table2 t2
    ON t1.store_id = t2.store_id

Optimize select portion of the query so that you return only columns that you really need.

It's not clear from your question, but If you need just to export data that can be later opened in Excel and not to build a fancy report then you can use INTO OUTFILE clause to generate CSV file very fast

SELECT t1.*, t2.*
  INTO OUTFILE '/tmp/tablename.csv'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
  FROM table1 t1 JOIN table2 t2
    ON t1.store_id = t2.store_id

Upvotes: 2

Related Questions