Results 1 to 9 of 9
  1. #1
    Newbie adsw's Avatar
    Join Date
    September 6th, 2006
    Posts
    45
    Help with datafeed import
    Hi,

    Trying to import a Shareasale feed into a mysql database. Using code found at http://howto.indika.net.id/wikka.php...vInsertToMySql

    Have imported the example csv file into database but want to import a txt file and don't know what to change below. Is there a different function which is used instead of fgetcsv. My feed is pipe delimited (|) so changed $filename, $delim ="," to $filename, $delim ="|".

    Is this right? Newbie here, so please go easy

    Here's part of the code found at the above website.

    //we need function to get the csv
    function getcsv($filename, $delim =","){

    $row = 0;
    $dump = array(); //create new array for hold the data csv

    $f = fopen ($filename,"r");
    $size = filesize($filename)+1;
    while ($data = fgetcsv($f, $size, $delim)) {
    $dump[$row] = $data; //put the data to array
    //echo $data[1]."<br>";
    $row++;
    }
    fclose ($f);

    return $dump;
    }

    //this function for insert data to csv
    function makeINSERTS($text, $table){
    global $linkdata; //make global database connection
    $insert = array(); //make array for hold data insert
    $i = 0;

    while (list($key, $val) = @each($text)){
    //insert the data
    $insert[$i] = "INSERT into ".$table." VALUES('','";
    $insert[$i] .= implode("','", $val);
    $insert[$i] .= "')\n";
    $result = mysql_query($insert[$i], $linkdata) or die('Query failed: ' . mysql_error());
    $i++;
    }

    return $insert;

    }
    Hope I'm making sense. Hope someone can help.

    Thanks

    Adrian

  2. #2
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    What is happening or not happening?
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  3. #3
    Newbie adsw's Avatar
    Join Date
    September 6th, 2006
    Posts
    45
    Hi Patrick,

    When I run the insert script I get an error back

    Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Box (DTS Version)','8848','YesAsia','http://www.shareasale.com/m-pr.cfm?mercha' at line 1
    Have changed code to look for Pipe delimeter as in the YesAsia SAS datafeed (txt file).

    Here's the 1th line of the datafeed

    455418103|A Chinese Odyssey Part I - Pandora's Box (DTS Version)|8848|YesAsia|http://www.shareasale.com/m-pr.cfm?merchantID
    Seems to have a problem with the ' character as in Pandora's Box.

    Any idea on how I can fix this.

    Thanks

    Adrian

  4. #4
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    THe apostrophe has special mean in SQL. It is usually use to surround literal text.

    If you want to insert one you need to replace it with double apostrophe - not a quote " but two apostrophes ''.
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  5. #5
    Member Tobius's Avatar
    Join Date
    August 16th, 2006
    Location
    Columbus, OH
    Posts
    55
    Your code doesn't account for unescaped characters like single apostrophes which are special to MySQL. Try replacing your makeINSERTS function with something like this:

    Code:
    function makeINSERTS($text, $table)
    {
        global $linkdata; //make global database connection
        $insert = array(); //make array for hold data insert
    
        foreach($text as $record)
        {
            $escaped = array();
    
            foreach($record as $field)
            {
                $escaped[] = mysql_real_escape_string($field);
            }
    
            //insert the data
            $sql = "INSERT INTO " . $table . " VALUES ('', '" . implode("','", $escaped) . "');\n";
            $insert[] = $sql;
            $result = mysql_query($insert[$i], $linkdata) or die('Query failed: ' . mysql_error());
        }
    
        return $insert;
    }
    Notice how I am taking each field value before it's being passed in and passing it through mysql_real_escape_string? This will escape anything that would otherwise cause the error that you're seeing.

    Note though that I just whipped this up and didn't have time to test it so it might not work out of the box. So post back and let me know how it goes and we'll help you work through it if you're still having issues.

    -tm

  6. #6
    Newbie adsw's Avatar
    Join Date
    September 6th, 2006
    Posts
    45
    Hi Patrick,

    Thanks for your reply.

    Removed apostrophes from feed and now getting error

    Query failed: Column count doesn't match value count at row 1
    SAS Datafeed structure is as follows (19 columns)

    ProductID|Name|MerchantID|Merchant|Link|Thumbnail|BigImage|Price|RetailPrice|Category|SubCategory|Description|Custom1|Custom2|Custom3|Custom4|Custom5|LastUpdated|status
    Have the exact same columns in my database.

    First line of the datafeed includes 19 delimeted fields as follows

    455418103|A Chinese Odyssey Part I - Pandora's Box (DTS Version)|8848|YesAsia|http://www.shareasale.com/m-pr.cfm?merchantID=8848&userID=YOURUSERID&productID=455418103|http://image.yesasia.com/assets/57/005/p1000000557.jpg|http://image.yesasia.com/assets/57/005/p1000000557.jpg|7.99|12.99|Entertainment|DVD|A bold reinvention of the classic "A Journey to the West" begins in <i>A Chinese Odyssey Part I - Pandora's Box</i>, the first of director Jeff any you've ever seen!||||||2006-09-14 15:30:07.563|instock
    Still don't know where I'm going wrong.

    Thanks again,

    Adrian

  7. #7
    Newbie adsw's Avatar
    Join Date
    September 6th, 2006
    Posts
    45
    Thanks Tobius,

    Will try that out.

    ADrian

  8. #8
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    No.. don't take them out!
    ---
    This response was masterly crafted via the fingers of Patrick Allmond who believe you should StopDoingNothing starting today.
    ---
    Focus Consulting is where I roll | Follow @patrickallmond on Twitter
    Search Engine Marketing | Search Engine Optimization | Social Media | Online Video

  9. #9
    Newbie adsw's Avatar
    Join Date
    September 6th, 2006
    Posts
    45
    Hi Tobius,

    Replaced my makeINSERTS function with your makeINSERTS function but now getting the following error.

    Query failed: Query was empty
    Have found out why I was getting the column count error, had to add a column for ID.

    Really appreciate the help

    Thanks

    Adrian

  10. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Best way to import datafeed for performance
    By MoreBeer in forum Programming / Datafeeds / Tools
    Replies: 15
    Last Post: May 25th, 2010, 02:26 PM
  2. Link to Import Datafeed
    By bluewaves1 in forum Programming / Datafeeds / Tools
    Replies: 11
    Last Post: March 13th, 2008, 07:02 PM
  3. Help with datafeed import
    By adsw in forum Midnight Cafe'
    Replies: 4
    Last Post: September 25th, 2006, 06:13 PM
  4. Datafeed Import Optimization
    By Snib in forum Programming / Datafeeds / Tools
    Replies: 31
    Last Post: May 8th, 2006, 02:41 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
  •