Monday, November 28, 2011

My simple Macaroni and Cheese recipe

Not strictly macaroni as it uses Penne pasta, but this cheese sauce based recipe will serve four people comfortably. For two people reduce the amounts by half. You could use macaroni instead of the penne pasta. Like all of my recipes, this has been simplified so it can be made using the limited range of ingredients I have in the cupboard (e.g. plain flour instead of cornflour). The paprika and nutmeg I use is Asda’s own brand in little jars.

500g penne pasta (bag)
50g salted butter
200g cheddar cheese (you may prefer more, or less, cheese)
50g plain flour
500ml full cream milk (semi skimmed will work too)
½ tsp paprika
Pinch of freshly ground nutmeg
Freshly ground black pepper

Grate the cheese.
Measure out the other ingredients.
Put the serving bowls in a very low oven to heat them.
Put the pasta on at a low heat initially (accelerate or take off heat as required during the sauce making process).

Melt 50g butter in a large saucepan over a medium heat
Stir in 50g flour and cook for one minute, until it turns light brown in colour.
Let it cook for a short time, stirring all the time.
Gradually whisk in 500ml milk, reduce the heat and cook for 10 minutes, whisking constantly.

Once thickened, stir in ½ tsp paprika and a pinch of nutmeg.
Season with salt and pepper
Stir in the cheese, retaining some for sprinkling on the top.
Set aside to cook slightly.
Check that pasta is cooked.

Boil the pasta until aldente.
Drain thoroughly.
Add back into the pan and stir in the sauce.
Mix in half the sauce.

Serve in the bowls with a sprinking of cheese on the top

Tuesday, November 8, 2011

Alternative to the Metro Newspaper Mobile Version

I notice that the mobile optimised version of the Metro newspaper normally found at has disappeared at the same time as the main Metro web site has been redesigned. The mobile address just sends you to their normal web site now.

I contacted Metro via Facebook, but got no response and they deleted my enquiry from their wall so I can only assume that is a dead duck. The main Metro web site is difficult to navigate on a phone which is why I used the mobile version. There is a way round this though. Google has a web kit which can make a standard page easier to read on a mobile device so try going here instead:

I don't know what this says abut .mobi or the mobile web in general. Quite a few newspapers do not have mobile versions so I tend to bookmark their main sites through the Google web kit.

Update: I just got a message from the Metro that they will be launching a new mobile site in the next few months so we might be back on track. Meanwhile, try the link above.

Monday, November 7, 2011

Sending an Email Merge from Microsoft Access using PHP

I was recently faced with a network restriction on sending out emails to more than 20 recipients from Outlook when we had over 800 members on our Access database who required to be mailed notifications for meetings and newsletters. I did try a number of mail merge options including Worldmerge by Coloradosoft, but I felt that what we needed was a simple hosted solution that avoided the need for an onsite mail server. In the long term we need to move to a service like Mailchimp, but everyone would (presumably) need to verify their opt in again and I am not sure how we would keep it in synch with our database.

Because of these difficulties I decided that as a short term solution I would generate recipients as CSV files from a database query and send the emails with a PHP script hosted on a web server. This worked and here is how I did it. I hope this is easy to follow. Its not intended to be a downloadable script solution, but a way of showing how the issue can be addressed fairly simply. It might help teach the basics of the PHP mail function too.

Generating the CSV file
Set up a query in Access which selects the recipients, but shows only first name, last name and email address. By incorporating the persons name into the email it makes it  more personal and I have found I get more feedback.

The Mailer Script
For simplicity, the bulk email script exists as three separate parts. The first (mailer.html) is an html form; the second (writefile.php) is a script for writing the address data to a file and the third (mailer.php) processes it and sends out the emails.There is a fourth file called addressfile.txt which is the list of names and email addresses.

This form I called “mailer.html” and consists of two forms on the one page.
The first sends certain values to the mailer script (from email address, real name, subject, message and footer). the second allows the comma separated values to be written to a text file on the server. here is the html for the two forms on mailer.html (aplogies for the use of tables - this wad done in a hurry):

<form method="post" action="mailer.php" name="mailer">
<table style="text-align: left;" border="0" cellpadding="2" cellspacing="2">
<td align="undefined" valign="undefined">From:
<td align="undefined" valign="undefined"><input name="from" value=""></td>
<td align="undefined" valign="undefined">Realname:&nbsp;&nbsp;</td>
<td align="undefined" valign="undefined"><input name="realname" value="Your Real Name"></td>
<td align="undefined" valign="undefined">Subject:&nbsp;&nbsp;</td>
<td align="undefined" valign="undefined"><input name="subject"></td>
<textarea cols="70" rows="20" name="message"></textarea><br>
<textarea cols="70" rows="10" name="footer">
<input name="btn" value="Submit" type="submit">
<form method="post" action="writefile.php" name="mailer"><br>
<textarea cols="70" rows="20" name="list"></textarea><br>
<input name="btn" value="Submit" type="submit">

You could set a default value for any of these fields so you don’t have to keep retyping them every time you send an email.

Write File Script
Open the CSV file in a text editor and copy and paste the values into the form. Press the submit button and it will write these to a file called addressfile.txt.

I called this script writefile.php

$filename = "addressfile.txt";
// file will need chmod to 666 or 777
$text = $_POST['list'];
$fp = fopen ($filename, "w");
if ($fp) {
fwrite ($fp, $text);
fclose ($fp);
echo ("File written");
else {
echo ("File was not written");

You should create an empty file called addressfile.txt and upload it to the same directory as the script and CHMOD it to 666 to give the scruipt the intial parameters to work from. A simpler solution would be to allow the upload of the CSV file from the form. I didn’t do it this way at this stage as I did not know for sure what the file permissions situation would be on writing a file. I will be replacing it shortly with that system. Some web servers will write files from PHP as a different user and you might not be able to delete or modify them so this is not an exact process.

Mail Processor Script
Clicking on the submit button in the top form of mailer.html posts the form values to the script mailer.php (below), opens up addressfile.txt, reads each line, explodes the entry into an array of first name, last name and email address thensends individual emails to each of these recipients. For simplicity it only handles plain text emails at the moment. It adds a “Dear $firstname” line to the beginning of the email.

There are some in-line comments that help explain what the script is doing:

//assign post data from the form to variables
$from = $_POST ['from'];
$realname = $_POST['realname'];
$subject = $_POST ['subject'];
$message = $_POST ['message'];
$footer = $_POST ['footer'];
//this first section processes the body of the email message
//PHP will escape any apostrophes or quotation marks so first we have to strip out the slashes
$message = stripslashes($message);
//to retain formating we limit lines to 70 characters and prevent leading full stops being deleted
$message = wordwrap($message,70,"\n");
$message = str_replace("\n", "\n", $message);
$message = str_replace("\n.", "\n..", $message);
//add a blank line at end of message to separate it from the footer
$message = $message . "\r\n\r\n";
//headers for the email
$headers = "From: \"".$realname."\" <".$from.">\r\n";
$headers .= "Return-Path: <".$from.">\r\n";
$headers .= "Content-type: text/plain; charset=iso-8859-1\r\n";
//open the text file containing the recipients
$file = fopen("addressfile.txt", "r") or exit("Unable to open file!");
//read the file one line at a time until the end is reached
  $dbentry = fgets($file);
//split the line from the text file into firstname, lastname and recipient
  $delimiter = ",";
  $splitcontents = explode($delimiter, $dbentry);
  $firstname = $splitcontents[0];
  $lastname = $splitcontents[1];
  $recipient = $splitcontents[2];
//generate first line and add blank line below it
//$firstline = "Dear $firstname $lastname,\r\n\r\n";
//version for just first name - you only need one of these lines
$firstline = "Dear $firstname,\r\n\r\n";
//join the first line, body text and footer together
  $bodytext = $firstline . $message . $footer;
//send the email
//show success message
  echo "Message sent to $recipient. <br />";
//close the file

Limitations and improvements
This all works OK under PHP5 and should do under PHP4 as well. Its a quick and dirty piece of programming and its not claiming to be well written, but it works.

Some issues that might need addressing:

  • The write file process needs to be changed to uploading the CSV file rather than writing the data. 
  • You may need to list your web server as a valid sender in your SPF DNS record to allow mail to be received by some mail services (depending how tightly your SPF records are set up).
  • With a long mailing list it takes a while for the confirmation page to appear. It would be better to open a window with javascript and write each successful send as a separate line.
  • Moving to a proper templating system where [[Firstname]] would be replaced by $firstname wherever that appeared. Could then move to having any number of fields in the query.
  • Allowing HTML if I could find a way of editing html effectively in a browser window.

Some web hosts limit the number of emails that can be sent using PHP so it might not work for you. I am sending emails to people who are members of an organisation and have opted in (in writing!) to receive emails. You should not use scripts like this for spamming. If you are a spammer you probably know more about this sort of programming than I do so you will have learned nothing from reading this.