nigel99 144 #1 September 21, 2010 Can anyone help with a MySQL and PHP query? I have a PHP script that takes a data stream and after parsing it inserts the results into a MySQL table. We need to check whether the data is unique based on an address and timestamp combination, so my current solution is to query the database with the timestamp and address and if a match is found discard the duplicate data. The problem is that this only works where the data has a delay between duplicate events. It is like there is some sort of write buffer that causes the query to return false when it should be true... Any idea's welcomeExperienced jumper - someone who has made mistakes more often than I have and lived. Quote Share this post Link to post Share on other sites
theonlyski 3 #2 September 21, 2010 Quote Can anyone help with a MySQL and PHP query? I have a PHP script that takes a data stream and after parsing it inserts the results into a MySQL table. We need to check whether the data is unique based on an address and timestamp combination, so my current solution is to query the database with the timestamp and address and if a match is found discard the duplicate data. The problem is that this only works where the data has a delay between duplicate events. It is like there is some sort of write buffer that causes the query to return false when it should be true... Any idea's welcome Have it read the data into a seperate array, after every capture process (or the end of the loop) have it process the data in the second array. You could also just write to php scripts as different pages and load them both in your browser, one to fill the second array, one to process the data into the first."I may be a dirty pirate hooker...but I'm not about to go stand on the corner." iluvtofly DPH -7, TDS 578, Muff 5153, SCR 14890 I'm an asshole, and I approve this message Quote Share this post Link to post Share on other sites
MakeItHappen 15 #3 September 21, 2010 Quote Can anyone help with a MySQL and PHP query? I have a PHP script that takes a data stream and after parsing it inserts the results into a MySQL table. We need to check whether the data is unique based on an address and timestamp combination, so my current solution is to query the database with the timestamp and address and if a match is found discard the duplicate data. The problem is that this only works where the data has a delay between duplicate events. It is like there is some sort of write buffer that causes the query to return false when it should be true... Any idea's welcome You could make a unique key from the two data fields. You could do subsequent queries with the distinct modifier (and not worry about dupes) You can run a dupe checker later and remove dupes. .. Make It Happen Parachute History DiveMaker Quote Share this post Link to post Share on other sites
vdschoor 0 #4 September 21, 2010 is the data in your php script "unique" to the script? If so, create a unique key in the mysql database, and just insert the data and implement the error handling in the php script instead of checking the database first, and then doing an insert.. that is going to back up fast if you have high volume.. Quote Share this post Link to post Share on other sites
nigel99 144 #5 September 21, 2010 thanks that sounds about right as an approach. We are in the process of trashing the PHP and moving across to C++ as PHP is a crap language for what we are doing anyway. We should be able to tweak things to make the unique key from the two data fields.Experienced jumper - someone who has made mistakes more often than I have and lived. Quote Share this post Link to post Share on other sites
MakeItHappen 15 #6 September 22, 2010 Quotethanks that sounds about right as an approach. We are in the process of trashing the PHP and moving across to C++ as PHP is a crap language for what we are doing anyway. We should be able to tweak things to make the unique key from the two data fields. I think some clarification might be in order. I was suggesting adding a unique key to the mysql DB based on a combination of the two data fields. (You have to make sure that the formatting of the combined field works for the timestamp format that you use, accounting for padding of spaces or 0) The DB then processes the requests and ensures only one entry for the key. I was not suggesting that you use the scripting language or a cgi language to process the determination of uniqueness. Also, just because the two data fields that you are looking at match, does not mean the rest of the record is identical. .. Make It Happen Parachute History DiveMaker Quote Share this post Link to post Share on other sites
nigel99 144 #7 September 22, 2010 QuoteQuotethanks that sounds about right as an approach. We are in the process of trashing the PHP and moving across to C++ as PHP is a crap language for what we are doing anyway. We should be able to tweak things to make the unique key from the two data fields. I think some clarification might be in order. I was suggesting adding a unique key to the mysql DB based on a combination of the two data fields. (You have to make sure that the formatting of the combined field works for the timestamp format that you use, accounting for padding of spaces or 0) The DB then processes the requests and ensures only one entry for the key. I was not suggesting that you use the scripting language or a cgi language to process the determination of uniqueness. Also, just because the two data fields that you are looking at match, does not mean the rest of the record is identical. . sorry my bad communications skills at work. I understood what you were saying and through in the extra noise about moving from PHP... thanksExperienced jumper - someone who has made mistakes more often than I have and lived. Quote Share this post Link to post Share on other sites
DigitalDave 0 #8 September 22, 2010 If I understand what you're saying, it sounds like you need to use a REPLACE query instead of INSERT. Quote Share this post Link to post Share on other sites