zipp Posted October 11, 2009 Share Posted October 11, 2009 What I am doing, is taking a xml file, and adding the values to a database. However, what I want to do is only add the new values. I am guessing that a script that compared the first xx chars of the xml value to the value in the database would work best. The script would be ran (in theory) once a day. Example: day1.xml <fruits> <fruit>apple</fruit> <fruit>pear</fruit> <fruit>orange</fruit> </fruits> day2.xml <fruits> <fruit>carrot</fruit> <fruit>apple</fruit> <fruit>pear</fruit> <fruit>grape</fruit> <fruit>orange</fruit> </fruits> Does anyone have any tips or code I could mooch off them? Quote Link to comment https://forums.phpfreaks.com/topic/177353-only-add-new-information-from-xml-to-mysql/ Share on other sites More sharing options...
PFMaBiSmAd Posted October 11, 2009 Share Posted October 11, 2009 Your column in your database table should be a unique key. Then, you can simply use the IGNORE keyword in your INSERT query to prevent duplicates values from being inserted without producing an error. Quote Link to comment https://forums.phpfreaks.com/topic/177353-only-add-new-information-from-xml-to-mysql/#findComment-935107 Share on other sites More sharing options...
zipp Posted October 11, 2009 Author Share Posted October 11, 2009 The values could be a lot longer (400+chars). Not just a single word like in my example. Sorry, that was a bad example in that aspect. Quote Link to comment https://forums.phpfreaks.com/topic/177353-only-add-new-information-from-xml-to-mysql/#findComment-935109 Share on other sites More sharing options...
PFMaBiSmAd Posted October 11, 2009 Share Posted October 11, 2009 What is the total number of expected rows to the nearest power of 10? 100, 1K, 10K, 100K, 1M??? And once a value has been inserted will it always be that same value or can it be altered and if it is altered and the original is still in the xml file should the original be inserted as a new value? Help us out by filling in some of the blanks that you know about the data. Quote Link to comment https://forums.phpfreaks.com/topic/177353-only-add-new-information-from-xml-to-mysql/#findComment-935117 Share on other sites More sharing options...
zipp Posted October 11, 2009 Author Share Posted October 11, 2009 The # of values is always increasing (never decreasing) at a random rate. 5 a day, maybe 500 a day. Once the value is in the xml file, it will not be changed. However, it can be removed. Right now, I have the code checking the complete value against that in the database, but that is pretty slow. Quote Link to comment https://forums.phpfreaks.com/topic/177353-only-add-new-information-from-xml-to-mysql/#findComment-935130 Share on other sites More sharing options...
PFMaBiSmAd Posted October 12, 2009 Share Posted October 12, 2009 I would also store a sha1 hash of the value and the length of the value in the table (you could determine these at the time the query is executed but if ultimate performance is an issue, calculate them once when the data is inserted.) Then you will only need to compare the hash and length of the data with the information in the table. There is a very very high probability that if both the hash and length of data matches something already in the table that it is an exact match and does not need to be inserted again. Does each piece of data in the xml file contain any sort of date/time information that you could instead use to filter out things that have already been processed by storing the latest date/time each time you process the file? Quote Link to comment https://forums.phpfreaks.com/topic/177353-only-add-new-information-from-xml-to-mysql/#findComment-935405 Share on other sites More sharing options...
zipp Posted October 12, 2009 Author Share Posted October 12, 2009 Does each piece of data in the xml file contain any sort of date/time information that you could instead use to filter out things that have already been processed by storing the latest date/time each time you process the file? No Thank you for the has/length idea, I'll work on that Quote Link to comment https://forums.phpfreaks.com/topic/177353-only-add-new-information-from-xml-to-mysql/#findComment-935782 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.