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.


22 Comments » for Adding CJ Products To Your Datafeed Website
  1. Marion says:

    Nice script. Is it possible for this script to look for a particular keyword from an advertiser. Like say my advertiser has so much products but I only need those products that has iPhone in their title?

    thanks

  2. Eric Nagel says:

    Yes – check out the CJ Web Services help site. With the Product Catalog Search Service (REST), you can include “keywords”. The help file gives examples.

  3. amit says:

    is this the complete script? any link to download it? i created a database table based upon the code provided, and also created a connection, but nothing happens, do u have the full running script that u can email to me.

  4. Amrut Miskin says:

    Hi,,
    I am getting the following error upon trying to get the cj datafeed

    Warning: main() [function.main]: Node no longer exists in /home/waveban/public_html/yuvacart.com/test2/admin-cj-datafeed.php on line 78

    Warning: main() [function.main]: Node no longer exists in /home/waveban/public_html/yuvacart.com/test2/admin-cj-datafeed.php on line 78

    Warning: main() [function.main]: Node no longer exists in /home/waveban/public_html/yuvacart.com/test2/admin-cj-datafeed.php on line 92

    Warning: main() [function.main]: Node no longer exists in /home/waveban/public_html/yuvacart.com/test2/admin-cj-datafeed.php on line 92

    Kindly please help me out with the solution

    • Eric Nagel says:

      You’ll have to do some debugging to figure this out. After
      $cXML = simplexml_load_string($cHTML);

      Add
      echo($cHTML);
      exit();

      So you can see what CJ is returning. I’m guessing that’s the problem.

  5. Marivel says:

    Hi Eric
    Thank you very much i just have one problem when i put my CJ PID into
    Variable $cDevKey i get a message says Not Authenticated:
    So is that the field where i should put my PID or it should be something else

  6. Dan says:

    Hi

    Eric, one question:
    Your php works fine. I tried some merchants at CJ, one with over 200.000 products. But only 10.000 products were inserted into the table. Do you think its a restriction from my hosting service, your script or from CJ?

  7. Dan says:

    I found the answer: The 10000 limit is set by CJ.

  8. Dan says:

    Hi Eric
    Will a product be deleted in the database when a new datafeed is got and – this product is not in the new feed, (=not longer available at shop)?

    Thanks, Dan

    • Eric Nagel says:

      The product will be deleted.

      bActiveProduct is set to 0 before the data is processed. Then as products are updated (or inserted), bActiveProduct is set to 1.

      Finally, all records with bActiveProduct=0 are deleted.

      You could take out the delete statement, keep the product on your site, and just show an alternate message to the user (no longer in stock, or “Check for availability” which will at least get the cookie set)

  9. Mahendra says:

    Thanks for post such kind of post
    Hello i get the responce using the above code perfectly
    in that responce i got [advertiser-id],[advertiser-name],[buy-url],[description] and other field

    my question is i need coupon code,coupon desc,coupon start date,coupon end date,coupon type(whether it is deal or coupon)
    ‘s value so how can i get this value

    is there any idea than please give me a reply
    Thanks

    • Eric Nagel says:

      I was using the link-search API call and setting link-type=Text+Link and promotion-type=coupon then parsing through the results to pull out the fields you mentioned. However, I understand there’s another feed from CJ that gives the coupons, but I don’t know where it is.

  10. Sarita says:

    how can i get products for french version of CJ using the DEV key?I am already using one code to get products for UK.But when i simply sue the same code and replace the site id only then it gives no result for french.

  11. ocpjp says:

    Is there a way to search the CJ catalog only based on Advertiser SKU?

  12. Dan says:

    Hi
    Yes there’s a special CJ feed containing coupons. But this feed is full of non coupons, daily deals and other discounts with and without code. I think only 25% are real coupons. No, there’s no category column with splits the coupons from the other stuff. For me, this feed is not useable.

  13. vineesh says:

    Thaks for your support

    How i can change the 10000 limit is set by CJ.i want to get all product from the advertiser pls help me

    • Eric Nagel says:

      You can’t get more than 10,000 at a time, but you’ll see above I increment ‘page-number’ and loop through all of the products, so you’ll end up with more than 10,000 when it’s done.

      • Fer Vit says:

        after page-number=10 with records-per-page=1000, the api will return nothing, hence the mentioned 10k limit

Leave a Reply

Your email address will not be published. Required fields are marked *

*