How to do Mail Merge with php and MySQL

[kuau]

Yikes! I knew there was something very funky about this code but was't sure what. So the Register_Globals is a totally separate issue I need to fix? I am correct in my desire to turn Register_Globals off? When it is turned OFF all the numbers and dates load into the table as zeroes so adjustments are definitley needed. The code originally had no POST variables at all. I added a bunch, but perhaps not enough. Can this issue be corrected by making sure all the variables are POST variables? I haven't ventured into his date manipulations too much yet as it was over my head.

It is a relief to know that at least this email issue is not affected by the Register_Globals setting so we are narrowing it down. When using a function, can I put it in a separate file and then include it as needed? Do I have to include it every time the function is called? ie. how long do functions stay in memory?

[djr33]

Yes, it is a totally separate issue. It's not something you "need to fix", but you probably want to. It will work fine with register_globals on, and it may be a pain to rewrite the code so it works without it.
Register_Globals simply takes all the values of those input arrays and makes them individual variables.
So that means that if your dates or anything else are showing up as blank, then you need to use $_POST['date'] instead of $date, or whatever the name of the variable. The problem with Register_Globals is that it does this for ALL of the possible input arrays, so it will be hard to figure out where it is coming from-- the only way is trial and error. Use print_r() to print out the arrays (GET, POST, COOKIE, SESSION, SERVER-- I think that's all of them, and it won't be the last-- that's server configuration stuff), then see which one holds the info. You can also guess, if you happen to know you're using sessions, or submitting a form (get/post), etc.
There is also the option of $_REQUEST which is a combined array that holds the values of $_POST, $_GET and $_COOKIE. Using it will be easier than figuring out where each variable is, or if they may come from multiple sources (like maybe sometimes from a form, other times from cookies, etc), but it also gets messy for similar reasons to Register_Globals, but the difference is that there is no security hole-- it's just not clear if you're using cookies, forms, or whatever, but it will be stuck in $_REQUEST so you won't run into any conflicts with other variables.

Including files is like cutting and pasting the code into your main page. There is no difference* except in where it is stored. (*Rarely, you might have a case where it matters, but this is way beyond anything going on here-- for example, it might be an issue if you are dynamically including pages or something like that.)

functions are defined once and only once-- redefining them will give a (fatal?) error. Of course this is only true for each page load, so it doesn't "stay in memory" beyond just one page, but it will be there for the whole page. functions themselves do not have scope-- they are completely global and always available. Within ANY function, though, only local variables (and those sent in the function like function($var)) will be available. Use the keyword "global" to give the function access to a main variable.

http://www.php.net/manual/en/languag...bles.scope.php
There's plenty of info about all of this there.

The real problems are just when you have to figure out what is going on with scope, because it can be very hard to track. Take it step by step and work through the code. That should help you figure things out.

[kuau]

OK, I updated the files with the str_replace function and so far all is well, so that impending disaster has been averted... Thank you!!

Re Register_Globals, there are no sessions or cookies so I think the loading blanks problem is localized to entry forms and date manipulations, but I'd like to tackle that one after I've finished with the email merge.

Are you saying I can replace this code:

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

$body = replace_email_template_variables($body);

with

$body = file_get_contents($Email_now);

$body = replace_email_template_variables($body);

Sometimes 'r' was used, sometimes 'rb'... does this matter when I replace it?

Mahalo, e

[djr33]

Glad str_replace() works. If you ever run into trouble in the future, manually compare the search and replace values and find out what doesn't match. str_replace() does not have the complexity of eregi_replace() so there is a chance something won't match up-- but you can figure out what it is by comparing it manually and adjust as needed. I don't see anything that would be a problem in what you posted, though.


If there are no session variables, then you can definitely get all the info using $_REQUEST. It's slightly messier than being specific with $_GET and $_POST, but it's not that big of an issue. When you turn of Register_Globals and you find that a variable is blank, replace $variable with $_REQUEST['variable'] and it will work again. (Of course you'll need to find all of the variables to make it work fully, but it should be fairly easy, if time consuming.) But I completely agree-- if it works, it works, and it may be worth fixing but don't worry about it for now. Go back through once the rest is fixed up.


As for those two bits of code, in short, yes, you can replace it with that.
In detail, there are a couple issues:

1. The only advantage of the original code was that it split at line breaks and controlled line breaks and line length:
--Line length is an issue with emails. The specification officially requires that the format of emails is lines of only 70 characters. This makes emails compatible with even the oldest formats/applications. However, html emails, I believe, are new enough that this should never be a problem. (This is like having valid html... valid emails require 70-character lines... as a practical issue, I don't care much about that, but it doesn't hurt to be careful, I guess. This is why you will receive emails that have strange line breaks that don't make sense-- the mail server chopped the lines at 70 characters.)
--Line breaks are a complex issue within themselves. Different operating systems represent line breaks differently: some as \n, some as \r, and some as \r\n... why? Because competing software companies (Microsoft, Apple, and in this case Linux as well) want to make it harder for web designers (at least that seems the logical reason...). PHP has built in ways of dealing with line breaks and one was used in the original code. file_get_contents() will just dump the original contents into the variable then into the email, so line breaks may not always show up properly. Because of that, you may want to redo all the linebreaks with PHP to be sure they do work. I'm not sure what to suggest for that, because I'm not sure how email is supposed to be done in terms of line breaks, but here's a method: use a php function that handles line breaks and then reverse it. For example $fixed = br2nl(nl2br($old)); -- that will switch all line breaks to <br> tags, then back to line breaks. I'm not sure what is best here, and in the end it is probably best to just leave the original code-- if it works, it works, and there is no reason to change it if those functions are not getting depreciated. My main point in saying you could replacing it was just to explain how it works.

2. "r" and "rb" along with other terms are the ways in which files are opened. The letters stand for different things, like whether you are opening an existing file, or opening/creating a file, or if you're opening it for read only (I think that's "r"), or for writing ("w"), etc. There's a list at php.net under fopen().

[kuau]

Thanks for all the great info.

1. Seems odd to me that they would deprecate eregi_replace and regi_replace when there doesn't seem to be an equal alternative. Is using the percentage signs in the email text the proper way to indicate a variable, eg. %Comment% ?

2. Would I use $_REQUEST for all the variables in the INSERT command regardless of whether they are POST or calculated variables? I may be wrong but the way I have been doing it is to use POST if the variable comes straight from a form field, and just the variable name if the value has been taken from a form field and manipulated. Here is how I am loading the table (which works with Register_Globals = On but blanks all the dates and dollar values when it is Off):

$sql = "INSERT INTO booking
          ( `updated`,`country`,`Book_Count`,`Book_Confirm`,`confdate`,`Book_Date`,`Book_Begin_Date`,`Book_Begin_Time`,`Book_Pick_AP`,`Book_End_Date`,`Book_End_Time`,`Book_Drop_AP`,`Book_Days`,
					`Book_Car`,`Book_Comp`,`Book_Pick`,`Book_Drop`,`Book_Age`,`Book_First_Name`,`Book_Last_Name`,`Book_Phone`,`Book_Email`,`Book_Flight`,`Book_Cruise`,
					`Book_Card`,`Book_Net_Price`,`Book_Full_Price` )
        VALUES
          ( '$updated', '".$_POST['country']."', '' , 'Not confirmed', '0000-00-00','$updated', '$SQL_Pick_Date', '$N_Pick_Time', '".$_POST['Pick_AP']."', '$SQL_Drop_Date',
					'$N_Drop_Time', '".$_POST['Drop_AP']."', '$Days_Num', '".$Car_Class."', '$Best_Rental', '".$_POST['Pick_Location']."', '".$_POST['Drop_Location']."', 
					'".$_POST['Age_Group']."', '".$_POST['First_Name']."', '".$_POST['Last_Name']."', '".$_POST['Phone']."', '".$_POST['Email']."',
					'".$_POST['Flight']."', '".$_POST['Cruise']."', '".$_POST['Credit_Card']."', '$DB_Price_Net', '$DB_Price_Full' ) ";

Seems to me that if you take a POST variable value and assign it to another variable, it should be able to load into the table (?).

3. In all of the emails I am using, the files already exist as external html files - they are a known quantity - that are formatted within a table, so aren't the line lengths already determined and controlled/limited? I'm not sure how php reads a file, but unless you set a buffer length, does it dump it all into one mass of text? Does it ignore html formatting tags?

I did read about fopen at php.net and found it interesting that it lists all the options as r, w, a, and x and then recommends for portability that you use "b" which isn't even an option. Who writes this stuff?!

4. One new question: The old programmer appended the Google Analytics code to each email... does it do any good here, or should I remove it?

Thanks for all your help! e

[djr33]

1. eregi_replace is possible to duplicate with other functions. You could check function_exists() and if not recreate it in other versions. Basically it is just a regular expressions search and replace; I'm not sure which function is best to replace it, but there are a few combinations you could do. I've never been particularly good with regex so I don't use it much.
The %variable% format is just a way of marking it. It is sometimes a standardized format (in the sense that a lot of people use it), but there is no rule that you must write like that. In fact, the % symbol has many uses in various languages, so it can be confusing. But it is rarely used in text in that format, so it is a reliable marker that you won't get conflicts with-- if you are using "%name%" as the marker to replace a name in the text, the odds of having a conflict with text that should actually be output as "%name%" (not replaced) are very low. You can mark it however you want; just use str_replace() to find it and replace it. There's nothing wrong with marking it like that, or you could change it if you want (but I don't see a reason here why you'd need to).

2. $_REQUEST is a combined array of $_COOKIE, $_POST and $_GET-- in other words, all the form variables + cookies... all of the variables sent from the user's computer with the page request. It will never contain variables you create and change during the processing of the script. That is why Register_Globals is confusing. You need to determine which variables in the insert are user-input (use REQUEST) and which ones are from your script (use the normal format). The only advantage of $_REQUEST is not having to figure out if the variables are from cookies, post forms or get forms. That way it will save a bit of time, but it won't eliminate the need to determine which variables are from what, just that you won't have to do it as precisely.

3. PHP reads a file as characters. file_get_contents() returns a string of the file. It does not change, ignore, add, or do anything else to the text. What you have in the file is what you get in PHP: if you were to echo file_get_contents() and nothing else on the page, then you would basically be viewing the file in your browser.
Yes, the line length problem may not be an issue. But it is something you should know about, because in theory it might be at some point. It is also illogical to be replacing after cutting the lines down, because the replacing values (like username) might be very long and make the line too long then anyway. I would say don't worry about it and test the email. If it is working, maybe check on what you can do to make it perfect later, but for now just go with it.

4. No idea. What does google analytics do for emails? There's nothing wrong with removing it, because google analytics just stores statistics. If you don't store the statistics then you won't track something. Unless you need those statistics, there is no reason to keep it. In fact, I think that sounds annoying. But since I don't know what google analytics does specifically for emails, I can't say much more.

[kuau]

3. You certainly are a wealth of information! I'm going to try the email using file_get_contents() and see what happens. If nothing else, the code will be more compact. I like that.

1. I put the code that replaces the variables in an include and plan to ditch the function unless there is some advantage to using a function (?). There are several other places where replacements are done without the function - I am trying to standardize and eliminate duplicate code so I can get an overview of what is actually going on. Then it will be easier to get a handle on the Register_Globals issue. This guy copied and pasted all over the place... what a nightmare.

4. I'll remove the Google Analytics from the emails. I never liked that.

5. The code always breaks the dates into parts. I'm wondering if there are now newer date functions that did not exist back when it was written (php 3-4) that would help simplify things. Is all this dividing then converting to seconds necessary just to determine the number of days between two dates? I know some people feel that dates should always be stored in unix format, but what if you like to work directly in the tables?

$v_days_diff = (mktime(0,0,0,$_POST['Drop_Month'],$_POST['Drop_Day'],$_POST['Drop_Year'])) - (mktime(0,0,0,$_POST['Pick_Month'],$_POST['Pick_Day'],$_POST['Pick_Year'])); 
$v_days_diff = $v_days_diff / 86400; 

$Days_Num = round($v_days_diff,0);

Thanks! e

[djr33]

1. Functions are only useful for two reasons:
1) it separates and organizes code. This is sorta a misuse of functions, though, and doing an include or just adding comments to your code is probably a better idea.
2) The intent of functions is to not cut and paste code. It makes a section of code repeatable and easy to use with variable input (like function($var,$var2)) when you are frequently doing the same operation. It is in some ways similar to doing an include, but more standardized.

