Personalised content based on postcode radius [solved]

Solutions for other advanced phplisters

Personalised content based on postcode radius [solved]

Postby kaiatWTB » 2:15pm, Thu 13 Jan, 2011

I’ve had a search through the forum but haven’t found an answer I can use so before I try to re-invent the wheel (which may well end up square due to lack of skills) I thought I’d ask for some help.
I work for a charity delivering Bible seminars and would love to be able to have in the footer of our newsletter details of seminars coming up within a 30 mile radius of each subscribed user.
I have another database which holds all the seminar details including the postcode of the venue, the postcode of each user as an attribute and a method for calculating the distance between postcodes so I think I have the data I need but I’m unsure how to proceed from here.
If anyone has something they’ve setup to do this I’d be most grateful if they could share this with me. Even some pointers as to how this may be done would be great.
Many thanks.
kaiatWTB
phpLister
 
Posts: 6
Joined: 1:57pm, Thu 13 Jan, 2011

Re: Personalised content based on postcode radius

Postby jonnya » 11:36pm, Fri 14 Jan, 2011

The way I've done it is with a zip code list that has latitude and longitude coordinates in it.
1. Process your subscriber list and apply the lat, lon data to each.
2. Run another process where you compare the lat, lon of your seminar to those on the list.
The ones that meet the criteria get marked for mailing.
5 digit zip lists are pretty loose as they may cover a few miles. So it might be 30 miles give or take. Here's a clip from an old foxpro program that still runs every day for a client. It's a simplified formula , but it has been doing the job.
Logic is logic...
Code: Select all
*================================
FUNCTION  distancecalc(lat1,lon1,lat2,lon2)
*================================
*Approximate distance in miles = sqrt(x * x + y * y)
*where
*x = 69.1 * (zip2.lat - zip1.lat)
*and     
*y = 69.1 * (zip2.lon - zip1.lon) * cos(zip1.lat/57.3)
x = 69.1 * ( VAL(lat2) - VAL(lat1) )
y = 69.1 * ( VAL(lon2) - VAL(lon1) ) * cos( VAL(lat1) /57.3)
RETURN sqrt(x * x + y * y)
ENDFUNC
jonnya
phpList newbie
 
Posts: 1
Joined: 11:15pm, Fri 14 Jan, 2011

Re: Personalised content based on postcode radius

Postby kaiatWTB » 9:45am, Tue 18 Jan, 2011

Thanks for the reply, that's helped. I think I've got a clear idea how to achieve this now.

1/ I need to set up a new attribute by creating a new field in the phplist_user_attributes table.
2/ Populate this field by running some sql based on above formula to give the address of the seminars within 30miles
3/ simply include this attribute in the footer of the newsletter

Need to get my sql hat on now :?
kaiatWTB
phpLister
 
Posts: 6
Joined: 1:57pm, Thu 13 Jan, 2011

Re: Personalised content based on postcode radius

Postby H2B2 » 12:43pm, Tue 18 Jan, 2011

Seems related to this contribution:
H2B2
Moderator
 
Posts: 7188
Joined: 1:51am, Wed 15 Mar, 2006

Re: Personalised content based on postcode radius

Postby kaiatWTB » 3:03pm, Wed 16 Feb, 2011

I'm almost there - just the last step that has me stumped! The update query I'm running at the end of my script dosen't actually insert any data? Again can anyone see what's wrong...

Code: Select all
   while($row = mysql_fetch_array($list))
   {
   $venue_pc=$row['venue_post_code'];
   $dist=distance($home_pc,$venue_pc);
   //only print out distances less than 30 miles
   if ($dist < 31)

      {
      $all_details= $row['sem_type'].' Seminar '.$row['sem_englishdate'].' at '.$row['venue_fullname'].' '.$row['venue_post_code'].' Tel '.$row['org_telephone'].' is '.$dist.' miles away
      ';
      $update = "UPDATE phplist_user_user_attribute SET value = ".$all_details." WHERE attributeid=21 AND userid = ".$userid." ";
      //next 2 lines for debug to see what update is sent
      echo $update;
      echo "<br/>";
      mysql_query($update);
      }
   }


