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

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 t开发者_Go百科o be in PHP, and the resulting file must be usable by MS Office for use to print out address stickers.

All ideas are welcome!

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!

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.

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.

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.

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;