What I do on my bigger projects now is have an extension to the default php function library stored in a main folder, let's say /inc/. So /inc/functions.php is included in every page, and that way I have my standard operations included. For example, in my latest project there are user accounts and in functions.php there are functions like loginuser() and logout(), etc. That way they are always available. If you think you'll use the same functions a lot, or you write your own functions that add functionality to the php defaults, like maybe your own custom version of eregi_replace(), for version 6 and beyond (which is possible), then make an extension using this method and include it in every page.


4. I agree. You can always add it back in later if you need it.

5. If it works, don't do anything to it. Dates are hard to work with. The standard operations with dates are going back and forth from "human readable" dates to timestamps. Timestamps are very easy to store and always predictable. Human readable dates are a little easier to work with and always the format of user input and output. date() and mktime() are the standard formats for working with them. The easiest way to do arithmetic operations with dates is to convert to a timestamp, add, subtract, compare (greater, less than), or whatever else you want, then return them as human readable dates afterwords, if needed. They are fairly easy to work with mathematically: 24 hours in a day, 60 minutes in an hour, etc. Just do the math, and that's it. The problems are usually due to figuring out how to interpret user input and sometimes just getting really long bits of code for seemingly simple operations. That's what's happening here. If you want it more readable, you can separate each step into a different line and make the variable names shorter. This will make more code in the end, though, just in a less cluttered way. But it will still mean rewriting working code, and I don't see much of a point in that.

