How to do Mail Merge with php and MySQL

[kuau]

I have googled this to death already and cannot find any answers, so I am hoping the DD geniuses can save the day.

What is the correct way without using global variables (eregi_replace has been deprecated) to get data from a MySQL database into variables in an html email? There are no $_POST or $_GET variables. I tried using echo with php but it would appear php does not work in an html email.

I had been using the following code (which I never really understood & may have been awful in the first place) and would really like to learn the best possible way. I don't like having Register Globals = On in the php.ini but everything goes blank if I turn it off. Thanks!

@$fp = fopen($html_email_template_file, "r");
while(!feof($fp)) {
 $buffer = fgets($fp,100);
 $body.= $buffer;
}
fclose ($fp);
	
$body = replace_email_template_variables($body);

$Subject = "Your Quote Request";
$mail = new phpmailer();
$mail->From = "info@domain.com";
$mail->FromName = "Company Name";
$mail->AddAddress($_POST['Email'], $name);
$mail->AddReplyTo('info@domain.com', 'Company Name');
$mail->IsHTML(true);
$mail->Subject = $Subject;
$mail->Body = $body;
$mail->Send();

[bluewalrus]

I don't see any sql in there or where variables would be displayed as the name rather than value.

[kuau]

That's because I didn't think it necessary to include the html template or the SELECT command. There a bunch of different emails but they all have variables in them that look like this:

<tr><td width="114">First Name:</td><td>%First_Name%</td></tr>
<tr><td width="114">Last Name:</td><td>%Last_Name%</td></tr>
<tr><td width="114">Age Group:</td><td>%Age_Group%</td></tr>

and there is a SQL command like:

SELECT * from table WHERE id = $id

or whatever. The particular application here is people requesting a price quote on a car rental for specific dates and times, a particular car class, age group, pickup & drop off location, etc so there are a lot of variables that have to be inserted in the email body. I know how to do it in a text email with GET & POST variables, but how do you do it for an html email in which the template exists as a separate html file, and is not just a text email assembled on the fly? I would have thought this a fairly common procedure.

Almost every website has a Contact Form. Say you wanted to send something fancier than a plain text email as a response... it is the same principle using GET & POST. I would like to send a more professional looking email. I suspect there is a simple solution but I just can't find how to do it. Anyone?

[djr33]

I'm also very confused about what you need, exactly. Are you looking to replace something in a string? Just get the contents of the file and do a search and replace operation. str_replace() will do that, if you want to replace "%name%" with $name.

Regardless, here's an explanation of the code in the first post so you know what it does:

@$fp = fopen($html_email_template_file, "r"); //open a file for use
while(!feof($fp)) { //until you get to the end of the file (file-end-of-file)
 $buffer = fgets($fp,100); //get a line, only 100 characters long
 $body.= $buffer; //add this line to the body of the email
} //end of the loop
fclose ($fp); //we don't need the file any more, close it
    
$body = replace_email_template_variables($body); //is this your function?
//I suppose it modifies the $body variable to replace values
//see my note above for doing this; str_replace() is easiest, probably

$Subject = "Your Quote Request"; //set the subject
$mail = new phpmailer(); //phpmailer() is a class, so let's make a new "mail"
$mail->From = "info@domain.com"; //add a value to it based on the class
$mail->FromName = "Company Name"; //repeat
$mail->AddAddress($_POST['Email'], $name); //...
$mail->AddReplyTo('info@domain.com', 'Company Name');
$mail->IsHTML(true);
$mail->Subject = $Subject;
$mail->Body = $body;
$mail->Send(); //finally, now it's ready, so send it 

One note: the function file(), which I think is actually oddly designed, does what the first lines of the code do with more code in them. file() opens and stores the text of a file into an array separating the lines. So $f = file('--'); would be the same as the code above, then a foreach() loop would easily replace the rest there, if you for some reason want to go line by line. I suppose perhaps the idea is to truncate so lines are at most 100 characters. file_get_contents() would just get the entire text of the file without doing anything about line breaks, but you probably do want to convert a bit for the email.

[kuau]

Dear Daniel:

Thanks so much for explaining the code. I'm always afraid to try to replace code when I don't fully get what it is doing. But now I have no choice because, with the next php upgrade on the server, this code will no longer work. Here is the function:

