Compare values from CSV.....

[woldie]

Hi,

I have a CSV file which contains data from the search engines, which includes search engine, keyword, number of applicants and number of visits.

However in some cases there is repitition however I want to merge those figures together.

An example of what I mean....

AOL abc 64 361
Google widgets 56 1000
MSN xyz 48 1153
Google widgets 40 500

So this becomes:

AOL abc 64 361
Google widgets 96 1500
MSN xyz 48 1153

What I have done is upload a CSV file and insert the values into DB. However I need some assistance on how to compare each line of data so therefore compare the search engine and the keyword with another line of data and then merge the appropriate values together.

$allfiles = file ($userfile);
for ($n=0;$n<sizeof($allfiles);$n++)
{
$line = explode(",",$allfiles[$n]);

mysql_query("INSERT into tdata (searcheng,keyword,applicants,visits,tmid) VALUES ('$line[0]','$line[1]','$line[2]','$line[3]')");
}

Perhaps I need to place the name of the search engines into an array and keyword into another array as well, not quite sure. This may look a bit complex than first imagined.

Any assistance would be great.

Thanks.

W

[dcrombie]

I'd look at using 'awk' for this - probably a 3-4 line shell script...

Using SQL you could import the data into one table, then use a query to combine the data into a second table.

Postgres notatation would be something like:

INSERT INTO newtable SELECT DISTINCT(searcheng), SUM(hits) FROM table GROUP BY searcheng;

[woldie]

Yeah, thanks for the advice dcrombie, I have heard of 'awk' but could you point me in the right direction on this one?

I'm toying with the idea with arrays in PHP but with using 'awk' then perhaps this would be a shorter solution.

I agree with you on importing the data into one table then use the query you said.

Thanks, much appreciated