How to export 20 000 contacts from MySQL database via PHP for import into an desktop address book?

Question

Hello, I need to write a script to export contact data for printing on stickers for mailing. There could be up to 20 000 records in the database table.

The main problem is that the number of records could be so high and the site is hosted on a shared server and exporting the whole 20k records would, presumably, kill the script or stall. I the numbers were not so high i would simply export all data into a hCard file.

The solution needs to be in PHP, and the resulting file must be usable by MS Office for use to print out address stickers.

All ideas are welcome!

Answer 1

Work in batches...

1. Load 50 Addresses into $i.csv.
2. reload your site (tip: header()), repeat for around 400 times.
3. copy the 400 cvs files into one big one (even by using notepad).
4. Open e.g. with Excel.

Answer 2

20k records should be very fast to export to a CSV file. if your shared hosting is so resource-starved that it can't process 20k records in under a couple of seconds, then you've got bigger problems.

Answer 3

It depends on the host, but you can generally allow for longer script execution times by using set_time_limit. This coupled with dumping the data into a csv file is one way. As longneck has stated, 20k records should be faster than the 30 seconds usually allotted for scripts to run.

Answer 4

I'm presuming that you DO have access to MySQL server. Why not connect to MySQL directly it should save you a LOT of time. If the operation takes to long or you expect performance issues then plan it at midnight.

You can export directly to csv like this,

SELECT * INTO OUTFILE 'result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM my_table;

Load it in Word through mailmerge and of you go!

Answer 5

Code example: (I already had to do this)

$static_amount = 100; // 100 Entries to the same time
for($i = 0; $i < $mysql->count(); $i+$static_amount)
{
$toFile[$i] = $mysql->query('SELECT * FROM table WHERE id < $i AND id > $static_amount');
sleep(10); // very important!!!
}

OR

if($_COOKIE['amount'])
{
$_COOKIE['amount'] = 100;
}

$toFile = $mysql->query('SELECT * FROM table WHERE id > $_COOKIE['alreadyPerformed'] LIMIT $_COOKIE['amount']';
setCookie('amount') = 100;
setCookie('alreadyPerformed') = $_COOKIE['alreadyPerformed'] + 100;

Answer 6

Thanks for all the suggestions.

I will try them out and see what will best suit the client. I'm embarrassed to have forgotten exporting to a CSV file :) I vas stuck with the idea of exporting those contacts as a hCard formated file or xls file.