theconnollykid Posted February 24, 2009 Share Posted February 24, 2009 Greetings All, I am hoping someone can help shed some light on my dilemma... 1. I have a table with user profiles containing various info 2. one column contains their email address 3. one column contains the date that the record was made 4. the other columns (45 in total) hold various information like title, contact info, etc... sometimes they're blank, sometimes they're not... there are some users (identified by email address) who have more than one row in the table, and sometimes their titles or contact info changes, so my question is: what is the best way to go through the entire table and: 1. look at any users who have multiple rows 2. compare the profile fields and if there are multiple values, use the one from the row with the most recent date, unless its empty 3. end up with a single row per email address The only other thing that i saw when looking at the data, was there are a couple of instances where there were multiple rows for the same user on the same date (they downloaded two different files and the way the old system was set up, it create a separate row each time)... now i dont think that any of there contact info wouldve changed in the same day but i guess without going through the tens of thousands of ros, there is a possibility that they may have entered info twice in the same day that might have differed somewhat, like a typo or using st instead of street so in that situation i guess there wouldnt necessarily be any logic to determine which one gets used in the consolidated record... All of your input and thoughts are welcomed and appreciated much in advance. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2009 Share Posted February 25, 2009 Well, you have to come up with definite rules for "who wins" in each case... but it's going to be tricky to do in mysql... Quote Link to comment Share on other sites More sharing options...
theconnollykid Posted February 25, 2009 Author Share Posted February 25, 2009 Well, the rough PHP/MySQL solution i have in my head is to: 1. create an empty consolidated table 2. run through the original table, grab all records ORDER BY date ASC 3. while loop: a. check if row with matching email exists in consolidated table i. NO: insert a row in a new consolidated table ii. YES: create a loop to go through each column - if column is blank -> do nothing - if column has a value - > update consolidated table I also think i could do it by storing the values for each consolidated record in an array before inserting it into the final consolidated table... thoughts? Quote Link to comment Share on other sites More sharing options...
fenway Posted February 25, 2009 Share Posted February 25, 2009 It's not robust to simply "fill in the blanks" -- the record may not make sense anymore. Quote Link to comment 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.