function replace_email_template_variables($body_text) {	// This function replaces the variables in the Body text for the email
	global $Today;
	global $First_Name;
	global $Last_Name;
	global $Phone;
	global $Email;
	global $Age_Group;
	global $Flight;
	global $Cruise;
	global $Credit_Card;
	global $Best_Price_Net;
	global $Best_Price_Full;
	global $New_Pick_Date;
	global $N_Pick_Time;
	global $Pick_Location;
	global $Pick_AP;
	global $New_Drop_Date;
	global $N_Drop_Time;
	global $Drop_Location;
	global $Drop_AP;
	global $Car_Class;
	global $Days_Num;
	global $country;	
	global $employee;
	global $Car_Logo;
	global $agencyname;
	global $Comment;
	$Comment = "";
	
	$body_text = eregi_replace('%Today%', $Today, $body_text);
	$body_text = eregi_replace('%First_Name%', $First_Name, $body_text);
	$body_text = eregi_replace('%Last_Name%', $Last_Name, $body_text);
	$body_text = eregi_replace('%Phone%', $Phone, $body_text);
	$body_text = eregi_replace('%Email%', $Email, $body_text);
	$body_text = eregi_replace('%Age_Group%', $Age_Group, $body_text);
	$body_text = eregi_replace('%Flight%', $Flight, $body_text);
	$body_text = eregi_replace('%Cruise%', $Cruise, $body_text);
	$body_text = eregi_replace('%Credit_Card%', $Credit_Card, $body_text);

	$body_text = eregi_replace('%Best_Price_Net%', $Best_Price_Net, $body_text);
	$body_text = eregi_replace('%Best_Price_Full%', $Best_Price_Full, $body_text);

	$body_text = eregi_replace('%New_Pick_Date%', $New_Pick_Date, $body_text);
	$body_text = eregi_replace('%N_Pick_Time%', $N_Pick_Time, $body_text);
	$body_text = eregi_replace('%Pick_Location%', $Pick_Location, $body_text);
	$body_text = eregi_replace('%Pick_AP%', $Pick_AP, $body_text);

	$body_text = eregi_replace('%New_Drop_Date%', $New_Drop_Date, $body_text);
	$body_text = eregi_replace('%N_Drop_Time%', $N_Drop_Time, $body_text);
	$body_text = eregi_replace('%Drop_Location%', $Drop_Location, $body_text);
	$body_text = eregi_replace('%Drop_AP%', $Drop_AP, $body_text);

	$body_text = eregi_replace('%Car_Class%', $Car_Class, $body_text);
	$body_text = eregi_replace('%Days_Num%', $Days_Num, $body_text);
	$body_text = eregi_replace('%country%', $country, $body_text);
	$body_text = eregi_replace('%Employee%', $employee, $body_text);
	$body_text = eregi_replace('%Car_Logo%', $Car_Logo, $body_text);
             $body_text = eregi_replace('%agencyname%', $agencyname, $body_text);
	$body_text = eregi_replace('%Comment%', $Comment, $body_text);  
	return ($body_text);
}

An example of the html template is http://www.carrentalhawaii.com/html/...mation-us.htmlhttp://www.carrentalhawaii.com/html/...mation-us.html

Seems to me the 100 character buffer is not necessary as the email format is handled within the email itself. So is this the right way...?

$sql = "SELECT * FROM booking WHERE book_id = $book_id ";
$result = mysql_query($sql,$connection) or die("Couldn't execute $sql query.");
$book = mysql_fetch_array($result);

$body = file_get_contents($html_email_template_file);
str_replace(%First_Name%,$First_Name,%body);
str_replace(%Last_Name%,$Last_Name,%body);
str_replace(%Phone%,$Phone,%body);

etc, doing each possible variable one-by-one. I find mysql_fetch_assoc($result); more compact but am not sure how to use it here. Could I say:

str_replace(%First_Name%,$book['First_Name'],%body);

or is there a compact way of saying, "Get all the data from the file for that record & replace all the variables in the email with it" using just one statement?
What I am after is a "best practices" model of code I can use whenever I need to get data from a MySQL database, or even from GET & POST variables into an html email. All I have to go on is a very old model of how to do it badly using a method that has been deprecated. I really appreciate your help and skill with the language. It's hard to become fluent in a language when I have to learn it as needed in circumscribed chunks. Mahalo! e

[djr33]

When you need information on a specific function, there will be plenty at php.net:
http://php.net/manual/en/function.eregi-replace.php

