Eric Nagel

I received an email last week asking how to add Commission Junction products to datafeed websites. To do this, take a look at the CJ Web Services. Particularly, the Product Catalog Search Service (REST).

What this does is allows you to specify a merchant’s ID, and get all of the product links for that merchant. In my example, I was grabbing the products from Mighty Leaf Tea.

So this PHP script will sit in your site’s root, like the other admin scripts, and when you pull it up, you’ll be presented with a form, asking for the Advertiser ID. That’s the AID when you’re pulling links, or advertiserId in the URL when you’re looking at the merchant in CJ. For Mighty Leaf Tea, it’s 2346375.

The biggest problem is that the CJ Web Service for Product Catalog Search doesn’t return the product ID! So I had to come up with a way to create an ID for a product. In this case, I based it on the SKU and ended up with:

list($nProductID, ) = explode('E', round(hexdec(md5($oCJProduct->{'sku'}))/1000000000000000000000000));
$nProductID = str_replace('.', '', $nProductID);
$nProductID = abs($nProductID);

If you don’t know what this is doing, don’t worry about it. If you’re bored, look-up those functions & let me know if you can think of a better way to make an ID from a sku. There has to be a simpler solution.

Anyway, because of this, I had to change the ProductID field to a bigint(20), since these item IDs were quite large (ex: 22229748315941). In addition, whenever the front-end scripts type-juggled the itemID into an (int), I had to remove that, as int’s are not big enough to hold this data (browse.php and item.php).

Getting back to the script, when I enter 2346375 in the form, then submit it, this script will query CJ for all product links for that merchant, insert or update them in the database, and finally remove any old products.

<?php
	include('./vars.php');
	include('./admin-password.php');
	$bNavHome = true;
	include('./header.php');

	$cTitle = 'Get CJ Datafeed';