I can see from the echo (see below) that the info is there but it is not getting written to the table?

UPDATE phplist_user_user_attribute SET value = Old Testament Seminar 25th Jun 2011 at Highwoods Methodist Church, Colchester CO4 9FF Tel 01206 242183 is 10 miles away WHERE attributeid=21 AND userid = 1
kaiatWTB
phpLister
 
Posts: 6
Joined: 1:57pm, Thu 13 Jan, 2011

Re: Personalised content based on postcode radius

Postby duncanc » 8:16am, Thu 17 Feb, 2011

You are missing extra single quotes around the value in the SET clause. I find it is a lot clearer to use interpolated variables in a double-quotes string instead of concatenating strings and variables together

Code: Select all
$update = "UPDATE phplist_user_user_attribute SET value = '$all_details' WHERE attributeid=21 AND userid = $userid";


You should have been getting a MySQL error message on your query, is the code checking that the insert was successful?
duncanc
Moderator
 
Posts: 2440
Joined: 6:34am, Sat 08 May, 2010
Location: London

Re: Personalised content based on postcode radius

Postby kaiatWTB » 10:27am, Fri 18 Feb, 2011

Thanks to everyone for all your help - I now have a working script which I've posted below in the hope it may be helpful for others.
Basically what it does is search through our list of Bible seminars and extract the details of seminars within 30 miles of each user on phplist. For my setup I added a new attribute called local_sems within phplist and this script updates the column value in table phplist_user_user_attribute where attributeid=21 with the details. I simply include [local_sems] in the footer of my default template and then every user gets a list of their local seminars in our monthly newsletter.

Code: Select all
<?php

   //overide 30sec php execution time limit
   set_time_limit(120);

   //check execution time of script
   $bgtime=time();

   //Include database connections details
   ####
   
   //Array to store validation errors
   $errmsg_arr = array();
   
   //Validation error flag
   $errflag = false;
   
   //Connect to mysql server
   ######

/*
Prepare postcodes by removing last 3 digits
the two vars will be user_pc for the phplist users postcode and sem_pc for the seminar venue postcode
*/

 function distance($user_pc_full,$sem_pc_full) {
 

#Convert the post code to upper case and trim the variable
$user_pc_full = strtoupper(trim($user_pc_full));
#Remove any spaces
$user_pc_full = str_replace(" ","",$user_pc_full);
#Trim the last 3 characters off the end
$user_pc = substr($user_pc_full,0,strlen($user_pc_full)-3);

#Convert the post code to upper case and trim the variable
$sem_pc_full = strtoupper(trim($sem_pc_full));
#Remove any spaces
$sem_pc_full = str_replace(" ","",$sem_pc_full);
#Trim the last 3 characters off the end
$sem_pc = substr($sem_pc_full,0,strlen($sem_pc_full)-3);
 
/*
query postcode table to assign lat and long values for these postcodes
*/

 $sql="SELECT * FROM bibleorg_WTBv2.postcodes WHERE Pcode = '$user_pc'";
 $result=mysql_query($sql) or die(mysql_error());
 $row=mysql_fetch_array($result);
 
 $user_pc_lat=$row['Latitude'];
 $user_pc_long=$row['Longitude'];
 
 
 $sql="SELECT * FROM bibleorg_WTBv2.postcodes WHERE Pcode = '$sem_pc'";
 $result=mysql_query($sql) or die(mysql_error());
 $row=mysql_fetch_array($result);
 
 $sem_pc_lat=$row['Latitude'];
 $sem_pc_long=$row['Longitude'];
 
 //echo "<br/>".$user_pc."<br/>  ".$user_pc_lat."<br/>  ".$user_pc_long;
 //echo "<br/>".$sem_pc."<br/>  ".$sem_pc_lat."<br/>  ".$sem_pc_long;
 
 $distance = getDistance($user_pc_lat, $user_pc_long, $sem_pc_lat, $sem_pc_long);

//echo "<br/>Distance:".$distance;

return $distance;
 
 }

