Mail Merge from mysql database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dreeves
Forum Commoner
Posts: 39
Joined: Thu Oct 22, 2009 8:53 am

Mail Merge from mysql database

Post by dreeves »

I would like to perform the simple task of taking data from a MySQL database and filling in a doc/rtf template. I can not use COM and this is pretty new to me. I've scoured the Internet and the explanations don't make enough sense or just haven't worked for me. I will be posting my entire script every time in case there are simple errors I am making. This has been bothering me for a few weeks now.

For each record in my table, I would like the users information filled into a letter. The script should loop through these records and create a separate letter for each. They do not need to be separate documents, and I will use .doc or .rtf, which ever is easier. I'm not sure if I need to use fopen(), fwrite(), file_get_contents, which headers to use...the list goes on. Please help, this is driving me crazy.
I'm not sure if any of this is correct, but it is what I have been playing around with:

Code: Select all

<? 
$template = "form_letter.rtf";
$handle = fopen($ourFileName, 'r+') or die("can't open file");
$FileData = file_get_contents($template);

$FileDataReplace = str_replace(%%FIRST%%,David, $FileData);

fwrite($handle, $FileDataReplace);
fclose($handle);
rename ("form_letter.rtf", "../hr/complete.rtf") or die ("Could not move file");


header('Content-type: application/octet-stream');
header('Content-Disposition: attachment; filename="complete.rtf"');
readfile('hr/complete.rtf');
?>
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Mail Merge from mysql database

Post by requinix »

Actually that's looking pretty good. I'd make a couple changes though:

Code: Select all

<?php
$template = "form_letter.rtf";
//$handle = fopen($ourFileName, 'r+') or die("can't open file");
$FileData = file_get_contents($template);

$FileDataReplace = str_replace("%%FIRST%%", "David", $FileData);

//fwrite($handle, $FileDataReplace);
//fclose($handle);
//rename ("form_letter.rtf", "../hr/complete.rtf") or die ("Could not move file");


header('Content-type: application/octet-stream');
header('Content-Disposition: attachment; filename="complete.rtf"');
//readfile('hr/complete.rtf');
echo $FileDataReplace;
?>
The commented lines can be removed: I left them in so you can see what changed.
  1. Try hard to use <?php tags. Those are supported everywhere while short tags (<?) aren't.
  2. It looks like you're just giving the file to the user. Unless you need a copy saved on the server you can skip the $ourFileName stuff. Do you need an actual file?
  3. %%FIRST%% and David are strings. As such they need quotes around them. Without quotes PHP will try to interpret them as code. Which they aren't. Which will crash your script.
Without COM I suggest RTF: it's closer to plain text than DOC.

A script can only send one file at a time (for download). You have a few options:
  1. Use one giant RTF file for all the letters. This may be easier for the user but will require a bit of research on your part.
  2. Use multiple RTF files. The user will have to download each one separately.
  3. Use multiple RTF files but all contained in a ZIP file. This will require a more complex script.
Now questions:
- Are you trying to create all the letters at once? Or just one letter at a time?
- As I asked earlier, do you need to save the letter on the server or just give it to the user?
- What version of PHP are you using?
dreeves
Forum Commoner
Posts: 39
Joined: Thu Oct 22, 2009 8:53 am

Re: Mail Merge from mysql database

Post by dreeves »

Thank you so much! I am excited that this is beginning to work.
Now questions:
- Are you trying to create all the letters at once? Or just one letter at a time?
- As I asked earlier, do you need to save the letter on the server or just give it to the user?
- What version of PHP are you using?
Yes, I am trying to create all the letters at once. I might need about 60 letters and would rather have it done on one single click.

I do not need to save the letter on the server, I just want to give it to the user.

I am using PHP Version 5.2.6-1+lenny8

Well, I made your changes and it is the closest I've ever gotten to it working. After I was prompted to open the rtf, there was a bunch of confusing stuff on there that looked like:

{\rtf1\adeflang1025\ansi\ansicpg1252\uc1\adeff31507\deff0\stshfdbch31506\stshfloch31506\stshfhich31506\stshfbi31507\deflang1033\deflangfe1033\themelang1033\themelangfe0\themelangcs0{\fonttbl{\f0\fbidi \froman\fcharset0\fprq2{\*\panose 02020603050405020304}Times New Roman;}{\f34\fbidi \froman\fcharset1\fprq2{\*\panose 02040503050406030204}Cambria Math;} {\f37\fbidi \fswiss\fcharset0\fprq2{\*\panose 020f0502020204030204}Calibri;}

I'm assuming it has to do with formatting the document. Also, when I run the script on my localhost, I am prompted to open the document. When I put it on the server, the jumbled output is displayed directly on the screen (no prompt to open the rtf).
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Mail Merge from mysql database

Post by requinix »

That's the right output - it's the RTF format. See how it uses stuff you can actually read? Not the same for DOCs.

Using application/octet-stream should make the browser prompt for open/save. Try removing the quotes from the filename - I don't think that's included in the standard.
dreeves
Forum Commoner
Posts: 39
Joined: Thu Oct 22, 2009 8:53 am

Re: Mail Merge from mysql database

Post by dreeves »

tasairis wrote:That's the right output - it's the RTF format. See how it uses stuff you can actually read? Not the same for DOCs.
If that is the correct output, why does it look so weird? It's not much good to me if it looks like that. I re-created the rtf in notepad and it worked fine, but I would like the letter to have some formatting.
tasairis wrote:Using application/octet-stream should make the browser prompt for open/save. Try removing the quotes from the filename - I don't think that's included in the standard.
I've been messing around with different headers to trying to make the browser prompt for open/save. I still get "David" printed on the screen.
Li0rE
Forum Commoner
Posts: 41
Joined: Wed Jun 07, 2006 6:26 am

Re: Mail Merge from mysql database

Post by Li0rE »

Allow the file to be downloaded, not streamed to the browser, then open it with a rich text editor and you'll see it.
Post Reply