This is a question about the best approach to tackle my present problem

DarthGuido · October 1, 2010, 6:32am

Just forget about those two tables, the datasource column and your tecnical (impossible) solution for now. Please explain in your own words what you need this information for. Use examples if needed to clarify. Remember, we don’t know anything about your project

r937 · September 30, 2010, 4:21pm

what is the purpose of these two tables? after you do the magic update that you are looking for, what will happen to the tables afterwards? will you throw table B away?

or is this update procedure supposed to be repeatable? how often will you be running it?

paul_paisley · October 1, 2010, 2:55pm

r937:

INSERT
  INTO server_log_data
     ( server_rmc_time 
     , server_rmc_date 
     , server_latitude 
     , server_longitude
     , other_columns
     , datasource ) 
SELECT rmc_time      
     , rmc_date     
     , latitude     
     , longitude
     , other_columns
     , 'mobile' 
  FROM rmc_raw_data
ON DUPLICATE KEY UPDATE data_source = 'both'

don’t forget you have to set up the 4-part index –

ALTER TABLE server_log_data
ADD INDEX 
( server_rmc_time 
, server_rmc_date 
, server_latitude 
, server_longitude )

Thanks for the suggestion. That means I have to populate all the entries to ‘Mobile’ in the rmc_raw_data for its datasource right? if i do this, then i select into the serverlog table to get ‘both’ on duplicate. This means any remaining entry that is unmatched, i would have to populate them with ‘server’. Is this what it translates to or am i missing anything? meanwhile i would be running it and give you feedback soon. Thanks

paul_paisley · October 1, 2010, 8:59am

Thank you for your reply. This is what I what I am exactly doing.

EXPLANATION IN MY OWN WORDS.

I have been collecting data using a mobile and a server. The information collected by them is similar based on latitude, date,longitude and time. They are stored in different tables “mobile” and “server”. Now, 1., I want to know if both the server and mobile recorded the same lat,longtitud,time,and date (this is possible). If this is true I should insert “both” in a field called “datasource” for both the server table and the mobile table. if only the server recorded the lat, time,dat etc then i insert “server” in the server table. 2. I can again do another search to find out if only the mobile has the same lat,longt, time and date then insert “mobile” in the mobile table.

I now think, I would have the column “datasource” in both the server table and the mobile table. For the server table, the datasource column would have “both” if the server and mobile recorded same lat,time etc, and for the mobile table, the datasource column would have “both” which would be the same as in the server table since both have the same records of lat,long etc. And in addition to having “both” “server” which means that particular row’s lat,time,date appears only in the server.

At the end I would merge the two tables now having only one “datasource” field. I would then calculate the percentages for all the records which datasource are “both”, do same for “mobile” and again for “server”

This is only what I have to do. Like you guessed before, the datasource has to be in both tables.

Now this is sample of actual data I have. sorry send it in min

paul_paisley · October 1, 2010, 3:07am

well if it meant nothing to you, then i should be sorry either. It is my burden really and am still struggling to get out it with the assistance of everyone on this platform. I appreciate

paul_paisley · September 30, 2010, 7:10pm

sorry please just take a look at the attached text file. There is a kind of this comparison we are talking about here towards the end of the textfile. script just buttress the confused logic i have. It could add to my explanation too.

r937 · September 30, 2010, 7:13pm

that script means nothing to me, sorry

paul_paisley · September 30, 2010, 5:46pm

If am wrong in my explanation please tell me.

Base on the explanation you gave yourself using the two tables, that is my intension.

Since we are comparing row by row, to treat the umatched entry for the mobile, one may have to run another loop, this time we are not interested in the server but in the mobile. so we do the comparison again, set the datasource to “both” if they are equal and most importantly now set the datasource to “mobile” for the date sep 12 as in your example.

So in my opinion one needs to have 2 loops that handle the first criteria which set datasource column to either"both" or “server” for each record and a second loop which handles the second criteria which set the datasource column to either “both” or “mobile” for each record.

This what I think if the logic is not well thought please let me know. Many thanks.

Paul

paul_paisley · September 30, 2010, 6:09pm

SO WHAT DO YOU SUGGEST

I would like to send you a VBA script which I had used to achieve similar result but excel is not efficient in handling this kind of transaction for large data volumes and not a database system. should i send you the script for your lookup?

paul_paisley · September 30, 2010, 4:11pm

sorry for late reply.

Your analysis is impressive. That is the problem r937. and to answer Guido, i would say only one table would have the column “datasource” it could be any of them. please dont mind that phrase Guido. the explanation was a bit over bar there.

Now assigning mobile is really my problem which i envisage a kind of complex reversal looping which i cannont handle with my level of skill and being new to php and msyql.

so what do you guys think is the way out of this dilema? This is where i am stucked.

r937 · September 30, 2010, 6:02pm

but this is the problem!!! you can’t set the datasource to “mobile” because the september 12 row does. not. exist. in table A

i am letting you know

r937 · September 30, 2010, 2:26pm

if table A (server) has the datasource column, and there are rows in table B (mobile) that don’t exist in table A, then you aren’t going to be able to set the datasource column to “Mobile” on those rows, are you