Today I Searched how to make an AJAX call to a URL on a different domain. My first attempt failed with a security error. I dug into the problem a bit and found out that PHP can be used as a proxy to fetch the web content.

AJAX Calls PHP Script On Your Domain -> PHP Script Fetches Content From Remote Domain -> PHP Script Returns The Remote Data Back To The AJAX Call

Below is a sample PHP script that I found and tweaked.

// Set your return content type
header('Content-type: text/html');

// Website url to open
$daurl = 'http://www.otherdomain.com/index.php?sku=' . $_GET["sku"];

// Get that website's content
$handle = fopen($daurl, "r");

// If there is something, read and return
if ($handle) {
    while (!feof($handle)) {
        $buffer = fgets($handle, 4096);
        echo $buffer;
    }
    fclose($handle);
}

Below is the AJAX call to load the content into a div using JQuery

$("#loadremotecontent").load('getprice.php?sku=322000');

The script could be further modified to pass in the entire URL as a parameter so it could be more generic. I would love to hear about other cross domain AJAX options if anyone has any other suggestions.

Today I searched how to allow free shipping for a specific shopper group in the Virtuemart shopping cart. There was a shopper group extension in development for this, but it does not work with our 1.1 version of Virtuemart.

I decided to dive into the source code and tackle the problem on my own. Below are the details of how to implement this on your Virtuemart system should you have need.

The first thing I did was added a column to the shopper group table. This will flag the shopper group to have free shipping in the cart if you so desire.

/*You may have to alter the table name if your prefix does not start with jos_vm_ which is the default*/
ALTER TABLE `jos_vm_shopper_group` ADD COLUMN `shopper_group_freeshipping` TINYINT(1) UNSIGNED AFTER `default`;

There is on PHP file that must be edited. It is located in the following folder under your Joomla installation directory.
\administrator\components\com_virtuemart\classes\ps_checkout.php

At or around line 63 you will see the following if statement

