Results 1 to 15 of 15
  1. #1
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    PHP Datafeed Logic Help, Please
    Ok, so I'm using the bullet proof datafeed thead to retool an old site. I have my first Datafeed import scripts almost complete except for on thing. It only adds records to the database and will not update an existing record.

    PHP Code:

        
    if ( $iGender != "ACCESSORIES" ) {  //  Test for Proper Product
        
    $query "select
        id 
        from 
        products 
        where 
        sku = '
    $sku
        and merchant = '
    $merchant
        "

        
    $result mysql_query($query,$dbh) or die(mysql_error());
           if(
    $row mysql_fetch_array($result)){ 
           
    $query "update
           products 
           Set
                    Sku = '
    $iSku', 
                    Name = '
    $iName', 
                    SalePrice = '
    $iSalePrice',
           Price = '
    $iPrice', 
                       Merchant = '
    $merchant',
           Description = '
    $iDescription',
           Brand = '
    $iBrand',
           LinkURL = '
    $iLinkURL',
           ImageURLsm = '
    $iImageURLsm',
           ImageURLlg = '
    $iImageURLlg',
           Gender = '
    $iGender',
           NotAvailable = '0'
            "
    ;
           
    $itemsUpdated++;
        } 
        else 
        { 
              
    $query "insert into
          products 
          Set
            Sku = '
    $iSku', 
                   Name = '
    $iName', 
                   SalePrice = '
    $iSalePrice',
          Price = '
    $iPrice', 
                      Merchant = '
    $merchant',
          Description = '
    $iDescription',
          Brand = '
    $iBrand',
          LinkURL = '
    $iLinkURL',
          ImageURLsm = '
    $iImageURLsm',
           ImageURLlg = '
    $iImageURLlg',
          Gender = '
    $iGender',
          NotAvailable = '0'
        "

        
    $itemsAdded++; 
        } 
    Anyone have any pointers Thanks
    Thanks,
    Doug

  2. #2
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    You're missing the "where" clause in your update query. You need to get the product id from the first select query and update where product id = $prod_id.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  3. #3
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    Looks like it is not really updating. The script will add a record if one does not exist but it does not seem to be updating. I test the update by changin the name of the last product. When I ran the script it had indicated that it updated all the existing products. But when I went into phpMyAdmin and looked at the last product in the table the name was not updated to what was in the datafeed.

    Thanks
    Last edited by Doug247; March 30th, 2008 at 10:30 AM. Reason: Further testing found error
    Thanks,
    Doug

  4. #4
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Like Snib said, you need to add a where clause in your update query. You have to indicate which product you want to update.

    PHP Code:
    $query "update
           products
           Set
                    Sku = '
    $iSku',
                    Name = '
    $iName',
                    SalePrice = '
    $iSalePrice',
           Price = '
    $iPrice',
                       Merchant = '
    $merchant',
           Description = '
    $iDescription',
           Brand = '
    $iBrand',
           LinkURL = '
    $iLinkURL',
           ImageURLsm = '
    $iImageURLsm',
           ImageURLlg = '
    $iImageURLlg',
           Gender = '
    $iGender',
           NotAvailable = '0'

    //YOU NEED TO ADD SOMETHING LIKE THIS HERE
    where product_id = 
    $prod_id

            "


  5. #5
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    I did add the where clause...

    PHP Code:
    $query "select
                id 
                from 
                products 
                where 
                sku = '
    $iSku
                and merchant = '
    $merchant
                "

                
    $result mysql_query($query) or die(mysql_error());
                echo 
    $results;
                    if(
    $row mysql_fetch_array($result)){ 
                    
    $query2 "update
                              products 
                              Set
                              Sku = '
    $iSku', 
                              Name = '
    $iName', 
                              SalePrice = '
    $iSalePrice',
                              Price = '
    $iPrice', 
                              Merchant = '
    $merchant',
                              Description = '
    $iDescription',
                              Brand = '
    $iBrand',
                              LinkURL = '
    $iLinkURL',
                              ImageURLsm = '
    $iImageURLsm',
                              ImageURLlg = '
    $iImageURLlg',
                              Gender = '
    $iGender',
                              NotAvailable = '0' 
                              where id = '
    $row[$id]'
                              "
    ;
                    
    $itemsUpdated++;
                    } 
                    else 
                    { 
                    
    $query2 "insert into
                              products 
                              Set
                              Sku = '
    $iSku', 
                              Name = '
    $iName', 
                              SalePrice = '
    $iSalePrice',
                              Price = '
    $iPrice', 
                              Merchant = '
    $merchant',
                              Description = '
    $iDescription',
                              Brand = '
    $iBrand',
                              LinkURL = '
    $iLinkURL',
                              ImageURLsm = '
    $iImageURLsm',
                              ImageURLlg = '
    $iImageURLlg',
                              Gender = '
    $iGender',
                              NotAvailable = '0'
                              "

                    
    $itemsAdded++; 
                    
    $recCount++;
                    } 
                    
    mysql_query($query2) or die("Error in query: $query. " mysql_error()); 

    Currently the code above will add a product if that product dies not exist but it says it is updating but it really does not. Thanks for your help
    Thanks,
    Doug

  6. #6
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Doug, try changing this line

    where id = '$row[$id]'

    to this

    where id = '$row['id']'


    and see what happens.


    Or assign the variable $id the value in the original query, then your where statement would look like this:

    where id = '$id'
    Last edited by bettylou; March 30th, 2008 at 11:31 AM. Reason: To add alternate variable option

  7. #7
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    Quote Originally Posted by bettylou
    Doug, try changing this line

    where id = '$row[$id]'

    to this

    where id = '$row['id']'


    and see what happens.


    Or assign the variable $id the value in the original query, then your where statement would look like this:

    where id = '$id'

    I tried both and it still did not work. Should I repost more of what I have for code? Thanks
    Thanks,
    Doug

  8. #8
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    That would probably be helpful. I am assuming that there is query above that gives you the $isku and $merchant variables.

    And I also am guessing that the reason it is telling you that it updated x number of products is because you are echoing the $itemsUpdated variable in the end, which doesn't mean that it actually updated the product just that you are increasing the variable by 1 each time it runs the loop.

    It you don't mind, could you post more of the code. I know it's kind of pain.

  9. #9
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    Not a problem at all as I greatly appreciate you help. Here is what I have pretty much from the beginning

    PHP Code:
    require('db_connect.php');            
        
    $FeedFile "db/$datafeed_file";    
        
    $feed gzopen($FeedFile'r');
        
    $itemsUpdated 0;
        
    $itemsAdded 0;
        
    $rowNum 0;
        
    $recCount 0;
        while(
    $data fgetcsv($feed3000"|")){
            if(
    $rowNum 0){
                
    $iName         addslashes($data[11]);
                
    $iMerchant         addslashes($data[3]);
                
    $iDescription         addslashes($data[13]);
                
    $iSku         addslashes($data[0]);
                
    $iBrand         addslashes($data[15]);
                
    $iSalePrice         addslashes($data[7]);
                
    $iPrice         addslashes($data[7]);
                
    //$iRetailPrice         = addslashes($data[14]);
                
    $iLinkURL         addslashes($data[4]);
                
    //$iImpressionURL         = addslashes($data[17]);
                   
    $iImageURLsm         addslashes($data[5]);
                 
    $iImageURLlg         addslashes($data[6]);
                
    //$iCurrency         = addslashes($data[11]);    
                
    $iGender         addslashes($data[12]);
                
    $iCategory         addslashes($data[10]);
                
                if ( 
    $iGender != "ACCESSORIES" ) {  
                
    $query "select
                id 
                from 
                products 
                where 
                sku = '
    $iSku
                and merchant = '
    $merchant
                "

                
    $result mysql_query($query) or die(mysql_error());
                if(
    $row mysql_fetch_array($result)){ 
                
    $id $row[id];
                echo 
    $id;
                    
    $query2 "update
                              products 
                              Set
                              Sku = '
    $iSku', 
                              Name = '
    $iName', 
                              SalePrice = '
    $iSalePrice',
                              Price = '
    $iPrice', 
                              Merchant = '
    $merchant',
                              Description = '
    $iDescription',
                              Brand = '
    $iBrand',
                              LinkURL = '
    $iLinkURL',
                              ImageURLsm = '
    $iImageURLsm',
                              ImageURLlg = '
    $iImageURLlg',
                              Gender = '
    $iGender',
                              NotAvailable = '0' 
                              where id = '
    $id'
                              "
    ;
                    
    $itemsUpdated++;
                    } 
                    else 
                    { 
                    
    $id $row[id];
                    echo 
    $id;
                    
    $query2 "insert into
                              products 
                              Set
                              Sku = '
    $iSku', 
                              Name = '
    $iName', 
                              SalePrice = '
    $iSalePrice',
                              Price = '
    $iPrice', 
                              Merchant = '
    $merchant',
                              Description = '
    $iDescription',
                              Brand = '
    $iBrand',
                              LinkURL = '
    $iLinkURL',
                              ImageURLsm = '
    $iImageURLsm',
                              ImageURLlg = '
    $iImageURLlg',
                              Gender = '
    $iGender',
                              NotAvailable = '0'
                              "

                    
    $itemsAdded++; 
                    
    $recCount++;
                    } 
                    
    mysql_query($query2) or die("Error in query: $query. " mysql_error()); 
                    } 
                 } 
                  
    $rowNum++;
                  } 
                 
    $query "update products set LinkURL = replace(LinkURL,'$aidholder','$aid') Where Merchant = '$merchant'"
                 
    mysql_query($query) or die(mysql_error()); 
                 
    mysql_close ($dbh); 


    Thanks again!
    Last edited by Doug247; March 30th, 2008 at 01:14 PM. Reason: Forgot to say thanks
    Thanks,
    Doug

  10. #10
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Doug,
    First off, I don't see where you get the $merchant variable in this line:

    and merchant = '$merchant'

    should it be $iMerchant

    or are you assigning it a value somewhere else?

  11. #11
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    Quote Originally Posted by bettylou
    Doug,
    First off, I don't see where you get the $merchant variable in this line:

    and merchant = '$merchant'

    should it be $iMerchant

    or are you assigning it a value somewhere else?

    I define $merchant in block of code above the database connection. I left it off the post because I includes my connection information. I can post it though.

    I'll look at what to think it should be though.

    OK I just looked at merchant = '$merchant' and the is ok. I want to have manual control over what is in that field. I plan on having a script run for every datafeed and that is why I defined it in the upper section of the code.




    Thanks
    Thanks,
    Doug

  12. #12
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    I'm not too sure what the did last but it seems to be updating and adding products the way it should. I just tested it in FF and it seemed to work great!


    Many Thanks Bettylou!
    Thanks,
    Doug

  13. #13
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Ok, let me look a little more. Right off, I don't see the problem.

    We know that is running the update loop because it is increasing the $itemsUpdated variable. For some reason, it doesn't like the query to actually do the update. I'm guessing when it you echo the $id variable right before the update query it is showing you what you want it to show you???

  14. #14
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    ooops! I made my last reply just as you did.

    Great! I am so glad it is working. Credit should go to Snib, he saw the original lack of a where statement. He is the php king.


    Good Luck!

  15. #15
    ABW Ambassador Doug247's Avatar
    Join Date
    January 18th, 2005
    Location
    DE USA
    Posts
    931
    Quote Originally Posted by bettylou
    ooops! I made my last reply just as you did.

    Great! I am so glad it is working. Credit should go to Snib, he saw the original lack of a where statement. He is the php king.


    Good Luck!
    Snib is the one who pointed out the datafeed thread and sparked my interest in retooling an old site and in PHP.

    Thanks Snib, Thanks Bettylou
    Thanks,
    Doug

  16. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. Datafeed/webmerge VS Php/mysql datafeed
    By Lasthope in forum Programming / Datafeeds / Tools
    Replies: 7
    Last Post: November 25th, 2004, 10:23 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
  •