Results 1 to 8 of 8
  1. #1
    Affiliate/AM Moonlighter dflsports's Avatar
    Join Date
    January 17th, 2005
    Posts
    874
    Database of datafedds how to update - mysql
    So I loaded many datafeeds into a mysql database. I have a search script and other things working. I use an ID field set to auto increment and use that as the primary key. (I am a newbie at this stuff so my knowledge is limited)

    So when I want to update the data, do I have to delete all the existing data and upload the new data?

    If I try to overwrite the data on a per feed basis, how will a script know to overwrite the correct data since the id field is created "on the fly"?

    the feeds come from different sources, not just one place like CJ or Linkshare but from both linkshare and CJ among many other places.

    Thank you in advance for any assistance you can provide

  2. #2
    ABW Ambassador Greg Rice's Avatar
    Join Date
    January 18th, 2005
    Location
    Ohio
    Posts
    4,889
    Are you using the unique key for any queries? If not, you can create a temp table using the same structure, import your data into that and then swap the table names. You can also create a script that will do all of this.
    Greg Rice Affiliate Program Management
    www.gocmc.com info(AT)gocmc.com | 330-259-1223

    Join us! - MiNeeds.com | DiscountCandleShop/CheeseSupply | Feng Shui Plaza

  3. #3
    Affiliate/AM Moonlighter dflsports's Avatar
    Join Date
    January 17th, 2005
    Posts
    874
    Ummmm, Ummm, Ummmm

    Well I use the ID field to create the individual product pages "on the fly"

  4. #4
    ABW Ambassador Greg Rice's Avatar
    Join Date
    January 18th, 2005
    Location
    Ohio
    Posts
    4,889
    What I'm asking is if it matters if Blue Widget has an ID of 1 today but may have ID of 2 tomorrow. If this happens, will it ruin your query? For example, a category page would show products that match a certain category and not the auto increment ID number. What I'm thinking is if this ID is auto incremented, then it may not be that important for your queries. If you're not searching for this ID, then the above mentioned method would work. If this ID can't change and must remain as it is originally assigned, then the update is a little more complicated.

    I'm no expert and have no training with mySQL so my method probably isn't as lean as it could be. If for some reason I can't just create a temp table, import the data and swap table names then here is what I do.

    1. I copy the production table, data and all, to a new temp table like TableA.
    2. Copy the production table's structure to another temp table like TableB.
    3. Import the new product data, that you downloaded from CJ or LS, into TableB. So now TableA has the old data and TableB has the new data. The feeds should have the product sku or product number which is unique to that product and, therefore, this sku or product number is the same with each update from LS or CJ.
    4. Do an UPDATE IGNORE query from TableB to TableA and update those columns you wish to update using the sku or product number to match the products to be updated. This will overwrite the columns in TableA with data from TableB and won't overwrite those columns you don't want updated (or changed, like your auto incremented ID).
    If you need help with these queries, you can email me at greg(AT)commercemc.com and I can show you some examples of what I do. I use phpMyAdmin, which makes it much easier so I hope you use it too. With no training in this, I struggled with this too so I feel your pain.
    Greg Rice Affiliate Program Management
    www.gocmc.com info(AT)gocmc.com | 330-259-1223

    Join us! - MiNeeds.com | DiscountCandleShop/CheeseSupply | Feng Shui Plaza

  5. #5
    Affiliate/AM Moonlighter dflsports's Avatar
    Join Date
    January 17th, 2005
    Posts
    874
    Thanks for responding Greg

    The ID does not matter in searches and can be changed. After a search, I then use the ID to display a single product.

    So I have not created a second table. But It looks like I can create a second table in the same mysql database. So with this second table could import the data into the mysql database, and the primary key in this case would be the sku, if available. So this would then update the mysql database with the new products. then the other table used in the search script and product script would have access to the updated products?

    And yes I am using phpmyadmin and I did find a neat little import script but the whole ID field messes with the update since it's the primary key.

    Am I getting warmer

    Also, would the BUYURL be a viable option as the primary key since it should be unique for every product?

    Again, thanks alot for the help.

  6. #6
    ABW Ambassador Greg Rice's Avatar
    Join Date
    January 18th, 2005
    Location
    Ohio
    Posts
    4,889
    When you're done updating your production table, you should only have the production table left. Your table just needs to have one unique column and since each product should be different, I use the sku or product number in the feed from the network (CJ or LS). If you use this, then the auto incremented value is no longer important. Using the sku as the unique makes updating easy. Using phpMyAdmin, select the table, go to Operations and copy the table with the structure only option, just give the new table a different name since you can't have duplicate named tables. Then import your data into this new table and swap the table names. The query is

    RENAME TABLE Table to TableOld,TableA to Table

    where Table is your production table and TableA is your newly copied and updated table. After you check out the updated data, you can delete TableOld if you want.

    To display a single product, I use the sku or product id that's in the feed. If you're already using this auto incremented id and can't change your pages/queries then the multiple table method can be used. Still, once you're done updating you will only have the production table to work with, not two tables. It's a little difficult to explain this in writing so I apologize if I'm not making this clear so I'll give a little more detail to see if it helps.

    Table=Production table
    TableA=Copy of Table's structure

    So now we have 2 tables with the same structure, Table had data in it and TableA does not have any data in it yet.

    Now, import the updated feed into TableA. We now have 2 tables with data in both, Table has old data and TableA has new data.

    Within phpMyAdmin, run a number of queries like this:

    UPDATE IGNORE Table, TableA SET Table.Price = TableA.Price WHERE Table.SKU = TableA.SKU
    This query will take the price from TableA and overwrite the price in Table where the SKU's match up. You can run this query multiple times, changing the value of the columns you want to update. The only problem with this method is that it won't add any new products that may exist in the new datafeed but will only update data where the SKU's match.


    If you want all the new products added as well, you can reverse the update and just update the auto incrementing value from Table and insert it into TableA.


    UPDATE IGNORE TableA, Table SET TableA.ID = Table.ID WHERE TableA.SKU = Table.SKU

    The problem with this is as you import the data into TableA, it will assign an auto incrementing value to the rows. If TableA gives the value "3" to a new product but value "3" is used for another product in Table, you may end up with 2 products with value "3" or the query won't work. This is why I use the sku as the unique key as it should remain a constant value from feed to feed as the merchant updates the feed. Yes, you should be able to use the product's buyurl as a unique key as well since each one should be different from the other.
    Greg Rice Affiliate Program Management
    www.gocmc.com info(AT)gocmc.com | 330-259-1223

    Join us! - MiNeeds.com | DiscountCandleShop/CheeseSupply | Feng Shui Plaza

  7. #7
    Affiliate/AM Moonlighter dflsports's Avatar
    Join Date
    January 17th, 2005
    Posts
    874
    Thanks again, it makes sense but I think I need to bang my head on the desk first to fully comprehend this, wait a second, ok, that really did not help, ha!

    I like the sku idea, thing is I get alot of feeds outside the networks and they usually do not have a sku. Alot of these feeds are pretty basic and not relaly customized by the merchant due to their lack of technical ability.

    I may be emailing you if I can't grasp this by mid next week

    Happy Holidays!

  8. #8
    ABW Ambassador Greg Rice's Avatar
    Join Date
    January 18th, 2005
    Location
    Ohio
    Posts
    4,889
    I understand what you're saying about the sku and some merchants, just pick a unique column that stays constant and you'll be better off. I'm sure there are better ways to do this but this is about the limit of what I know. If you still need help, don't hesitate to contact me. I work from home and am usually around so it's not a problem. Have a great Christmas.
    Greg Rice Affiliate Program Management
    www.gocmc.com info(AT)gocmc.com | 330-259-1223

    Join us! - MiNeeds.com | DiscountCandleShop/CheeseSupply | Feng Shui Plaza

  9. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. So i've got the datafeed into mysql database..
    By chillini in forum Programming / Datafeeds / Tools
    Replies: 10
    Last Post: December 2nd, 2008, 02:06 PM
  2. Using xml to UPDATE my mySQL database. tricky!
    By halifaxer in forum Programming / Datafeeds / Tools
    Replies: 4
    Last Post: February 20th, 2008, 10:20 PM
  3. Database Replication with mySQL
    By Snib in forum Programming / Datafeeds / Tools
    Replies: 10
    Last Post: September 8th, 2006, 01:24 AM
  4. How can I use buyitbomb with Mysql database?
    By Amavisca in forum Programming / Datafeeds / Tools
    Replies: 0
    Last Post: July 6th, 2005, 06:43 AM
  5. MySQL database - Can I...
    By ~Michelle in forum Programming / Datafeeds / Tools
    Replies: 2
    Last Post: March 26th, 2004, 01:59 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
  •