Results 1 to 8 of 8
December 20th, 2005, 06:54 PM #1Database 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
December 20th, 2005, 08:05 PM #2
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.
December 20th, 2005, 08:47 PM #3
Ummmm, Ummm, Ummmm
Well I use the ID field to create the individual product pages "on the fly"
December 20th, 2005, 09:35 PM #4
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.
- I copy the production table, data and all, to a new temp table like TableA.
- Copy the production table's structure to another temp table like TableB.
- 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.
- 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).
December 20th, 2005, 09:51 PM #5
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.
December 21st, 2005, 10:04 AM #6
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.
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
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.
December 21st, 2005, 12:02 PM #7
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
December 21st, 2005, 12:14 PM #8
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.
By chillini in forum Programming / Datafeeds / ToolsReplies: 10Last Post: December 2nd, 2008, 01:06 PM
By halifaxer in forum Programming / Datafeeds / ToolsReplies: 4Last Post: February 20th, 2008, 09:20 PM
By Snib in forum Programming / Datafeeds / ToolsReplies: 10Last Post: September 8th, 2006, 12:24 AM
By Amavisca in forum Programming / Datafeeds / ToolsReplies: 0Last Post: July 6th, 2005, 05:43 AM
By ~Michelle in forum Programming / Datafeeds / ToolsReplies: 2Last Post: March 26th, 2004, 12:59 PM