According to that, the function ignores case and replaces the content. str_replace will work, or if you do need regex you can use a more complex method (but I don't see why you need that here-- the format of what you are replacing will always be the same, right?).

str_replace returns the changed string. That means you need to use this format:
$new = str_replace($search, $replace, $old);

You can use the same variable for $new and $old, so you are just updating it. But just running the function won't change it-- you need to save it's output back into a variable for later use.

I don't see any reason you can't just replace every instance of eregi_replace() with str_replace() in the code above and it would not work. eregi_replace() allows more options than str_replace, but I don't think any of them are needed in this particular code.

[kuau]

Dear Daniel:

I had gone to php.net, which is why I am so concerned about the code. Did you notice this warning under the eregi_replace function description?

Warning
This function has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 6.0.0. Relying on this feature is highly discouraged.


Please bear in mind that I did not write this code. I just took over the site to help out a friend after his programmer bailed, and I've been trying to figure out what the code is doing and update it bit by bit. It is a live ecommerce site so I have to be very careful not to break things.

So are you saying I should use the same function but just replace the eregi_replace() with str_replace()? I thought it was bad to use global variables... are these necessary? Isn't there a better way to do this? I have the impression this guy wasn't the greatest programmer in the world so I would be surprised if his method was the most efficient. Is there an example somewhere of the best way to a do a mail merge? Or is this called something else?

Mahalo, e

[djr33]

Global variables are sometimes needed. They only real problem with them is that they are annoying to keep track of, so you may end up with a weird situation where something is or is not global that you don't expect and odd things occur. But in a situation like this, that's fine.
The traditional way to get information into a function is within the function call: function($data,$data2, etc)
But when you need so many variables, and when you know what they will be called in the main part of the script, there is no reason not to use globals.
You could always rewrite it without the function. Functions are generally for chunks of code you will use repeatedly and with variable input. But if this is just a one-time process, you can include it in the main part of the page. That is-- take all the code from the function and move it to where the function is called. But it's not hurting you as is, if you don't mind the use of globals (which is not a problem).

And, yes, just replacing them should work. If it doesn't, try to figure out what isn't matching, but I don't see any reason. eregi_replace uses regular expressions to look for a match. That's fine, and useful sometimes, but here you are replacing a normal string, not something you need to search for with regular expressions.

[kuau]

The reason I am concerned about globals is because (for security reasons) the default setting for php.ini is Register_Globals = Off;. This is the only one of my sites that has it turned On, so when the host upgrades their php installation and the php.ini gets replaced, the whole site breaks and the owner freaks out (& loses money).

Is it true that if Register_Globals = Off; in the php.ini that using global variables won't work? If so, is there a way to accomplish the variable replacement without setting them to global? Perhaps I don't really understand the difference between regular variables and global variables. I thought it was that regular variables live only in one file whereas globals persist in memory so can be passed between files (?). Is it because the html template is in an external file that globals are needed?

In this application, the code is used in multiple cases so I'll edit the function & see if it works. Thanks so much for helping me understand. e

[djr33]

No, those are two entirely different types of "global" variable.

Global variables are those that have unlimited scope. "Scope" is the area in which a variable is valid. By default, variables are limited to where they are created. They are either available in the page as a whole, or within a function where they are created. global $var; is the method of unlimiting a global within a function. This means that a global in the main script is now available within a limited function (of course this has to be called within every function).

Register_Globals is something different. Some global variables are by default global, rather than using the command "global" within a function. These are generally called "super globals".
Anyway, what Register_Globals does is something very specific:
The arrays from input and server configuration ($_POST, $_GET, $_COOKIE, $_SESSION, $_SERVER) are automatically super globals. When Register_Globals is turned on, then these values are available outside of the array, as variables:
$_X['name'] is then available as $name directly, as a global variable.
In other words, this has nothing to do with your current script because you are dealing with variables you create and making them "global" within a function-- expanding their scope only.

Register_Globals is generally bad, for three reasons:
1. It supports lazy coding. You can use, for example, $username, and not be paying attention to where it came from-- $_SERVER? $_POST? $_SESSION? etc. (Of course this may be "helpful" if you aren't sure where it is coming from, but that's also lazy.)
2. It can confuse things. Let's say you forgot about a cookie stored under the name "x". Then you have another variable in your script named $x, and suddenly those get mixed up and you aren't sure where it's coming from. This annoying Register_Globals setting will mess up your code in a way that is not visible in the script itself, if you aren't expecting it.
3. It can be a security risk, where a variable can be submitted by the user. In the url the user can type ?x=1, and if $x is used to check something for security, then it will be injected as the value for $x in the script. This also applies for mysql injection, where now every variable in your script is a possible security hole if someone knows how to inject values into your queries by just sending get variables in the url (or other similar methods, like cookies).


So: in short, Register_Globals is a pain and lazy coding. Sometimes you need it on if you are using a script someone wrote lazily, but it's more of a problem than it is helpful. But this has nothing to do with the current script. Globals aren't bad-- but making them automatically be generated from all of the input is, because things get messy.
You do point out, though, that this name is very confusing. It should be called "Globalize_Input_Arrays" or something clear like that.