Money-Code

Coding For Online Success

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

| 18 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.

if(get_magic_quotes_gpc()){
        $retVal         = mysql_real_escape_string(stripslashes($retVal));
}else{
        $retVal         = mysql_real_escape_string($retVal);
}
 

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:

CREATE TABLE `tblProducts` (
  `ProductID` int(11) NOT NULL auto_increment,
  `ID` varchar(8) NOT NULL,
  `AdvertiserID` int(11) NOT NULL,
  `AdvertiserName` varchar(200) NOT NULL,
  `ClickURL` varchar(255) NOT NULL,
  `Currency` varchar(200) NOT NULL,
  `Description` longtext NOT NULL,
  `ImageURL` longtext NOT NULL,
  `Name` varchar(255) NOT NULL,
  `Price` decimal(10,2) NOT NULL,
  `SKU` varchar(200) NOT NULL,
  `UPC` varchar(200) NOT NULL,
  PRIMARY KEY  (`ProductID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
 

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

<?
$DBserver               = "localhost";
$DBname                 = "mydatabase";
$DBuser                 = "mydbuser";
$DBpassword     = "mypassword";
$developerkey   = "00930cb6218da857ea6mysuperlongdeveloperkey64bce9920be2219bc1";
$websiteIdx     = "0000000"; // this is CJ PID for my website.
 

Next we need to include our NuSOAP class files

require_once("nusoap/nusoap.php");
 

Let’s set up connection to our database

if(!($connect = mysql_connect($DBserver, $DBuser, $DBpassword))){
        echo "Error Connecting to the Database.";
        exit();
}
$DB = mysql_select_db($DBname);
 

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

$Keywords                       = ‘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.

$soapclient             = new soapclient("https://api.cj.com/wsdl/productSearchService.wsdl", ‘wsdl’);
$params = array(
               "developerKey" => $developerkey,
               "websiteId" => $websiteIdx,
               "advertiserIds" => $AdvertiserIDs,
               "keywords" => $Keywords,
               "serviceableArea" => "",
               "upcOrIsbnOrEan" => "",
               "manufacturerName" => "",
               "advertiserSku" => "",
               "lowPrice" => $LowPrice,
               "highPrice" => $HighPrice,
               "currency" => "",
               "sortBy" => "",
               "orderIn" => "",
               "startAt" => 0,
               "maxResults" => 150
);
$proxy          = $soapclient->getProxy();
$result         = $proxy->search($params);
$count          = $result[‘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.

if($count > 0){ // 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`";
if(!mysql_query($sql,$connect)){
        echo "Database Error";
        exit();
}
?>
 

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.

Incoming search terms:

  • commission junction php script
  • commission junction api php class
  • commission junction script
  • commission junction api script
  • commission junction api example
  • cj api php
  • commission junction api php
  • cj api script
  • cj api php script
  • how to input commission junction FTP files to sql

18 Comments

  1. Make sure you’re only declaring it once. Do you have this line multiple times?

    $soapclient = new soapclient

    This line should be listed only once throughout the script.

    hanji

  2. i using this script but i face this problem plz help me out
    Fatal error: Cannot redeclare class soapclient in C:\wamp\www\context\lib\nusoap.php on line 7240
    thanks
    regards
    ahtsham asghar

  3. Hi,
    I opened the table on the DB, install the nuSOAP files on the root folder like the source file, and updated the source file.

    Where can i get the advertiser id from? that’s all i need to run this… i hope.

    Thanks again :)

  4. Nice post but… I have tried your code but seems not working since cj has changed to productSearchServiceV2… correct me if i’m wrong.. wait for your next post.. i need it ;)

  5. Hello

    Thanks for posting! This might help.. I just wrote this post today…

    http://www.money-code.com/node/61

    HTH
    hanji

  6. I get this error when I run your script!!!

    Fatal error: Call to undefined function: search() html/cj-api-example.php on line 42

    line 42 is:

    $proxy = $soapclient->getProxy();
    (line 42) $result = $proxy->search($params);
    $count = $result['searchReturn']['count'];

    can you please help?

    Thanks,

    Antonio

  7. Whoa. That is a weird error. Are you connecting to version 1 wsdl? It’s basically saying that search() is not an available function in the web service? I have a feeling you might be trying to connect to version 2?

    When I go:
    https://api.cj.com/wsdl/productSearchService.wsdl

    I see ‘search’ as being available.
    hanji

  8. Hanji -

    Thanks for your help. I replaced the path with what you suggested and I didnt get that error. however it still does not work.

    I get this error now:
    Parse error: parse error, unexpected T_STRING html/cj-api-example.php on line 48

    here is line 48:
    (line 48) $sql = “DELETE FROM tblProducts;
    if(!mysql_query($sql,$connect)){
    echo “DB error – 1.0″;
    exit();

    I was assuming that there needs to be a ” (quote) after the word tblProducts to close the query. I tried inserting a quote after the word tblProducts and when I ran the script, it did not give me an error but it did not populate the tblProducts database at all. Nothing happaned at all

    here is my entire code:

    < ?
    $DBserver = "databasename (i replaced it with mine)";
    $DBname = "dbname (i replaced it with mine)";";
    $DBuser = "dbuser (i replaced it with mine)";";
    $DBpassword = "dbpasword(i replaced it with mine)";";
    $developerkey = "I added my developer key";
    $websiteIdx = "i added my website id"; // this is CJ PID for my website.

    require_once("nusoap/nusoap.php");

    if(!($connect = mysql_connect($DBserver, $DBuser, $DBpassword))){
    echo "Error Connecting to the Database.";
    exit();
    }
    $DB = mysql_select_db($DBname);

    $Keywords = 'rings';
    $LowPrice = 0;
    $HighPrice = 10000;
    $AdvertiserIDs = "1496"; // this is the advertiserIDs I was talking about. They can be comma delimited when adding more than one

    $soapclient = new soapclient("i added the link you suggested here", 'wsdl');
    $params = array(
    "developerKey" => $developerkey,
    “websiteId” => $websiteIdx,
    “advertiserIds” => $AdvertiserIDs,
    “keywords” => $Keywords,
    “serviceableArea” => “”,
    “upcOrIsbnOrEan” => “”,
    “manufacturerName” => “”,
    “advertiserSku” => “”,
    “lowPrice” => $LowPrice,
    “highPrice” => $HighPrice,
    “currency” => “”,
    “sortBy” => “”,
    “orderIn” => “”,
    “startAt” => 0,
    “maxResults” => 150
    );
    $proxy = $soapclient->getProxy();
    $result = $proxy->search($params);
    $count = $result['searchReturn']['count'];

    if($count > 0){ // 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."
    “;// 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().”
    “;
    //echo $sql.”
    “;
    }
    }
    }
    unset($count);
    unset($result);
    unset($proxy);
    unset($params);

    $sql = “OPTIMIZE TABLE `tblProducts`”;
    if(!mysql_query($sql,$connect)){
    echo “Database Error”;
    exit();
    }
    ?>

  9. Hello

    Glad you’re getting closer. The error is related to the missing quote in the sql string..

    $sql = “DELETE FROM tblProducts;

    Should be:

    $sql = “DELETE FROM tblProducts”;

    hanji

  10. Thanks Hanji –

    One last thing – the code runs without any errors but the tbl_Products table in my database is empty? I can not seem to figure out why. If it could not connect or if there was an error with the code, wouldnt MySQL display some sort of error message?

    Please advise.

    Thank you once again for your help!

  11. Nothing in the database table would probably be the result of 0 records returned from your API query. I would echo $count to verify that the loop is turning with INSERTs. If count is 0, then nothing would be inserted.

    hanji

  12. Hi Hanji!
    I get XML files from several stores via FTP, after that i put data to my mysql. My question is – Should i include key developer code to PHP file? Everthing happens on my server, why should i put DK?

  13. I have some other questions. by the way, thanks a lot for great post about CJ.

  14. Hanji, there is more important question and if u can answer me that would be awesome.
    I want to pass my value in buyUrl http://www.kfgzyfj.com/hg116xdmjdl0484669502163A857&customer_id=235434654
    So &customer_id=235434654 is one. When i get Commission Detail Service ReST or Soap report, do they send me my values? do u understand what i’m talking about? :)

  15. Hello Dima

    First question: I get XML files from several stores via FTP, after that i put data to my mysql. My question is – Should i include key developer code to PHP file? Everthing happens on my server, why should i put DK?

    My example was using Nusoap to get the feed. Since you’re pulling them directly from FTP, you will NOT need DK on the server, since you already have the data and parsing it in your database.

    I’m definitely confused on your second question: Hanji, there is more important question and if u can answer me that would be awesome.
    I want to pass my value in buyUrl http://www.kfgzyfj.com/hg116xdmjdl0484669502163A857&customer_id=235434654
    So &customer_id=235434654 is one. When i get Commission Detail Service ReST or Soap report, do they send me my values? do u understand what i’m talking about?

    I’m unfamiliar with Commission Detail Service REST. Are you pulling reports from their API?

  16. Thank a lot for your attention Hanji! As i understood from i read there i can get reports for all deals and commission, very detailed reports.
    I wanted to get answer from their forum, that is very empty.
    So…
    I want to give some values with their link and get that value when they send a report via XML to me.
    So is that posable or not? hmmm

  17. I am getting no results in the db when i run this script. I echo count and got nothing. The advertisers id that i inputted is correct. Do you have any suggestions on what else I could try?

  18. since you already have the data and parsing it in your database.The development of dynamic Web pages often use a database to store information lead to database access to frequently refresh the page, greatly consuming system resources.

Leave a Reply

Required fields are marked *.


CommentLuv badge