[kuau]

Dear Daniel:

1. What do you mean by an "extension" when you say "an extension to the default php function library stored in a main folder" -- a subdirectory? What do you mean by the default php function library? the functions that are built into php and are on the default include path in php.ini? There are some really basic things about php I don't know. I've had to learn it on the fly, piece by piece like a jigsaw puzzle, so things start to make more sense the more I learn, but I still have no idea what the big picture looks like or even how big it is. Your explanations help me immensely.. thank you for your patience & magnanimity in sharing your knowledge and experience.

5. I'm extremely relieved that I don't have to redo all the date code. I guess I just need to understand it. I didn't want to spend time learning a crappy way of doing something but, if what he did is the best way, then I am happy to learn it.

6. When you were helping me with the eregi_replace I discovered at least 9 places in the code where he used either the function or the commands on the same data, but he was not consistent with the variable names... what a nightmare. Am I correct in my efforts to put redundant code into includes? Is there ever a justification for using different variable names when loading data from the same table? I'm thinking (but doubting) it might be necessary in the following scenario:

Data is loaded from a table and displayed on the screen.
Someone then edits that data in a form (eg. adds a confirmation number, perhaps corrects an email address).
The updated data is then put back in the original table.

After the editing, the data in the form should be in $_POST variables, no? I notice he updates the table but does not use $_POST anywhere. In any case, I don't see why the variable names need to be different here when he loads them from the table for admin editing than when he loads them from the table for client editing (eg. to cancel) or to send emails. Am I misunderstanding?

