Money-Code

Coding For Online Success

Simple script for connecting to Commission Junction’s Product Web Service and populating a local database

| 17 Comments

Today’s post, I’m going to get back to code.. about time I would say. I thought it would be cool to talk about a way to populate a local database with Commission Junction products. Then you could display those products on your store front. I like to pre-populate a store and use a local database versus hitting a web service so I can speed the experience for the visitor. Web service calls always have a pause, and CJ’s seemed a little slow.

Things we’re going to use to pull this off. You’ll need to make sure all of these requirements are met.

  • PHP
  • MySQL
  • NuSOAP client (for sending/receiving web service requests) — PHP5 users are spoiled with better functionality, and this will not be required, but you’re on your own for sending/receiving the XML, this tutorial covers PHP4 only. You can download NuSOAP here (http://dietrich.ganx4.com/nusoap/)
  • CJ Developer’s web key (http://webservices.cj.com/)

This tutorial will discuss receiving and populating a products database (for example every hour) to maintain a fresh product list from a CJ merchant. When I initially created this I wrote it as a CLI (Command LIne script, where cron was running it every hour). If you plan on using this as a straight up web script, ensure you’ve limited access to it (usually only the web server or your IP only). Another security consideration, this script uses mysql_real_escape_string(), if you’re environment does NOT use SAFE_MODE then you’ll need to add stripslashes() inside of that function.

 

How CJ web services work is pretty simple. Based off of your PID which you pass to them, you’re able to search for products from all merchants associated with your account. Obviously, you’ll want to be able to search only from a few merchants, so you might want to do an initial search for products (please see http://webservices.cj.com/ and read the API Reference and Help section). From there you’ll be able to see ‘AdvertiserIDs’, once you have that, you’ll be able to narrow your search down. I haven’t seen a better way to get this, and if you know.. please tell me!

First let’s create our DB table:

 

Let’s start our script by setting up some important variables (db connection string, PID and developer key)

span class=”st0″>"localhost";
$DBname                 = "mydatabase";
$DBuser                 = "mydbuser""mypassword""00930cb6218da857ea6mysuperlongdeveloperkey64bce9920be2219bc1";
$websiteIdx     = "0000000"; // this is CJ PID for my website.
 

Next we need to include our NuSOAP class files

span class=”st0″>"nusoap/nusoap.php");
 

Let’s set up connection to our database

span class=”st0″>"Error Connecting to the Database."

Here we’re setting up some important web service variables on the products I’m looking for:

span class=”st0″>’widgets’;
$LowPrice                       = 0;
$HighPrice                      = 1000;
$AdvertiserIDs          = 123,5566;     // this is the advertiserIDs I was talking about. They can be comma delimited when adding more than one
 

Now, it’s time to make our call to the web service! The code below will assemble a proper web service request and connect to CJ. It will then receive a ‘response’ and set them up as local variables for us.

span class=”st0″>"https://api.cj.com/wsdl/productSearchService.wsdl", ‘wsdl’"developerKey""websiteId" => $websiteIdx,
               "advertiserIds" => $AdvertiserIDs,
               "keywords""serviceableArea" => "",
               "upcOrIsbnOrEan" => "",
               "manufacturerName" => "",
               "advertiserSku" => "",
               "lowPrice" => $LowPrice,
               "highPrice" => $HighPrice,
               "currency" => "",
               "sortBy" => "",
               "orderIn" => "",
               "startAt" => 0,
               "maxResults"‘searchReturn’][‘count’];
 

If there are records returned, I’m going to empty the database products table(you can do what you want ie: UPDATE, check for changes, etc). Once the DB table is empty I re-populate it with current data. I run this script once a day.

span class=”co1″>// you could increase this amount to 5 or 10 to ensure that you’re getting a good product list.
        $sql    = "DELETE FROM tblProducts;
        if(!mysql_query($sql,$connect)){
                echo "
DB error – 1.0";
                exit();
        }
        for($i = 0; $i < $count; $i++){
                $advertiser_id          = $result[‘searchReturn’][‘products’][‘products’][$i][‘advertiserId’];          
                $advertiser_name        = $result[‘searchReturn’][‘products’][‘products’][$i][‘advertiserName’];
                $click_url                      = $result[‘searchReturn’][‘products’][‘products’][$i][‘clickUrl’];
                $currency                       = $result[‘searchReturn’][‘products’][‘products’][$i][‘currency’];
                $description            = $result[‘searchReturn’][‘products’][‘products’][$i][‘description’];
                $image_url                      = $result[‘searchReturn’][‘products’][‘products’][$i][‘imageUrl’];
                $name                           = $result[‘searchReturn’][‘products’][‘products’][$i][‘name’];
                $price                          = $result[‘searchReturn’][‘products’][‘products’][$i][‘price’];
                $sku                            = $result[‘searchReturn’][‘products’][‘products’][$i][‘sku’];
                $upc                            = $result[‘searchReturn’][‘products’][‘products’][$i][‘upc’];
                echo $name."
<br>";// debug
                $sql                    = "
INSERT INTO tblProducts(ID, AdvertiserID, AdvertiserName, ClickURL,
                                                Currency, Description, ImageURL, Name, Price, SKU, UPC)
                                                VALUES(‘".substr(md5(uniqid(rand(),1)),0,8)."’,
                                                ‘".mysql_real_escape_string($advertiser_id)."’,
                                                ‘".mysql_real_escape_string($advertiser_name)."’,
                                                ‘".mysql_real_escape_string($click_url)."’,
                                                ‘".mysql_real_escape_string($currency)."’,
                                                ‘".mysql_real_escape_string($description)."’,
                                                ‘".mysql_real_escape_string($image_url)."’,
                                                ‘".mysql_real_escape_string($name)."’,
                                                ‘".mysql_real_escape_string($price)."’,
                                                ‘".mysql_real_escape_string($sku)."’,
                                                ‘".mysql_real_escape_string($upc)."’)";
                if(!mysql_query($sql,$connect)){
                        echo "
DB error 1.1";
                        exit();
                        //echo mysql_error()."
<br>";
                        //echo $sql."
<br>";
                }
        }
}
unset($count);
unset($result);
unset($proxy);
unset($params);

When I’m all done, I like to optimize the table.

$sql                    = "OPTIMIZE TABLE `tblProducts`""Database Error"

View the full the script

This script was intended to be a ‘worker’ script, not accessible by the public but run only from cron internal to re-populate my database table. You definitely could use some of the programming above to make real time calls, etc. If you want real time search, make sure you sanitize search keyword with htmlspecialchars(strip_tags()). Keywords are somewhat weird, you’ll want to refer to the API documentation on this, since you can have positive and negative keywords to help refine your search.

Again, make sure that this script is not accessible to your visitors since you don’t want robots or malicious users hitting this script and potentially abusing CJ’s web service or corrupting your products database table.

17 Comments

Leave a Reply

Required fields are marked *.


CommentLuv badge

This site uses Akismet to reduce spam. Learn how your comment data is processed.