Results 1 to 15 of 15
  1. #1
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Help with autoupdate
    I have gone through Dick Gardner's thread on auto updating and am trying my first feed in MySQL.

    I copy and pasted most of his example but am having a problem and cannot figure it out after staring at it for hours.

    Here is the code:

    <?
    require_once (xxx);

    // File can be anywhere on the Internet
    $FeedFile = 'C:\wamp\www\test\example.txt';

    mysql_query("drop table vendor_temp");
    mysql_query("CREATE TABLE vendor_temp (
    ProductName blob NOT NULL,
    LDescription blob NOT NULL,
    SDescription blob NOT NULL,
    Sku varchar(50) NOT NULL default '',
    PRIMARY KEY (sku),
    Brand varchar(50) NOT NULL default '',
    SalePrice varchar(7) NOT NULL default '0.00',
    Keywords blob NOT NULL default,
    RetailPrice varchar(7) NOT NULL default '0.00',
    BuyLinkURL blob NOT NULL,
    TrackingURL blob NOT NULL,
    ImageURL blob NOT NULL,
    ThumbURL blob NOT NULL,
    Category varchar(100) NOT NULL default '',
    SCategory varchar (100) NOT NULL default'',
    ProdGroup varchar (100) NOT NULL default '',)") or die(mysql_error());

    $feed = fopen($FeedFile, 'r');

    $rowNum = 0;
    $recCount = 0;

    while($data = fgetcsv($feed, 3000, "\t")){
    if($rowNum > 0){

    $iProductName = addslashes($data[2]);
    $iLDescription = addslashes($data[4]);
    $iSDescription = addslashes($data[5]);
    $iSku = addslashes($data[1]);
    $iBrand = addslashes($data[2]);
    $iSalePrice = addslashes($data[14]);
    $iKeywords = addslashes($data[12]);
    $iRetailPrice = addslashes($data[13]);
    $iBuyLinkURL = addslashes($data[11]);
    $iTrackingURL = addslashes($data[15]);
    $iImageURL = addslashes($data[10]);
    $iThumbURL = addslashes($data[9]);
    $iCategory = addslashes($data[6]);
    $iSCategory = addslashes($data[7]);
    $iProdGroup = addslashes($data[8]);

    $sql = mysql_query("insert into vendor_temp (ProdName, LDescription, SDescription, Sku, Brand, SalePrice, Keywords, RetailPrice, BuyLinkURL, TrackingURL, ImageURL, ThumbURL, Category, SCategory, ProdGroup)
    values
    ('$iProdName', '$iLDescription', '$iSDescription', '$iSku', '$iBrand', '$iSalePrice', '$iKeywords', '$iRetailPrice', '$iBuyLinkURL', '$iTrackingURL', '$iImageURL', '$iThumbURL', '$iCategory', '$iSCategory', '$iProdGroup')") or die(mysql_error());
    $recCount++;
    }

    mysql_query("drop table vendor");
    mysql_query("ALTER TABLE vendor_temp RENAME vendor") or die(mysql_error());

    $to = "<email>";
    $subj = "Vendor Database Table Update Report";
    $mssg = "This is an automated email. The Vendor Table update has
    completed successfully.\n\nThe total number or items loaded was
    $recCount.\n\n";
    $hdrs = "From: $to\n";
    mail($to, $subj, $mssg, $hdrs);
    gzclose ($FeedFile);
    echo "Avantlink Vendor Import Completed Successfully";}
    ?>

    Here is what I get in return:

    0){ $iProductName = addslashes($data[2]); $iLDescription = addslashes($data[4]); $iSDescription = addslashes($data[5]); $iSku = addslashes($data[1]); $iBrand = addslashes($data[2]); $iSalePrice = addslashes($data[14]); $iKeywords = addslashes($data[12]); $iRetailPrice = addslashes($data[13]); $iBuyLinkURL = addslashes($data[11]); $iTrackingURL = addslashes($data[15]); $iImageURL = addslashes($data[10]); $iThumbURL = addslashes($data[9]); $iCategory = addslashes($data[6]); $iSCategory = addslashes($data[7]); $iProdGroup = addslashes($data[8]); $sql = mysql_query("insert into vendor_temp (ProdName, LDescription, SDescription, Sku, Brand, SalePrice, Keywords, RetailPrice, BuyLinkURL, TrackingURL, ImageURL, ThumbURL, Category, SCategory, ProdGroup) values ('$iProdName', '$iLDescription', '$iSDescription', '$iSku', '$iBrand', '$iSalePrice', '$iKeywords', '$iRetailPrice', '$iBuyLinkURL', '$iTrackingURL', '$iImageURL', '$iThumbURL', '$iCategory', '$iSCategory', '$iProdGroup')") or die(mysql_error()); $recCount++; } mysql_query("drop table vendor"); mysql_query("ALTER TABLE vendor_temp RENAME vendor") or die(mysql_error()); $to = ""; $subj = "Vendor Database Table Update Report"; $mssg = "This is an automated email. The vendor Table update has completed successfully.\n\nThe total number or items loaded was $recCount.\n\n"; $hdrs = "From: $to\n"; mail($to, $subj, $mssg, $hdrs); gzclose ($FeedFile); echo "Avantlink Vendor Import Completed Successfully"; ?>

    Some of the information has been changed to protect my source and login.

    My understanding is this means an open tag, I think it may be at "while($data = fgetcsv($feed, 3000, "\t")){
    if($rowNum > 0){"
    but where do I close it?

    Any thoughts would very much appreciated.

    AL

    P.S. Also ran it through Syntax checker and it showed no problem.

  2. #2
    Member
    Join Date
    February 15th, 2008
    Location
    Rocky Mountain
    Posts
    64
    I think the last line may have some problem, because it has a closing }, but no open { before it.

    try deleting } in your last line of code.

  3. #3
    ABW Ambassador PatrickAllmond's Avatar
    Join Date
    September 20th, 2005
    Location
    OKC
    Posts
    1,219
    I concur - you have a mismatch my friend ! If you cannot find them put the { } on their own lines and count them.
    ---
    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

  4. #4
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Thanks for the input.

    I checked the tags and they match up, none not accounted for. I am throughly confused by this. Anyone else have any suggestions? Maybe they aren't in the right spots?

  5. #5
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    551
    1.
    You are closing your while loop too late.

    Your while loop should be closed directly after the close of your if condition.

    I also couldnt see any incrementation of $rowNum which means that your condition if($rowNum > 0){ will never be used.

    2.
    When I code, I indent.

    Code:
    $count = 0;
    while(condition){
        if($count > 0){
            echo "Do stuff";
        }
        $count++;
    }
    email me the result;
    This makes it easier to see the beginning and end of your conditions.

    3.
    Youre also better off using a condition instead of "or die" eg.

    if(mysql_query("ALTER TABLE vendor_temp RENAME vendor")){
    email me sucess;
    }else{
    email me failure;
    }

    use a function for your email, that way you can call the function and just pass the $mssg.
    http://uk2.php.net/manual/en/language.functions.php

    4.
    if you use fopen, you should use fclose, not gzclose
    http://uk2.php.net/manual/en/function.fopen.php
    http://uk2.php.net/manual/en/function.gzclose.php


    -------------
    bash away.....

  6. #6
    Full Member
    Join Date
    October 22nd, 2006
    Posts
    200
    The default of WAMP does not allow the use of short open tags eg <?

    You can either change the line "short_open_tag = off" in php.ini to "short_open_tags =on" or better still only use the full open tag <?php

  7. #7
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Wayder,

    Thanks for all the tips, I will put them to use as soon as I get home. Still have a J#B, ugh. Hopefully not for long.

    Donk,

    Thanks for the info., didn't know that. I will go with the full code.

  8. #8
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    I got everything else working but now I have a problem with this code:
    Code:
      require_once ('../connect_purecountry_local.php');
    
                      // File can be anywhere on the Internet
                 $FeedFile = 'http://datafeed.avantlink.com/download_feed.php?id=XXXXXXX';
     mysql_query("drop table example_temp");
                    mysql_query("CREATE TABLE example_temp (
              ID INT NOT NULL AUTO_INCREMENT
              ProductName TEXT NOT NULL,
              LDescription TEXT NOT NULL,
              SDescription TEXT NOT NULL,
              Sku VARCHAR(50) NOT NULL default '',
              Brand VARCHAR(50) NOT NULL default '',
              SalePrice VARCHAR(7) NOT NULL default '0.00',
              Keywords TEXT NOT NULL default,
              RetailPrice VARCHAR(7) NOT NULL default '0.00',
              BuyLinkURL BLOB NOT NULL,
              TrackingURL BLOB NOT NULL,
              ImageURL BLOB NOT NULL,
              ThumbURL BLOB NOT NULL,
              Category TINYTEXT(100) NOT NULL default '',
              SCategory TINYTEXT (100) NOT NULL default'',
              ProdGroup TINYTEXT (100) NOT NULL default '',
              PRIMARY KEY (ID),)") or die(mysql_error());
    And the result I get is:

    Code:
    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 'ProductName TEXT NOT NULL, LDescription TEXT NOT NULL, SDe' at line 3
    MySQL version: 5.0.45

    Thanks to all who are helping me out, it is much appreciated.

    Bear with me as I learn this and hopefully I can pay this information forward one day to help someone else learn how.

    Al

  9. #9
    Full Member
    Join Date
    October 22nd, 2006
    Posts
    200
    I've spotted two errors

    1/ No comma , after
    ID INT NOT NULL AUTO_INCREMENT

    2/ you need to add '' after default in
    Keywords TEXT NOT NULL default,

    Hope that helps

  10. #10
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Thanks a bunck Donk,

    I found another problem also and now I am getting a timeout message. I am going to reduce the file size and put it on my ISP server. Local is not taking it. I was planning on splitting the feeds up anyway, might as well do it now after I finish my database layout. I wanted to do a feed to make sure it works.

    That is, after I clean all the hair off the floor. I wonder, are their any programmers with hair? Hmm.

  11. #11

  12. #12
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Thanks Wayder,

    Printed them out and am taking them to work with me. Will be back at it Monday, travel weekend this weekend. Need a break anyway.

  13. #13
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Donk, Wayder and Dealpigg,

    I just wanted to give a big Thank You for helping me out.

    I have all 5 feeds working and am now putting my database together. This will be another challenge, but so far things are coming together. I am doing a comparison site and doing the layout of the database is indeed a challenge.

    I will also be starting the cleanup scripts today but do have one question.

    When I do my scripts, can I set them up so when one script finishes it will start the next in line to run? Say for instance, I run my import script, get a confirmation that it completed, it will then run the cleanup script, complete that and then run the script to copy the columns into the correct tables and so on? In effect causing a chain reaction.

    Or do I have to do chron jobs for each script to run?

    Thanks again, you guys have been an immense help.

    Al

  14. #14
    ABW Ambassador
    Join Date
    January 18th, 2005
    Posts
    551
    1.
    You can, or you can make them into one script.

    You need to ask yourself what do you want to happen if something succeeds/fails?

    2.
    If you havent already done so, read up on database normalization
    http://www.google.co.uk/search?hl=en...e+Search&meta=

  15. #15
    Newbie
    Join Date
    January 15th, 2008
    Location
    Portage, MI
    Posts
    39
    Thanks Wayder,

    I have read up on normalization and set my database up too keep it at a one-to-many relationship type of database. Makes a lot of tables but it will be much easier to work with later.

    I thought I could probably put it all together in one script but will need to do a lot of planning in advance to make it as failsafe as possible. It will take a few loops to accomplish what I want but I'll try to make it work. Love a challenge.

    I'll get to work on it and see what comes out.

    Al

  16. Newsletter Signup

+ Reply to Thread

Posting Permissions

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