/*
Calculate the distance between these 2 as the crows flies
using some maths I didn't figure out myself
This returns the varible dist_to_sem
*/

 function getDistance($lat1, $long1, $lat2, $long2){

#$earth = 6371; #km change accordingly
$earth = 3960; #miles

#Point 1 cords
$lat1 = deg2rad($lat1);
$long1= deg2rad($long1);

#Point 2 cords
$lat2 = deg2rad($lat2);
$long2= deg2rad($long2);

#Haversine Formula
$dlong=$long2-$long1;
$dlat=$lat2-$lat1;

$sinlat=sin($dlat/2);
$sinlong=sin($dlong/2);

$a=($sinlat*$sinlat)+cos($lat1)*cos($lat2)*($sinlong*$sinlong);

$c=2*asin(min(1,sqrt($a)));

$dist_to_sem=round($earth*$c);

return $dist_to_sem;

}
/*
First need to clear previous values by setting field to blank
*/

$clear = "UPDATE bibleorg_plst1.phplist_user_user_attribute SET value = '' WHERE attributeid=21";
mysql_query($clear);

/*
Select postcodes from bibleorg_plst1.phplist_user_user_attribute to use as home postcode then select all seminars within 30 miles off this postcode
*/
$query = mysql_query("SELECT userid, attributeid, value AS postcode
FROM bibleorg_plst1.phplist_user_user_attribute
WHERE attributeid=8 AND value <> ''
ORDER BY phplist_user_user_attribute.userid ASC");

while($row = mysql_fetch_array($query))
{
$home_pc=$row['postcode'];
$userid=$row['userid'];
//next lines just to check details
echo "<b>";
echo $row['userid'];
echo " local seminars are ";
echo "</b>";
echo "<br/>";

   /*
   Select current seminars from db
   */

   $list = mysql_query("SELECT seminars.seminar_id, seminars.sem_date, DATE_FORMAT(seminars.sem_date, '%D %b %Y') AS sem_englishdate, seminars.sem_type,
   seminars.venue_id, seminars.organiser_id, CONCAT(venue.venue_name,', ',venue.venue_town) AS venue_fullname, venue.venue_post_code, organiser.org_telephone
   FROM bibleorg_WTBv2.seminars
   LEFT JOIN bibleorg_WTBv2.venue ON seminars.venue_id=venue.venue_id
   LEFT JOIN bibleorg_WTBv2.organiser ON seminars.organiser_id=organiser.organiser_id
   WHERE seminars.sem_date >= (CURDATE()) AND venue.venue_post_code <>''") or die ('Error: '.mysql_error ());

   /*
   Loop through results calculating distance between postcode
   and the current seminars
   */

   while($row = mysql_fetch_array($list))
   {
   $venue_pc=$row['venue_post_code'];
   $dist=distance($home_pc,$venue_pc);
   //only select distances less than 30 miles
   if ($dist < 31)

      {
      $all_details= $row['sem_type'].' Seminar '.$row['sem_englishdate'].' at '.$row['venue_fullname'].' '.$row['venue_post_code'].' Tel '.$row['org_telephone'].' is '.$dist.' miles away
      ';
      $update = "UPDATE bibleorg_plst1.phplist_user_user_attribute SET value = CONCAT( value, '$all_details') WHERE attributeid=21 AND userid = '$userid' ";
      //next 2 lines for debug to see what update is sent
      echo $all_details;
      echo "<br/>";
      mysql_query($update);
      }
   }
}

mysql_close();
echo "Update complete";
Print "Execution Time: ".(time()-$bgtime);
?>


I'm sure there is probably a more efficient way of doing this and at some point I'd like to be able to call this from within phplist maybe as a plugin, but for the time being I'm simply running it manually before we send out our newsletter.

Once again thanks to everyone for all your help.
kaiatWTB
phpLister
 
Posts: 6
Joined: 1:57pm, Thu 13 Jan, 2011


Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 2 guests