if($vendor_freeshipping > 0 && $vars['order_subtotal_withtax'] >= $vendor_freeshipping) {

That line needs to be replaced with the following

		$db = new ps_DB();
		$q = 'SELECT shopper_group_freeshipping from #__{vm}_shopper_vendor_xref xref
			inner join #__{vm}_shopper_group shopper on xref.shopper_group_id = shopper.shopper_group_id
			where xref.user_id = ' . $_SESSION['auth']["user_id"];

		$db->query($q);
		$db->next_record();
		$free_shoppergroup_shipping = $db->f('shopper_group_freeshipping') == 1;
		//original line at 63
		if(($vendor_freeshipping > 0 && $vars['order_subtotal_withtax'] >= $vendor_freeshipping) || $free_shoppergroup_shipping) {

A little bit further down there is this if statement

if(empty($_REQUEST['ship_to_info_id']) && ps_checkout::noShipToNecessary()) {

$db = new ps_DB();

You can comment out or remove the $db = new ps_DB();. It is now already being instantiated due to our new code.

There is nothing else that needs to be changed. You will have to manually flag shopper groups in the db using the new shopper_group_freeshipping column that was added. This should have a value of 1 for any shopper groups that need to get free shipping. All other groups can have a value of NULL or 0 to get the standard shipping you have configured in your installation.

I will make another post if I tackle adding a checkbox on the shopper group to update this field. If anyone else does it please let me know and I will post your code or provide a link to your blog.

Today I searched on how to get the city of a user based on their ip address. I was looking to do this using PHP and preferably for free. There was quiet a bit out there that had a free database you could download to cross reference that would return the country. However, we wanted to be able to get the city and state as well. After quiet a bit of googling, I came across a free API from ipgp.net. They allow you to post the ip to a URL and they return an XML packet with the county, a path to the image of the country’s flag, city, state, isp, latitude, and longitude. They have an example on how to do this on their page. We created our own class below for your use that is a little cleaner. It can be included in your php scripts and you don’t have to worry about downloading anything into MySQL.

	/*
		Provided by http://www.todayisearched.com
	*/
	class GeoIP {

		/*
			Returns all possible Geo Ip information in an associative array.  The following pieces of information
			are returned:
			IP - IP ADDRESS LOOKED UP
			CODE - COUNTRY CODE
			COUNTY - COUNTRY NAME
			FLAG - PATH TO IMAGE OF THE COUNTRY'S FLAG
			CITY - CITY NAME
			REGION - STATE NAME
			ISP - ISP NAME
			LAT - LATITUDE CORDINATE
			LNG - LONGITUDE CORDINATE

			USAGE:
			$ipinfo = GeoIP :: getGeoArray('xxx.xxx.xxx.xxx');
			echo $ipinfo['CITY'] . ', ' . $ipinfo['STATE'];
		*/
		public static function getGeoArray($ip) {
			$file = "http://www.ipgp.net/api/xml/". $ip;

			$xml_parser = xml_parser_create();
			$fp = fopen($file, "r");
			$data = fread($fp, 80000);
			xml_parse_into_struct($xml_parser, $data, $vals);
			$iplookup = array();
			foreach ($vals as $v) {
				if (isset($v['tag']) && isset($v['value'])) {
					$iplookup[$v['tag']] = $v['value'];
				}
			}
			xml_parser_free($xml_parser);
			fclose($fp);

			return $iplookup;
		}

		//shortcut to get the city name
		public static function getCity($ip) {
			$a =  self :: getGeoArray($ip);
			return $a['CITY'];
		}

		//shortcut to get the state name
		public static function getState($ip) {
			$a =  self :: getGeoArray($ip);
			return $a['REGION'];
		}

	}

Follow these steps to implement the above script:

1. Copy the script above and save it into a php file on your server
2. Add include_once(‘Path To The PHP File Saved On #1′);
3. Use the code snippet below in your php to obtain geoip information.

      include_once "geoip.php";
      $ipinfo = GeoIP :: getGeoArray($_SERVER['REMOTE_ADDR']);
      echo 'Hello, Your City And State Is ' . $ipinfo['CITY'] . ', ' . $ipinfo['REGION'];

Thanks http://www.ipgp.net for providing this invaluable service!

Today I searched how to write out an Excel file with PHP.  Keep in mind that this is being written by a PHP noob and I am a Java developer by trade.  This was my first project being built in PHP from the ground up, prior to this my only experience was tweaking a couple of our existing open source PHP apps.

OK, to the point….

My co-worker nalmf and I were searching for a library at the same time.  I actually have to give him the credit for stumbling across the solution we ended up using.  I will start by listing the 2 that I found and why we did not end up using them.

• Pear Spreadsheet_Excel_WriterThis one looked really promising.  I had heard of Pear before but know nothing about it really.  I was looking for a library that I could just drop into our project and immedietly start working with it doing little or no configuration.  I took a peak at installing Pear and it seemed like more work than I wanted to do at the time.  This is for a very small project.  If you already have Pear or are familiar with how to install it, it appears this could be a very viable solution.
• PHP Classes : MS-Excel Stream HandlerThis gives you a very bare bones way to dump data into Excel.  It provides no means to format the cell and no formulas.  I did not have need to use formulas for this project, but I thought I would throw that in there.  I came close to using this one and just have the user bold the header and freeze the top and left column.  I did have to make a couple of fixes to the source.  The script is broken by default on windows systems.  It drops out the ” : ” in the path to the Excel file to be written.  I found a fix for it in their forum.  There was also a $size variable that did not get initialized before it was read.  I initialized it to 0 before it attempted to do a += on it in a for loop.  I have already deleted my modifications to the source before posting this.  If anyone is interested in using it and needs help they can leave a comment.  I did not feel safe using the script in production without thorough testing.  I will say that it did write the Excel file out very fast for my 2,000 record test.  It was done in less than a second and I had about 5 columns of data.
• PHPExcelThis is the library we ended up going with.  It does not have any dependencies, which was really nice, and I was able to drop it right into our project include it and run with it.  It is very extensive and can even create Excel 2007 documents.  I was looking to create an Excel 2000 compatible document, which it easily handled as well.  I was able to bold our headers and it handles the freeze pane as well. I was able to freeze the top header row and the left column which contained some time slots.  Below is the code I used to extract the data out of our db and dump it into Excel.  This library is very well documented and looks as if it will handle any of our PHP / Excel  needs for the future.

require_once $_SERVER["DOCUMENT_ROOT"] . '/classes/PHPExcel.php';
require_once $_SERVER["DOCUMENT_ROOT"] . '/classes/PHPExcel/IOFactory.php';
require_once($_SERVER["DOCUMENT_ROOT"] . '/classes/database.php');
/* Here there will be some code where you create $objPHPExcel */
// redirect output to client browser
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="appointment.xls"');
header('Cache-Control: max-age=0');

$objPHPExcel = new PHPExcel();
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();
	try {
		$dbh = Database :: getConnection();

		$sql = 'select *,DATE_FORMAT(appoint_dob, \'%m/%d/%Y\') dobFormat from appoint a
				inner join onehourslots h on a.appoint_hour = h.onehourslots_hour
				left outer join language l on a.language_no = l.language_no
				left outer join sick s on a.sick_no = s.sick_no
				left outer join ethnicity e on a.ethnicity_no = e.ethnicity_no
				where a.event_no = :eventNo order by a.appoint_hour';

		//setup headers
		$sheet->setCellValue('A1', "Time");
		$sheet->setCellValue('B1', "First");
		$sheet->setCellValue('C1', "Last");

		$stmt = $dbh -> prepare($sql);
		$eventNo = $_GET['event_no'];
		$stmt -> bindValue(':eventNo', $eventNo, PDO :: PARAM_INT);
		$stmt -> execute();

		$i = 2;
		while ($r = $stmt->fetch()) {
			$sheet->setCellValue('A' . $i, $r['onehourslots_display']);
			$sheet->setCellValue('B' . $i, $r['appoint_first']);
			$sheet->setCellValue('C' . $i, $r['appoint_last']);
			$i ++;
		 }

		$sheet->getStyle('A1')->getFont()->setBold(true);
		$sheet->getStyle('B1')->getFont()->setBold(true);
		$sheet->getStyle('C1')->getFont()->setBold(true);

		//freeze the top header row and the left time column
		$sheet->freezePane('B2');			

		$dbh = null;
	} catch(PDOException $e) {
		echo $e -> getMessage();
		$dbh = null;
	}

$objWriter->save('php://output');