Thanks again for your help! You are a saint. e

[djr33]

1. "extension", as in an extension to the existing code in PHP. If you are frequently doing complex operations with arrays, you might write a bunch of additional functions to deal with arrays, so this would be an extension to the default functions for arrays and you'd want to include them in all pages. PHP has certain functions; you can expand it by writing your own, as you want to. PHP contains (in a rough sense) two parts: 1. the parser; 2. the default functions and other operations. You can expand this by making more functions (and including them into all of your scripts). It doesn't need to be an extension exactly, but you could just make a few functions you want to use often, etc.

6. If you want to, you can write a function for this. That's what the best approach would be. Write a function like dostuff($a,$b,$c), and define this in a page somewhere. Then include that as an extension to the rest of the pages so it is always usable. (In other words, just include this at the top of all of your pages so you can always call the function; if nothing else is in the file but a function definition, then it won't do anything except define an extra function to be available.) Then in each of these instances, you can call that function "dostuff()" with the current variable names and you don't need to change anything. That's the main point of functions-- repeated operations with different variables. function add($a,$b) { return $a+$b; }. You can use that function now to add any $a and $b. Of course you don't need that (since there are built in ways to do it), but that's the point of functions: expanding the default operations to do things you need repeatedly and with variable input. It's entirely up to you. Personally, I wouldn't rewrite the code if it's all working unless there is some need to expand/change in the future-- changing all of that code would be as hard as making it based on a single function (or include), so you might as well go ahead and do that now, if you think you'll be updating/changing the site significantly in the future. There is nothing wrong with not using functions, except that it becomes hard to deal with the code. So that is a personal choice between you and your code-- what's easiest to deal with?

If you get into a situation where it sounds like functions might be helpful, here's how I do it:
1. make main folder called /common/ (or whatever you want)
2. make a file called "functions.php", in which you have the following code:
include dirname(__FILE__).'/name.php';
a) "name" is something like "arrays" or "mysql" or "users"-- the name of the set of functions you'll put there, to keep things organized. You could just write the functions directly into "functions.php" but it will be easier to keep them organized if you have different files for all types of operations.
b) dirname() is used to get the relative location of the files to be included-- otherwise if you include this from two different folders on your site (like the main index.php page, and /contact/index.php), then the relative locations will be off-- so base it on the file's (function.php's) location.
c) repeat that line of code for every extra page of functions you want.
3. In each of those pages, define extra functions that might help you, like my_mysql_connection(), which would automatically start the mysql, or whatever else you can think of that can be automated, like add($a,$b), if you plan to use that a lot, for example.
4. Just then include /functions.php (using a relative path-- like ../../functions.php if you are two folders in) into every single page you may want to call them from.

Of course this is a lot of setup work, but if you plan to have similar operations on many pages this structure is really helpful. No need to use it for this (or any) project, but I like it when I'm working on a big project with lots of repeated php operations.