?>
<div class="post">
<h2 class="title"><?= $cTitle ?></h2>
<div class="entry">
<?php

	if (!empty($_GET['advertiser-id'])) {
		$cDevKey = 'youridhere';
		$nWebSiteID = "yourwebsiteid";

		$nMax = 1;

		// We'll use this later to determine which products can be deleted
		mysql_query("update products set bActiveProduct=0 where MerchantID=" . (int)$_GET['advertiser-id'] . "");

		function addCJProduct($oCJProduct) {
			list($nProductID, ) = explode('E', round(hexdec(md5($oCJProduct->{'sku'}))/1000000000000000000000000));
			$nProductID = str_replace('.', '', $nProductID);
			$nProductID = abs($nProductID);

			if (mysql_num_rows(mysql_query("select * from products where ProductID=" . $nProductID . " and MerchantID=" . (int)$oCJProduct->{'advertiser-id'} . " limit 1")) == 0) {
				// This is a new record
				$cQuery = "insert into products (ProductID, Name, MerchantID, Merchant, Link, Thumbnail, BigImage, Price, RetailPrice, Description, Lastupdated, Manufacturer, PartNumber, ISBN, UPC, SKU, bActiveProduct) values (" . $nProductID . ", '" . myres($oCJProduct->{'name'}) . "', '" . myres($oCJProduct->{'advertiser-id'}) . "', '" . myres($oCJProduct->{'advertiser-name'}) . "', '" . myres($oCJProduct->{'buy-url'}) . "', '" . myres($oCJProduct->{'image-url'}) . "', '" . myres($oCJProduct->{'image-url'}) . "', '" . myres($oCJProduct->{'price'}) . "', '" . myres($oCJProduct->{'retail-price'}) . "', '" . myres($oCJProduct->{'description'}) . "', now(), '" . myres($oCJProduct->{'manufacturer-name'}) . "', '" . myres($oCJProduct->{'sku'}) . "', '" . myres($oCJProduct->{'isbn'}) . "', '" . myres($oCJProduct->{'upc'}) . "', '" . myres($oCJProduct->{'sku'}) . "', 1)";
			} // ends <insert new record>
			else {
				// This is an existing record
				$cQuery = "update products set Name='" . myres($oCJProduct->{'name'}) . "', MerchantID=" . (int)$oCJProduct->{'advertiser-id'} . ", Merchant='" . myres($oCJProduct->{'advertiser-name'}) . "', Link='" . myres($oCJProduct->{'buy-url'}) . "', Thumbnail='" . myres($oCJProduct->{'image-url'}) . "', BigImage='" . myres($oCJProduct->{'image-url'}) . "', Price='" . myres($oCJProduct->{'price'}) . "', RetailPrice='" . myres($oCJProduct->{'retail-price'}) . "', Description='" . myres($oCJProduct->{'description'}) . "', Lastupdated=now(), Manufacturer='" . myres($oCJProduct->{'manufacturer-name'}) . "', PartNumber='" . myres($oCJProduct->{'sku'}) . "', ISBN='" . myres($oCJProduct->{'isbn'}) . "', UPC='" . myres($oCJProduct->{'upc'}) . "', SKU='" . myres($oCJProduct->{'sku'}) . "', bActiveProduct=1 where ProductID=" . $nProductID . " and MerchantID=" . (int)$oCJProduct->{'advertiser-id'} . " limit 1";
			} // ends else from <updating existing record>
			mysql_query($cQuery);
			if (mysql_error()) {
				echo("<p><b>MySQL Error: " . mysql_error() . "<br />\n");
				echo("$cQuery</p>");
				echo("<pre>");
				print_r($rsItem);
				echo("</pre>");
				exit();
			} // ends if (mysql_error())

		} // ends function addCJProduct($oCJProduct)

		for ($nPageNumber = 1; $nPageNumber <= $nMax; $nPageNumber++) {

			$cURL = 'https://product-search.api.cj.com/v2/product-search?';
			$cURL .= 'advertiser-ids=' . $_GET['advertiser-id'] . '&';
			$cURL .= 'records-per-page=1000&';
			$cURL .= 'page-number=' . $nPageNumber . '&';
			$cURL .= 'website-id=' . $nWebSiteID;

			$ch = curl_init();
			curl_setopt($ch, CURLOPT_URL, $cURL);
			curl_setopt($ch, CURLOPT_HTTPHEADER, array(
						  'Authorization: ' . $cDevKey,
						  'User-Agent: "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.15) Gecko/2009101601 Firefox/3.0.15 GTB6 (.NET CLR 3.5.30729)"'
						));

			curl_setopt($ch, CURLOPT_HEADER, false);
			curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

			curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 10);
			curl_setopt($ch, CURLOPT_TIMEOUT, 10);

			$cHTML = curl_exec($ch);
			if (curl_error($ch)) {
				echo "Curl error: " . curl_error($ch);
			} // ends if (curl_error($ch))
			else {
				$cXML = simplexml_load_string($cHTML);

				// Update nMax
				if ($cXML->products->attributes()->{'total-matched'} > 1000) {
					$nMax = floor($cXML->products->attributes()->{'total-matched'}/$cXML->products->attributes()->{'records-returned'});
				} // ends if ($cXML->products->attributes()->{'total-matched'} > 1000)

				for ($i = 0; $i < count($cXML->products->product); $i++) {
					addCJProduct($cXML->products->product[$i]);
				} // ends

			} // ends else from if (curl_error($ch))
		} // ends for loop

		mysql_query("delete from products where bActiveProduct=0 and MerchantID=" . (int)$_GET['advertiser-id'] . "");

		echo("<p><strong>" . $cXML->products->attributes()->{'total-matched'} . "</strong> products have been loaded.</p>");

		echo("<p>There are " . mysql_num_rows(mysql_query("select * from products where MerchantID=" . (int)$_GET['advertiser-id'] . "")) . " products.</p>");
	} // ends if (!empty($_GET['advertiser-id']))
	else {
		// Show form
		?>
		<form method="get" action="<?= $_SERVER['PHP_SELF'] ?>">
			CJ Advertiser ID: <input type="text" name="advertiser-id" /><br />
			<input type="submit" name="cAction" value="Get Datafeed" />
		</form>
		<?php
	} // ends else from if (!empty($_GET['advertiser-id']))
?>

</div>
</div><!-- ends class="post" -->

<p align="center">[ <a href="<?= $_SERVER['PHP_SELF'] ?>"><?= $cTitle  ?> Home</a> | <a href="./admin.php" target="_top">Admin Home</a> ]</p>
<?php
	include('./footer.php');
?>

Because CJ products do not include categories, I had to change my browse.php to search the product title for the category (green, black, white). To create a quality site, however, I recommend you put products in their appropriate category using the custom category script.

You’ll notice I output 2 numbers at the end – the first is how many products were returned, and the second is how many were loaded into the database. For Mighty Leaf Tea, it loaded 413 products, but only 410 were in the database when I was done. Either 3 products had duplicate SKUs, or 3 products ended up with duplicate ProductIDs after my process was done to generate a ProductID from SKU. I’m not sure, but 410/413 is pretty good, so I left it alone.

This script was really hacked together – let me know if you have questions / problems.