Results 1 to 11 of 11
  1. #1
    Newbie
    Join Date
    November 13th, 2006
    Posts
    14
    Please help me Parse Reports (for cashback system)
    I'm using PHP and I would like to parse CSV reports (with SID info).

    This is what the reports look like:
    http://help.cj.com/en/lo*@))p/Publisher_Help_v2.htm#SmartRewards_Overview.htm

    Has anyone ever written a php script that extracts the information and then stores it in a database?

    Anyone have some advice? I'm trying to provide a cashback system so any advice on how to calculate how much I owe each user would be greatly appreciated :-)

  2. #2
    ABW Ambassador MoneyBusiness's Avatar
    Join Date
    March 14th, 2006
    Posts
    2,051
    Plenty of people import data from files into databases. Check out: http://forum.abestweb.com/showthread...ht=bulletproof

    It's pretty much all there - from importing the datafeed, to implementing one.

    Good luck.

  3. #3
    Member HarveyC's Avatar
    Join Date
    August 19th, 2006
    Location
    UK
    Posts
    113
    I think the user was referring to parsing aff. sales reports.

    I've tried (and failed) with something that converts a CSV into an array. Let me know if you find anything!

  4. #4
    ABW Ambassador MoneyBusiness's Avatar
    Join Date
    March 14th, 2006
    Posts
    2,051
    Sorry about that. Didn't read it correctly! However, you should still be able to use the same info to pull a CSV file in, parse it with PHP, and import it into a DB (as long as it's delimited properly). Not too terribly different than working with a product datafeed.

  5. #5
    Newbie
    Join Date
    November 13th, 2006
    Posts
    14
    Thanks for your reply guys :-)

    yes, I actually meant parsing aff. sales reports.

    I'm sure it's possible since all cashback websites do it
    I'll look into it and let you know if i manage to do it, but I'm also hoping someone on this forum knows how

  6. #6
    ABW Ambassador MoneyBusiness's Avatar
    Join Date
    March 14th, 2006
    Posts
    2,051
    I pulled a section from one of my scripts. This is a very basic example, and can get much more complicated. Also, this assumes you already created a table via mySQL (you'll need to adjust the field names accordingly).

    Code:
    $dbh=mysql_connect ("localhost", "username", "password") or die ('I cannot connect to the database because: ' . mysql_error()); 
    mysql_select_db ("database"); 
           
    // File can be anywhere on the Internet 
    $FeedFile = 'http://www.domainname.com/datafeed.txt'; 
    
    $feed = fopen($FeedFile, 'r'); 
    
    while($data = fgetcsv($feed, 5000, "|")){ 
    
            $last_updated = addslashes($data[0]); 
            $name = addslashes($data[1]); 
            $category = addslashes($data[2]); 
            $keywords = addslashes($data[3]); 
            $short_description = addslashes($data[4]); 
            $long_description = addslashes($data[5]); 
    	$sku = addslashes($data[6]);
    
            $sql = mysql_query("insert into yourtablename (last_updated, name, category, maincat, seccat, keywords, short_description, long_description, sku) values ('$last_updated', '$name', '$category', '$mainCat', '$secCat', '$keywords', '$short_description', '$long_description', '$sku')") or die(mysql_error()); 
            
    
    }
    
    
    fclose($feed); 
    mysql_close($dbh);

    HOpe that sheds some light. If you need an example of table creation via SQL, let me know. Good luck!
    Last edited by MoneyBusiness; December 4th, 2006 at 01:11 PM.

  7. #7
    Newbie
    Join Date
    November 13th, 2006
    Posts
    14
    Thank you very much MoneyBusiness

  8. #8
    Newbie
    Join Date
    November 13th, 2006
    Posts
    14
    moneybusiness,

    you know how cj updates its status from open to locked to closed. Instead of adding a new field in the database how can I write a query that replaces the field in the db with the new status?

  9. #9
    ABW Ambassador MoneyBusiness's Avatar
    Join Date
    March 14th, 2006
    Posts
    2,051
    If I'm reading you correctly, you're looking to replace a value in a field, with a value from another field? If so, you need to grab the new status that you're talking about (not sure where that is in your DB, or other source, without further details), and do an update statement in your DB. For instance, if using mySQL, you could do something like:

    Code:
    $sql = mysql_query("UPDATE YourTableName SET Field1 = 'Open'") or die(mysql_error());
    The above line updates all values in your particular field and table, with the new status that you specify.

    If you're setting individual records with specific values, you could add a "where" clause as well, such as:

    Code:
    $sql = mysql_query("UPDATE YourTableName SET Field1 = 'Open' WHERE Field2 = 'Closed'") or die(mysql_error());
    The examples assume you're scripting with PHP. Otherwise, plain SQL would look like:

    Code:
    UPDATE YourTableName SET Field1 = 'Open' WHERE Field2 = 'Closed'
    Hope that's an answer you're looking for. If not, please clarify with more detail. Good luck!

  10. #10
    Newbie
    Join Date
    November 13th, 2006
    Posts
    14
    Thanks for your reply money business :-)

    I'm trying to update on duplicate

    PHP Code:
           $sql mysql_query("insert into cb_order (oid, ordertype, ordertyped, userid, orderpaid, merchantid, merchname, orderdate, ordertotal, ordercashback, ordercbperc, ordercbperct, myordercashback) values ('$ID', '$Order_Type', '$Action_Name', '$SID', '$Order_Paid', '$Advertiser_ID', '$Advertiser_Name', '$Event_Date', '$Sale_Amount', '$ordercashback', '$ordercbperc', '$ordercbperct', '$Commission') ON duplicate KEY UPDATE  .... 
    now how do I continue this query to say: on a duplicate oid (which is not my primary key in the database) update all fields?

  11. #11
    ABW Ambassador MoneyBusiness's Avatar
    Join Date
    March 14th, 2006
    Posts
    2,051
    First, the "On Duplicate Key Update" is really intended to be used with the primary key of your table.

    Having said that, I created a test table to see if I could combine two sql statements (one to find duplicates, and the other to update those duplicate records via subquery), but unfortunately, you'll receive an error if trying to use a subquery with the same tables/table names as your parent query.

    You may want to try to combine the results of your sql statements within your PHP file. As long as you're not updating hundreds of thousands of records, it shouldn't be a huge drain on your system.


    You can find the duplicates within your table by using a query like such:

    Code:
    $query = "SELECT oid FROM TestTable GROUP BY oid HAVING (COUNT(oid) > 1)";
    That'll return all duplicate records in your table. Simply loop through the results of that query, and update fields based on the values you need to insert/update. Unfortunately, I'm not sure where your values (ones you want to insert) are coming from, so can't write any sample code.

    Does this make sense? It will be a patch job, but it should work. If you need help implementing the script, let me know.

    by the way: Im no SQL expert, so it is possible that a better answer is out there. Im planning on calling a DBA friend of mine sometime soon, and will see if he knows of a better solution.
    Last edited by MoneyBusiness; October 9th, 2007 at 09:41 AM.

  12. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Sir Parse-Alot to the rescue
    By Sir Parse-Alot in forum Introduce Yourself
    Replies: 1
    Last Post: May 16th, 2010, 09:08 AM
  2. Help! Getting a parse error and can't find it
    By ProHealth in forum Programming / Datafeeds / Tools
    Replies: 0
    Last Post: December 22nd, 2007, 03:32 PM
  3. Please help me Parse Reports (for cashback system)
    By kinggori in forum Commission Junction - CJ
    Replies: 0
    Last Post: December 3rd, 2006, 11:22 PM
  4. Can I use sql to parse a datafeed column?
    By johnnyWebAffiliate in forum Programming / Datafeeds / Tools
    Replies: 5
    Last Post: April 5th, 2006, 07:58 AM
  5. Mouseover code giving parse errors.
    By thebu in forum Commission Junction - CJ
    Replies: 3
    Last Post: February 19th, 2005, 07:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •